Elasticube Data Folders Get Created Only From Web Version Of Elasticube Manager
Question: When an elasticube is created in desktop version of elasticube manager, only a .ecube file gets created, where as when an elasticube is created in web version of elasticube manager, a folder with the elasticube name is created that contains few subfolders. The folder is created in the path C:\ProgramData\Sisense\PrismServer\ElastiCubeData. Do the desktop version and web version of elasticube manager work differently? What do these folders hold? Is it a good practice to first create the elasticube in web version of elasticube manager, so that the folder structure gets created and then use desktop version for any modifications that cannot be done using web version? Solution: Both the desktop and web ElastiCube Managers create folders here C:\ProgramData\Sisense\PrismServer\ElastiCubeData after the first cube build (and subsequent builds). The desktop version does this after the cube is built for the first time (and updates these folders each time a build is done). When you open a cube from the Server Console on the desktop, you're really opening the .ecube file stored within C:\ProgramData\Sisense\PrismServer\ElastiCubeData\<cube name> When you create a new cube and save off the cube, you're really saving the schema (the .ecube file) which contains the relationships between the data but doesn't actually store the data from the data sources. This is nice when you want to do some versioning of the schema (in case you need to change the data model, are testing some relationships, etc). The web version also creates a folder in C:\ProgramData\Sisense\PrismServer\ElastiCubeData on first build. These folders in C:\ProgramData\Sisense\PrismServer\ElastiCubeData store the schema of the cube and all of the data (tables, views, etc) that have been imported into the cube. As for a recommendation for building cubes, keep in mind that the desktop version of ECM is more stable and has more features but the web ECM is easier to access (no need to get onto the server) and has greater sharing capabilities with select users.Editing An Existing SQL Custom Import Query
Question: I want to edit existing Custom Import Query, how can I do so? Solution: From the context menu open on the table with custom import query Change connection settings for table: From there you will be able to see your custom import query and edit it. It is also described here.Calculating YTD With Variable Start And End Dates Of The Year (Academic Calendar)
Question: For a specific industry, like Education, even though the number of days is roughly the same every year, each academic year can start and end on different dates. The usual way of calculating YTD wouldn't work for this requirements because school year can start and end in the middle of calendar year. Solution: The idea is we want to compare the years by the numbers of days that have passed since the current year started, e.g. compare the first 135 days of this school year with the first 135 days of last school year, the first 135 days of last two school years, the first 135 days of last three school years, and so on, regardless of the actual dates. To accomplish this, we need to create a date dimension table with a day rank and a YTD flag. Day rank is an enumeration of school days within each school year. By comparing the day rank of each day by the day rank of today's date, we can calculate the YTD flag. We need these two tables for this solution to work: List of school year with start and end dates List of dates. You can use the date dimension Excel file from this article. School Year table: Date table (you only need the Date column for this purpose) Step 1 Create a new dim date table using the custom table option: Get the list of school dates (only dates between start and end dates of each school year) and remove dates that fall on Saturday and Sunday (optional). Rank the dates within each school year. Based on the result from above, get today's date rank. Compare each date's rank with today's rank to calculate the YTD flag. You can use this SQL as an example. --Join the list of school dates with today's date rank --Compare each date's rank with today's date rank to create YTD flag SELECT *, CASE WHEN t1.[Day Rank] <= t2.[Today Day Rank] THEN 1 ELSE 0 END AS isYTD FROM ( --Get the list of only school dates (dates between start and end date) --Remove dates that fall on Saturday and Sunday (not school dates) --Rank dates within each school year SELECT d.Date, s.[School Year], s.[Start Date], s.[End Date], rankasc(s.[School Year], d.Date) AS [Day Rank] FROM [Dim Dates] d JOIN [School Year] s ON d.Date BETWEEN s.[Start Date] AND s.[End Date] WHERE DayOfWeek(d.Date) <= 5 ) t1 JOIN ( --Get today's date rank SELECT t.[Day Rank] AS [Today Day Rank] FROM ( --Get the list of only school dates (dates between start and end dates of each year) --Remove dates that fall on Saturday and Sunday (not school dates) --Rank dates within each school year SELECT d.Date, s.[School Year], s.[Start Date], s.[End Date], rankasc(s.[School Year], d.Date) AS [Day Rank] FROM [Dim Dates] d JOIN [School Year] s ON d.Date BETWEEN s.[Start Date] AND s.[End Date] WHERE DayOfWeek(d.Date) <= 5 ) t WHERE t.Date = createdate(getyear(now()), getmonth(now()), getday(now())) ) t2 ON 1=1 The table should look like this: Step 2 Link the fact table(s) to the new date dimension table on the Date field. Step 3 Create your widget(s) and add the YTD flag as a filter, set to 1.Connecting To The Elasticube From Microsoft Excel
Question: The purpose of this post is to demonstrate how to connect to a Sisense Elasticube from Microsoft Excel. Solution: Begin by installing the Sisense ODBC driver. https://www.sisense.com/data-connectors/odbc/ Once it is installed, go to ODBC-64 bit from the windows search (just search odbc and it will pop up), then go to the system tab and add a new connection. Then choose the Sisense ODBC Driver. Add a name and a description and then click on the empty box for elasticube names. This will prompt you for a Sisense login. If working on the same server as Sisense, the server will be localhost:8081. The username and password corresponds to your login in Sisense. Note: If you receive a licensing error, please reach out to your Customer Success Representative. Finally, select an Elasticube and save the connection. Now you are ready to connect to the elasticube from Excel. Now open a new workbook in Excel. Select the Data tab> Get Data> From Other Sources > ODBC. You will be prompted to either select an existing connection or enter a connection string. Select the connection and continue. You may be prompted for credentials. However, because the connection already contains them, they are irrelevant at this stage. You can enter a random value and click continue if it will not let you continue with an empty box. Once the connection is established, you should see all tables available in the elasticube. Please note you can not see hidden fields from the ODBC connection. In the case above, I have selected the Admissions table and loaded it into Excel. From this point you should have a populated table with the ability to refresh. You can append the query to join two tables together and customize the query. Please refer to the documentation for Excel Power Query.Removing Leading Zeros
Question: What is the best way to remove leading zeros? I have a field that is alphanumeric values (JONESBJ001, SMITH KC02, 0000012345, 0000555213. Solution: If all of your data with leading zeros will always be some sort of numerical string (ie there will not be values like 000{character}) you could create a custom column using the following expression in an Elasticube: CASE WHEN Left([Values],1) = '0' THEN ToString(ToInt([Values])) ELSE [Values] ENDFlattening JSON Objects In SQL Server
Question: In instances where tables within SQL Server contain JSON objects within a column you can use the import sql query editor feature in Sisense to flatten these objects into individual fields. For example: Can be turned into: Solution: To accomplish this, SQL Server has native functions that are capable of parsing json objects and flattening them into individual fields. The below code uses the JSON_Value extracts a scalar value from a JSON string in which the first parameter takes an expression which is typically the name of a variable or column that contains JSON text. The second parameter takes a JSON path that specifies the property to extract. The default path mode is lax mode, which takes the syntax '$.<keyname>'. Below is the SQL used to flatten the JSON values in the sample data. select SalesOrderNumber , JSON_Value(JSONValue,'$.CustomerID') as CustomerID , JSON_Value(JSONValue,'$.OrderDate') as OrderDate , JSON_Value(JSONValue,'$.TotalDue') as TotalDue , JSON_Value(JSONValue,'$.ShipMethodID') as ShipMethodID , JSON_Value(JSONValue,'$.TerritoryID') as TerritoryID , JSON_Value(JSONValue,'$.SalesPersonID') as SalesPersonID from OrderHeaderJSON Other useful functions regarding working with JSON in SQL Server can be found here: ISJSON (Transact-SQL) tests whether a string contains valid JSON. JSON_VALUE (Transact-SQL) extracts a scalar value from a JSON string. JSON_QUERY (Transact-SQL) extracts an object or an array from a JSON string. JSON_MODIFY (Transact-SQL) changes a value in a JSON string.How To Concate Three Strings In Elasticube
Question: I have requirement to combine and show the dimension. There are two dimensions Product and Product_Family. I want to display it as single dimension in charts like "Product 1 | Product Family 1", "Product 2 | Product Family 1", so on. Solution: If all dimensions are of string data type it will be enough to use the '+' sign between them and the '|'. If they are not strings yet, you can use the TOSTRING() function before using the '+' You can also use the Concat function: Concat(Concat([Product1].''),[Product2]) Or Concat([Product1],[Product2])Accumulation Build For Millisec (Ms) By Using Unix Timestamp Transformation
Question: This post we will talk about accumulation via millisec by using a unix timestamp transformation In this use case, We will be working with a CData Elasticsearch ODBC driver and client need a way to accumulate data base off milliseconds. Naively, we are unable to accumulate down to the millisec but with this work-around, we are able to do so. Solution: This will be done upon data pull in from your data source where we will edit the query and transform it into a unix timestamp Please keep in mind this is using function from your native data source. In this case, we are using ODBC CData and they support the function we needed for this transformation By using the following function, we are able to bring in data and transform into unixts SELECT datediff('millisecond' ,DATEFROMPARTS(1970, 1,1),datetime) FROM demosisense.testDate After this has been complete, you can now go ahead and set the accumulation base off the new field we created. Following regular Sisense function, we can then hide this field while do an accumulative build on this. And this will accumulate down to the millisec!Using SQL Coalesce To Handle Null Values
Question: SQL NULL is not technically a value, but rather a state. Fields are not equal or not equal to null, they are or are not null. As such, null fields can get a little tricky when attempting different kinds of transformations. For example, mathematical equations across multiple fields where any of the fields are null will result in a null value: 5 * null = null null – null = null 5 + null = null Consider a salary calculation where an employee as Base but no Commission: Solution: In this case the Total for Regina should equal 49000, not null. To transform the null into a not null value like 0 we could use SQL Case: More efficiently we can use Coalesce which checks for null values in the specified expression (i.e., Base or Commission) and does the translation to the specified value (i.e., 0): Coalesce is also useful for finding the first populated (i.e., non-null) value across multiple fields in the same record (e.g., select the first non-null phone number from home, mobile, office fields). Using SQL Case: The same logic using Coalesce results in more efficient and decipherable query: Both queries bear the same correct result:Enforcing Security On Consortium 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. Solution: 1. Duplicate the data in the table by using the UNION ALL clause, to create a copy of the data with masked values. 2. Add a flag that indicates whether the record is masked or not. 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 3. Create or import a table that connects user and company, for example: 4. Create a table that contains the rules of masking for each user: 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. 5. Connect the tables on Company code and isMasked: 6. 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.