Recommendations For A Book/Tutorial On SQL For Sisense
Question For a novice starting out with Sisense and a very basic knowledge of SQL, would someone in this community be able to recommend a book and/or tutorial that could help expedite learning the SQL syntax used in the system to set up custom tables, etc.? Answer You can start with the below Udemy course which covers most of the basics that you'll need and for new users, it's practically free (if for some reason it cost you more - simply google the course's name and the phrase "coupon". Work like a charm) https://www.udemy.com/microsoft-sql-for-beginners/ https://www.w3schools.com/sql/ is another website where you can learn basic SQL with ease Here is another free interactive site you can learn some basic SQL. https://sqlzoo.net/How To Filter For The Last Two Records Using SQL In Ecube
Question How to find out if there exists the top 2 records for the same ClientID in a table. I know that the MAX gives the Top record and that SQL has the LIMIT 2 but I cannot use these two keywords in eCube. Answer This can be easily done within the ElastiCube. Here are the steps: Create a new custom column in your table to ranks all records for a specific ClientID, the script will be something like this: rankasc([client_id],[created_at]) Create a custom SQL expression (and name this table [Table Max Ranking]) to find out the max ranking for each client, the syntax will be : SELECT client_id, max([rank]) AS MaxRank FROM [Table] GROUP BY client_id Create another custome SQL expression with the following scripts : SELECT a.* FROM [Table] a JOIN [Table Max Ranking] b ON a.client_id = b.client_id AND a.[rank] >= (b.MaxRank - 1) ORDER BY a.client_id, a.[rank] descConsortium Data By Masking Sensitive Values
Question Some organizations provide Sisense to their clients as part of their business offering. Some of these organizations would like also to provide clients with the consortium data derived from Sisense - a single ElastiCube where clients submit their confidential sales and other data to the vendor, and then the vendor allows them to benchmark their sales, share (concentration), and rank amongst their peers. Each client may see the details of their own data, but cannot see the names of competitors or their specific products. Consortium data allows clients to better understand concentration and distance from their competitors. The vendor must provide the following security measures on consortium data: Users should be able to see their company's own data relative to aggregated data, but should not be able to see details of competitors at company or product level, and should not be able to filter competitor company or products. Users must be able to use Product Name/Category as a dimension but only display a company's own products/categories. Clients should be able to see the name of their company but not the names of their competitors. Users must be able to filter by dimensions and use a variety of dimensions. Below you can find an approach for enabling security on consortium data. This approach suggests masking sensitive values in order to hide them from unwanted users. Answer Duplicate the data in the table by using the UNION ALL clause, to create a copy of the data with masked values. Add a flag that indicates whether the record is masked or not. Here is the SQL code you can use: SELECT D.Date, D.CompanyCode, D.CompanyName, D.Country, D.ProductType, D.[Product Name], D.DistributionChannel, D.Sales, D.Assets, 0 isMasked FROM Data D UNION all SELECT D.Date, D.CompanyCode, '###########' CompanyName, D.Country, D.ProductType, '#######' [Product Name], D.DistributionChannel, D.Sales, D.Assets, 1 isMasked FROM Data D​ Create or import a table that connects user and company, for example: Create a table that contains the rules of masking for each user: Here is the SQL code you can use: SELECT U.User, U.Company, 0 isMasked FROM UserToOrg U UNION all SELECT DISTINCT O.User, D.CompanyCode, 1 isMasked FROM [Data] D JOIN [UserToOrg] O ON D.CompanyCode!=O.Company The logic is to take the list of users and their company and allow them to see it unmasked (isMasked 0). This list is added to a join of each user with every other company (join on non equal), which they will see masked. Connect the tables on Company code and isMasked: Create security settings for users/group: The result: Note: This solution requires company codes to be displayed. If the existing company codes might compromise the identity of the competitors, consider doing one of the following: Instead of masking all as the same value (#####), give each a unique masked value (for example - ####1,###2). You can do so more easily by creating an excel file with the relevant fields. Use company codes that don't relate directly to company names.
