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 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)