How to export .ecdata and .dash files
Introduction This forum post will demonstrate how to save your ElastiCube .ecdata file which contains your ElastiCube schema and data, and the .dash file of your desired dashboard. Purpose/Benefits The .ecdata file contains both the data and the logic structure of the ElastiCube model, the .dash file contains the widgets structure logic and the relation to relevant ElastiCube. Exporting the .ecdata and .dash files will allow you to easily import the files and run your ElastiCube/Dashboard environment. Please note that security settings and users are stored on MongoDB and will not be stored within these files. To avoid confusion between .ecdata and .ecube files please see the following post: Difference between .ecdata and .ecube files Steps exporting .ecdata (.sdata for Linux) Windows: Step 1 - Open the ElastiCube Manager, 'Servers' and choose 'Manage Servers' to open Sisense Server Console Step 2 - Stop the relevant ElastiCube and Choose Export This will open a window allowing you to choose the location of the .ecdata file Note: in case your ElastiCube is very large, export can take a while to complete. We recommend stopping your Web server (IIS / IIS Express) to avoid the ElastiCube from automatically starting during this time, as a result of incoming queries. Linux: Use Sisense CLI to export the .sdata file: https://docs.sisense.com/main/SisenseLinux/using-sisense-cli-commands.htm The command export should be used. Example: si elasticubes export -name MyCube -path \"/opt/path/to/NameOfCube\" -file cubeName.sdata Another option to export - using v2 of REST API: To get the ID of the model, use another API call: Or to do the same from GUI: Open the Data page. For the relevant ElastiCube, select > Export Model. Select to export the model schema and the data. Click Export. exporting .dash It will be the same for Windows and Linux. Open Sisense web, click the more options of the relevant dashboard and choose Export Dashboard The dashboard will be downloaded to your local drive. Creating a Sample Set of your ElastiCube It will be the same for Windows and Linux. If the .ecdata file is too large to transfer, you might want to create a smaller ElastiCube sample data. To do so, duplicate the model in order to not to impact your dashboards: And build the duplicated model with a limited amount of rows:Alert on a M2M Risk
Introduction This article describes how to enable an alert* when there is a risk of Many-To-Many relationship (M2M) between two or more tables. * This implementation involve the use of Pulse and it's supported in Sisense v6.5 and above. Motivation Almost every Data Model designer that dealt with a bit more advanced modeling at some point ran into data\performance issue and after a long troubleshoot process he\her found out that the root cause came from M2M. M2M can occur due to the following**: Connecting 2 tables with a pure M2M relationship such as two Fact tables with a non-unique key. A unique key became a non-unique due to changes or data issue within the source system. A business need to have M2M between two entities. From the described scenario you can assume that the M2M can be identified not only during the EC design but also during its ongoing use. Therefore, having a proactive alert for the EC designer can prevent from the user experiencing poor dashboard performances, wrong result sets and save many hours of troubleshooting, panicking users and general feeling of frustration. ** There can be other more complex cases of M2M which we won't describe here as the mechanism of the M2M alert is less relevant for them. Please refer to this article for a more detailed description of M2M relationship Implementation As an Data Model designer, during the design and after the final version of my schema is completed, I want to track on my connection and make sure that my assumptions of uniqueness on one side of the relationship (to get 1:M relationship) is being captured. Step 1 - Build a M2M Detection Table We'll create a table that will reflect the uniqueness of each table within the final schema that has to be unique according to its relevant primary or surrogate key. Basically we'll count the number of total rows within a table, the number of unique values of the table key and compare between them. if they are equal then the key is unique within the table, but if not there is a risk for M2M and so we'll raise a flag as you can see here: We can learn from the output of the table that for our case we have only one table that is not unique (Cost). Here's the query I used in order to create this table: SELECT 'DimDates' AS Table, 'Date' AS Key, count(t.[Date]) [Total Count], DISTINCT_count(t.[Date]) [Distinct Count], count(t.[Date])-DISTINCT_count(t.[Date]) AS [Duplicated Records], ifint(count(t.[Date])-DISTINCT_count(t.[Date])>0,1,0) AS M2M_Flag FROM DimDates t UNION ALL SELECT 'Customer' AS Table, 'CustomerID' AS Key, count(t.CustomerID) [Total Count], DISTINCT_count(t.CustomerID) [Distinct Count], count(t.CustomerID)-DISTINCT_count(t.CustomerID) AS [Duplicated Records], ifint(count(t.CustomerID)-DISTINCT_count(t.CustomerID)>0,1,0) AS M2M_Flag FROM Customer t UNION ALL SELECT 'SalesPerson' AS Table, 'BusinessEntityID' AS Key, count(t.BusinessEntityID) [Total Count], DISTINCT_count(t.BusinessEntityID) [Distinct Count], count(t.BusinessEntityID)-DISTINCT_count(t.BusinessEntityID) AS [Duplicated Records], ifint(count(t.BusinessEntityID)-DISTINCT_count(t.BusinessEntityID)>0,1,0) AS M2M_Flag FROM SalesPerson t UNION ALL SELECT 'Product' AS Table, 'ProductID' AS Key, count(t.ProductID) [Total Count], DISTINCT_count(t.ProductID) [Distinct Count], count(t.ProductID)-DISTINCT_count(t.ProductID) AS [Duplicated Records], ifint(count(t.ProductID)-DISTINCT_count(t.ProductID)>0,1,0) AS M2M_Flag FROM Product t UNION ALL SELECT 'SalesOrderHeader' AS Table, 'SalesOrderID' AS Key, count(t.SalesOrderID) [Total Count], DISTINCT_count(t.SalesOrderID) [Distinct Count], count(t.SalesOrderID)-DISTINCT_count(t.SalesOrderID) AS [Duplicated Records], ifint(count(t.SalesOrderID)-DISTINCT_count(t.SalesOrderID)>0,1,0) AS M2M_Flag FROM SalesOrderHeader t --Example for uniqueness by surrogate key UNION ALL SELECT 'Cost' AS Table, 'Size + Weight' AS Key, count(t2.Key) [Total Count], DISTINCT_count(t2.Key) [Distinct Count], count(t2.Key)-DISTINCT_count(t2.Key) AS [Duplicated Records], ifint(count(t2.Key)-DISTINCT_count(t2.Key)>0,1,0) AS M2M_Flag FROM ( SELECT t.Size + '|' + tostring(t.Weight) AS Key FROM Cost t) t2 --Example for checking uniqueness for a table which not in the final schema but effects another table (as a source of a lookup custom field) UNION ALL SELECT 'Store' AS Table, 'BusinessEntityID' AS Key, count(t.BusinessEntityID) [Total Count], DISTINCT_count(t.BusinessEntityID) [Distinct Count], count(t.BusinessEntityID)-DISTINCT_count(t.BusinessEntityID) AS [Duplicated Records], ifint(count(t.BusinessEntityID)-DISTINCT_count(t.BusinessEntityID)>0,1,0) AS M2M_Flag FROM Store t Step 2 - Create relevant KPI within a widget We'll create an indicator with the following formula: if([Total M2M_Flag]>0,1,0) This widget can be temporary as it only will serve us for alerting when the total of M2M flags is bigger than 0. Step 3 - Add the KPI to Pulse After Adding the widget to the Pulse, we'll mark the condition according the threshold 'Greater than' 0. Feel free to adjust the notification mode and other preferences according your requirements.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 LINKMonitoring Your Builds
Part of having a successful BI tool is having your data refreshed on the time you need it. To ensure your builds are working as expected, you can follow the steps provided below. Step 1 - Setting Up a Build Alert Follow the documentation of Sisense Pulse Creating build alerts. Build alerts are created from Sisense and are sent following an ElastiCube build. Step 2 - Build Alert In case of a build alert, an alert will pop up letting you know what cube had failed and when Step 3 - But now what? how do I know why it failed? Go to the Data tab where you will see the cube that failed and open the schema of the cube Click on option > View Latest Logs This will show you the stage/table it failed on and the exception message In our case, the build failed due to a missing file used in table1 table Step 4 - Can't understand the exception message Look at the Build Errors Troubleshooting to better understand the build mechanism and errors. Still can't figure out what went wrong. Submit a ticket and don't forget to add the following details - Name of cube Exception message Did the cube ever built successfully? Time of failure and your time zoneGet CSV of ElastiCube Dependencies
Introduction This script exports to CSV a complete list of the front-end dependencies for each ElastiCube. It provides information about whether an ElastiCube is used for data security, hierarchies, build alerts, pulse KPIs, or starred formulas. It also provides the list of dashboards (by ID and name) that use an ElastiCube. Prerequisites Tested in Python 3.6. Requires Sisense 7.1+. Python Packages JSON Pandas RE Requests Steps 1. Download and unzip the file here. 2. Update the configuration section. For authentication, you can hard-code a token or use the program to generate a token. 3. Run the script Sample OutputServer Console Preferences & Configuration
Note: Sisense's default ElastiCube Server Console configuration is optimally tuned for ElastiCube and dashboard performance. It is generally not recommended to change these settings (except for Default server data folder and Rserve). This article identifies and defines the preferences available for the Sisense ElastiCube Server. In Sisense 6.7 or earlier, you can access the Sisense server preferences by clicking the cog in the Sisense Server Console: Setting Default Description Default server data folder C:\ProgramData\Sisense\PrismServer\ElastiCubeData Directory where ElastiCubes are stored (see here for details) # of simultaneous query executions Number of CPU logical cores / 2 Maximum number of queries that will be processed at once. Automatically set in the installation to half the number of logical cores on the server Query TCP port 812 Port used to query the ElastiCube server from the web application Rserve 120.0.0.1:6311 IP address and port to connect R with Sisense (more information here) Query timeout (in seconds) 300 Time alloted to a non-responding query before it times out. Query Retries on Failure 0 After a query times out, how many retries to attempt ElastiCube port 50000 Port the ElastiCube runs through Backup ElastiCube before each new build Unchecked Option to save a .ecdata file of the ElastiCube before each build Data-import chunk size 100000 Number of records imported at once in a build ElastiCube restart timeout (seconds) 3 When an ElastiCube times out, how long before it restarts Free memory percentile to auto-restart 30 Minimum free memory needed in order to restart the service automatically in case it fails (no longer in use) Server memory percentile to auto-restart 0 Minimum server memory needed in order to restart the service automatically in case it fails (no longer in use) Remove EOF Character from textual fields Checked Removes hidden characters from text files Compress on build Checked Compress data when performing builds Query CPU cores Number of CPU logical cores / 2 Maximum number of CPU cores that can be leveraged to process one query ElastiCube memory usage Dynamic Percentage of RAM on the machine that the ElastiCube server can use. Dynamic is the default setting and will use Sisense's built-in processes to manage and clear memory. Skip data import on connection errors Unchecked Continue with a build if there is a connection error on a specific data source Recycle queries Checked Store query metadata for later use In Sisense 7.0 or later, it is available in the Admin screen, Data Management - Data Sources, and by clicking the context menu beside the applicable ElastiCube server (fewer options are available than from the Sisense Server Console):Floating Precision Limitations
Sisense supports floating-point numbers (IEEE 754 standard) and allows you to perform an arithmetic calculation on these numbers. Floating-point numbers suffer from a loss of precision when represented with a fixed number of bits (e.g., 32-bit or 64-bit). This is because there is an infinite amount of real numbers, even within a small range like 0.0 to 0.1. Irrational numbers, such as π (pie) or √2 (square root of 2), or non-terminating rational numbers (like 0.333...), must be approximated, and calculations that are done by a computer Central Processing Unit (CPU) will need to be rounded. It is important to note that due to this loss in precision, you should almost never directly compare two floating-point numbers. A better way to do it is to compare numbers with some precision epsilon. For example: if (a == b) – problematic! if f (Abs(a – b) < epsilon) – correct! Floating-point numbers lose precision even when you are just working with such seemingly harmless numbers like 0.2 or 76.5. You should be extra careful when working with a large amount of floating-point operations over the same data as errors may build up rather quickly. If you are getting unexpected results and you suspect rounding errors, try to use a different approach to minimize errors. To do that, please consider the following: In the world of floating-point arithmetic, multiplication is not associative: a * (b * c) is not always equal to (a * b) * c. Additional measures should be taken if you are working with either extremely large values, extremely small numbers, and/or numbers close to zero: in case of overflow or underflow those values will be transformed into +Infinty, -Infinity or 0. Numeric limits for single-precision floating-point numbers are approximately 1.175494e-38 to 3.402823e+38. Sisense production deployments that include more than one CPU will use parallel calculation algorithms to speed the calculations and spread the arithmetic operation between several CPUs. Given that each CPU may result in a rounded calculation, the total arithmetic result can be different every time. This means that while running the calculation on a single CPU the result is approximated, but the same result every time, while the same calculation on a multi-CPU system may produce different results every time. This is normal behavior and should be addressed by using an epsilon during the compression of results from different iterations.