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.What is the difference between .ecdata and .ecube data files?
Both .ecdata and .ecube files are used to make backups of data and transfer data between ElastiCube Servers. Your data model or schema is saved in an ecube file. ecube files contain the schema and the data connection settings required for building your ElastiCube. The ecube file does not contain the raw data. After building your ElastiCube from an ecube file, your ElastiCube with the data is stored in an ecdata file. The Elasticube Manager stores the design of the data schema in an .ecube file. It is not the data itself, but only a “blueprint” of your data sources and structure. Saving a file in the ElastiCube Manager will output a .ecube file. The .ecdata files contains both the data and the logic structure of the data. Typically .ecube files are very small (a few KBs), whereas .ecdata files are sensitively larger (may reach GBs depending on your data). Exporting a file from the Sisense Server Console will output a .ecdata file. Learn how to export an ecdata file in this LINKData Transpose Guide
Source data that is flattened may require some or all of the columns of a source table to be transposed, in order to work seamlessly with SiSense’s functionality. This post will outline the cause and two possible solutions to this issue. The first solution uses an excel macro to tranpose data. The second solution provides a method for creating a custom SQL expression that will transpose the data in the ElastiCube Manager. Required: Excel, basic SQL knowledge. Suggested: Notepad++, basic knowledge of regular expressions (Notepad++ Download Page) Transposing data can make development easier on data sets that are not properly structured. This guide also provides an example of advanced custom SQL expressions and bulk creation of SQL statements. For our example, we are using survey data, where every question is a column, and the answer is the value in the column. The question we want to answer with the data is “How often do married people answer ‘Best’ to a question?” With the original table format, we are not able to answer this question a dashboard. In order to answer this question, we have to transpose some of the data. To do this, we need to create a more general ‘Question’ column, where each value is a question name, and a general ‘Answer’ column, where each value is an answer to the corresponding question. The original source data and the desired reformatted data can be found in the attached excel book Prism Reformatter.xls. This picture show the desired result of the process. Method 1 - Excel Macro in Prism Reformatter.xls The attached document Prism Reformatter.xls contains a macro that will transpose the data in a sheet based upon parameters a user can set. This method should be used if your data already in excel, would be easy to put into excel, and has less than 65,000 rows. Prism Reformatter.xls contains 3 sheets. Data- Will contain the data to be transposed. It is currently populated with an example data set. Formatted- This contains a formatted version of the example data set. Configuration- This is the sheet where you define the settings for your data transpose, and it also contains a button that executes the macro to transpose data. The following settings can be configured: Row Fields: The columns in the data sheet to be selected, but not transposed. In our example that would be Date, Name, Gender, Marital Status, and Age Col Fields: The columns to be transposed. In our example that would be Question 1, Question 2, Question 3. Source Sheet: The sheet in the workbook containing the original data. Target Sheet: The sheet to put the reformatted data in. This sheet will be created when the macro is executed Rows/sheet: How many rows to put on a sheet before creating another sheet to put data into. This is to account for the amount of data a worksheet can contain in different version of excel Once these settings are configured, click the Reformat button, and you’re done. Use the new sheet as the source for the Elasticube. Method 2- Custom SQL This method should be used if your data would be difficult to import into excel, or the amount of data is causing poor performance in excel. This can provide a basic methodology for more complex transpose logic that may not be possible with the excel document. At a high level, this solution requires a table to be created that defines the columns to be transposed, as well as a custom SQL table that will build the transposed data in the ElastiCube manager. Listed below is the final custom SQL expression used for this example to transpose the table DATA. This is to give you a sense of what we’re working toward. SELECT D.Date, D.Name, D.Gender, D.[Marital Status], D.Age, CRS.col_nm AS Question, CASE WHEN CRS.VAL=1 THEN D.[Question 1] WHEN CRS.VAL=2 THEN D.[Question 2] WHEN CRS.VAL=3 THEN D.[Question 3] END AS ANSWER FROM Data D CROSS JOIN [CRS_JN] CRS The CROSS JOIN joins every row from Data to every row in CRS_JN. The following diagram gives a high level overview of the process. Create the Custom SQL Expression The steps below explain the process for creating this SQL statement. It can be extended to transpose any number of columns. 1.Create a list of the columns you want to transpose. With our sample data set, we can copy all of the column names that need to be transposed (Question 1,2,3), and transpose them into a new worksheet, and name the column ‘col_nm’. If your source is not excel, a different method will be needed to retrieve the column names, and it will depend on your source system. Once the column names are in an excel sheet, generate a numbered list next to it and name the column ‘val’. I've named the sheet CRS_JN in this scenario. The table should look like this in excel. 2. Import the new workseet into ElastiCube Manager. In your elasticube, click Add Data –> Excel worksheet, and add the sheet that was created in step 1 (CRS_JN). You’ll end up with a new stand-alone table that looks like this: 3. Create the case statement for your custom SQL. This code can be written by hand, but in scenarios where you are transposing large number of columns, this step can be very helpful. Instead of writing each WHEN statement by hand, I used the text editor Notepad++, because it is able to find and replace lines of text based upon patterns. Take the data from the CRS_JN worksheet, without column names, and copy it into Notepad++. It should just be a list of a number, a tab, and a value. Open up the Find and Replace menu (Ctrl+h), and enter the following for your search pattern. (.*)\t(.*) And you replace with this pattern: WHEN CRS.VAL=\1 THEN D\.[\2] Make sure the ‘Regular Expression’ Search Mode is enabled. The following diagram show what happens on a row by row basis in this find and replace. 4. Write the rest of the custom SQL. Write the part of the select statement that doesn't require transposing, and add the cross join. In this case it is SELECT D.Date, D.Name, D.Gender, D.[Marital Status], D.Age FROM Data D CROSS JOIN [CRS_JN] CRS Next, select col_nm from the cross join table, alias it, and write the beginning and end of the case statement. SELECT D.Date, D.Name, D.Gender, D.[Marital Status], D.Age, CRS.col_nm AS Question, CASE END AS ANSWER FROM Data D CROSS JOIN [CRS_JN] CRS Insert the WHEN clause you created in step 3 between the CASE and END indentifiers SELECT D.Date, D.Name, D.Gender, D.[Marital Status], D.Age, CRS.col_nm AS Question, CASE WHEN CRS.VAL=1 THEN D.[Question 1] WHEN CRS.VAL=2 THEN D.[Question 2] WHEN CRS.VAL=3 THEN D.[Question 3] END AS ANSWER FROM Data D CROSS JOIN [CRS_JN] CRS 5. Build the new table. Open the Elasticube Manager, click Add Data -> Custom SQL Expression. Insert the query you generated, parse the SQL to see if it’s correct. If the query parses, build the table and validate the data. How often do married people answer ‘Best’ to a question? We can now answer this question. Create a dashboard with the ElastiCube that contains the transposed table. Create a pivot table that selects Marital Status and Answer as attributes, and add a value that calculates the DupCount(Answer). We now know married people answered 'Best' once. Attached Files Prism Reformatter.xls Transpose_Test.ecdata Related Links Regular Expression Guide: http://letconex.blogspot.com/2013/06/how-to-use-regular-expressions-in.html Cross Join Explanation: http://en.wikipedia.org/wiki/Join_(SQL)#Cross_joinData Denormalization for Faster Dashboard Query Response
Introduction This post will explain how to denormalize your ElastiCube schema in order to get faster query response in SiSense Web. Purpose/Benefit Denormalization of data is used to reduce the query response time in SiSense Web. It is most useful for large tables of over 100 million records. It reduces the time necessary to process the inner joins that are created when querying multiple tables. Please note that this process will lengthen the build time slightly depending on the number of fields you move. Furthermore, though it is not the main purpose, administration of the ElastiCube becomes easier due to fewer tables to manage. What is data denormalization? The standard definition for schema denormalization is the process of attempting to optimize the read performance of a database by adding redundant data. In SiSense terms, reducing data connections in the ElastiCube so the dashboard queries use fewer joins. Joins are costly on query time, especially when you have large transactional tables. Reducing the number of joins that are necessary to bring back data to the dashboard will help maximize query performance. What data can be denormalized? One to Many Relationship A one to many relationship is defined as each row in the related to table can be related to many rows in the relating table. In this case the Products table has a one to many relationship with Orders. ProductID in the products table is unique. ProductID in the Orders table is not unique. For every ProductID in the Product table, there are many matching ProductID’s in the Orders table. One to One Relationship A one to one relationship is defined as each row in one table is linked to one and only one other row in another table. In this case the Orders table has a one to one relationship with the Ecommerce table. For every OrderID, there is only one corresponding OrderID in the Ecommerce table. Steps Example – One to Many In this example we will walk through the Products and Orders table denormalization. Step 1 Ensure that both tables are included in the ElastiCube. Step 2 Create a custom field in the main table of focus (Orders table in our example) Step 3 Use a lookup function to bring the qualitative data into the main table of focus. The lookup function definition returns the first matching value for the specified name from a dataset that contains name/value pairs. The syntax: lookup(<Remote Table>,<Remote Result Column>,<Current Match Column>, <Remote Match Column>) Example of ProductName lookup: lookup(Products, Products.ProductName, Orders.ProductID, Products.ProductID) Step 4 Repeat this step for each field necessary. Step 5 Finally, make the table that contains the qualitative data invisible so to not confuse the user (Products in our example)