Data Models
Created Apr 15, 2026
3 members
50 discussions
Reading multiple Excel files into Sisense data model Introduction: If you are migrating from Windows to a Sisense Linux-hosted solution, on the Sisense Windows machine you may use an SFTP client that moves Excel files placed in a remote server's folder to a folder on the Sisense Windows server. With this, it is used as a source folder for Excel file import to an ElastiCube. Since this is not possible with your hosted Linux, we could use the CDATA SFTP Connector to import these files. In your existing connection string, you will get just the names of the files. When connecting to an Excel sheet stored in an SFTP server, the URI must be sftp://<server>:<port>/<path to file> , as shown below. If the connection string does not contain this, you will just get the names of the files. Additionally, the ConnectionType and AuthScheme must be set to SFTP , and the SSHAuthMode must be set to either None , Password , or Public_Key depending on your SFTP server. If the issue still persists, it would be helpful to have a log file. To generate a log file, in your connection string to Excel, please set the Log file to the path where the log file will be generated (such as C:/Logs/log.txt) and Verbosity to 3. Then reproduce the error. Additional Resources: Sisense Docs: https://docs.sisense.com/win/SisenseWin/introduction-to-data-sources.htm Sisense Academy: https://academy.sisense.com/sisense-data-designer-web-application
User Parameters Use Case - Personalized Data Filter Requirements This article explains one of the example use cases of User Parameters laid out in User Parameters - What They Are and What They're For (Example Use Cases) . User Parameters can be used in table queries in live models to provide tailored data to end users. A common use case of this type of parameter is for personalizing filters of certain tables in a live model. For example, users in different countries may have different history depth requirements or data retention policies. The idea is to use a parameter to associate each user group to their respective filter value, then use the parameter to customize the WHERE clause of the table query. Let's walk through an example of a Snowflake live model. Let's suppose we have an insurance company that has clients in the US and Australia. While all claim data is stored in the database, let's say for analytical purposes, claim managers in the US are allowed access to only one year of data, while those in Australia have access to two years. The first step to implement this requirement is to create the user parameter. When creating parameters for a query, be sure to select Custom Live Queries in the Applicable Areas field. Once you've created the user parameter, the next step is to assign a value for each group (or individual user, if needed). For this use case, the value should represent the required history depth, measured in years, i.e. 1 for US users and 2 for Australia users. Now that the parameter values have been assigned to groups, the final step is to create/modify your table query(ies). Let's take a table called FactPayments as an example. This is the original query of the table: SELECT<br/> P.CLAIM_KEY<br/> , P.POLICY_KEY<br/> , P.PAYMENT_DT_TIME<br/> , P.PAIDAMOUNT<br/> , P.STATUS<br/> , PO.CUSTOMERID<br/>FROM "INSURANCE_CLAIMS"."PAYMENTS" P<br/>JOIN "INSURANCE_CLAIMS"."POLICIES" PO<br/>ON P."POLICY_KEY" = PO."POLICY_KEY" To limit the data to the required history depth, add a WHERE clause with a condition that ensures the date field values are within the last number of years assigned in the user parameter. To add a parameter to a query, type two curly brackets, then select the parameter from the dropdown menu. For this example, here is how the WHERE clause condition should look like for a Snowflake database: WHERE P.PAYMENT_DT_TIME >= DATEADD(YEAR, -1 * {{HISTORY_DEPTH}}, CURRENT_DATE) Here is the full, updated query of the FactPayments table: SELECT<br/> P.CLAIM_KEY<br/> , P.POLICY_KEY<br/> , P.PAYMENT_DT_TIME<br/> , P.PAIDAMOUNT<br/> , P.STATUS<br/> , PO.CUSTOMERID<br/>FROM "INSURANCE_CLAIMS"."PAYMENTS" P<br/>JOIN "INSURANCE_CLAIMS"."POLICIES" PO<br/>ON P."POLICY_KEY" = PO."POLICY_KEY"<br/><strong><font color="#993300">WHERE P.PAYMENT_DT_TIME >= DATEADD(YEAR, -1 * {{HISTORY_DEPTH}}, CURRENT_DATE)</font></strong> Save the query and repeat the same step for all other tables that require a restriction on history depth. Once completed, republish the model and revisit your dashboard. In this example, here is how the data looks for the US users: Here is how the data looks for Australian users:
User Parameters Use Case - Enforcing Data Isolation / Row-Level Security Rules from Source Database This article explains two of the example use cases of User Parameters laid out in User Parameters - What They Are and What They're For (Example Use Cases) . The most common use case of User Parameters is to create a single dynamic data model that supports multiple database connections. A dynamic model has varying connection information that is resolved during runtime based on the currently logged-in user. These are two common scenarios that can be supported by a dynamic live model: Data are stored in multiple isolated, structurally identical databases and need to be federated in a single model (parameterized database location or name) Row-level security rules are already defined in the source database and need to be enforced to Sisense queries (parameterized username and password) Further details on each scenario and its implementation are provided below. 1. Data are stored in multiple isolated, structurally identical databases and need to be federated in a single model Certain organizations partition their data into separate isolated databases (e.g. one database per customer or region), typically due to laws, industry regulations, or company policies. For example, an insurance company serving the US and Australian markets may store the American customers' data in a Snowflake server located in Ohio and the Australian customers' data in a Snowflake server located in Sydney. We can serve both users in the US and Australia with a single data model without comprising security by using parameters to assign a connection string, database location, or database name to users and groups. For more information on how to create parameters, refer to this documentation: Personalization Parameters for Live Data Sources . Once the parameters are created and assigned to the appropriate groups and users, you can update your live model's connection settings and replace the hard-coded values with the parameters. Depending on the database vendor (Snowflake, Redshift, etc) and what is being parameterized, you can use the Location, Database, or Connection String fields. To select a parameter, click the dropdown menu to the right of the input field, then select the parameter name. The parameter name will then be populated in the input field in two pairs of curly brackets. Note that you can parameterize the whole or parts of a string. For example, if you opt to use the connection string field, and the databases are in the same server (they just have different warehouse/database names), you can specify the parameter as part of the string, as shown in the example below. jdbc://yoursnowflakeserver/?warehouse={{WAREHOUSE_NAME}}&db={{DATABASE_NAME}} If the two databases are stored in different servers, with different warehouse and database names, you can parameterize the whole string, as shown in the example below. The value of {{CONNECTION_STRING}} parameter is the entire connection string assigned to the groups/users. For example, this could be the connection string assigned to the American users: jdbc://yourUSAsnowflakeserver/?warehouse=USA_WH&db=USA_DB and this could be the connection string assigned to the Australian users: jdbc://yourAUSsnowflakeserver/?warehouse=AUS_WH&db=AUS_DB After updating the connection settings, republish the model and revisit your dashboard. The screenshots below show the same dashboard connected to the same data model, utilizing user parameters. When users open the dashboard, Sisense determines which database to be queried based on the value of the user parameter assigned to the logged-in user or the group they are a member of. Therefore, in this example, the entire dashboard displays only data from the region the user belongs to. This is most apparent in the State and City filters, as well as the Scatter Map widget. For USA users: For Australian users: Note that if further row-level restrictions are required within each database/region, data security rules still need to be established in the data model. 2. Row-level security rules are already defined in the source database and need to be enforced to Sisense queries In this scenario, each Sisense user or group has an associated account in the database server where row-level access rules have already been defined. By parameterizing the user and password used in the live model's connection settings, you don't have to recreate the security rules in the Sisense live model. In other words, when users open the dashboard, Sisense determines which database username and password will be used to establish the connection, based on the value of the user parameter assigned to the logged-in user or the group they are a member of. All associated row access policies from the source database are then enforced automatically to Sisense queries. The first step to implementing this use case is creating the username and password parameters, and assigning the appropriate values to the users and groups. After you create and assign these parameters, update your live model's connection settings and populate the User Name and Password input fields with the associated parameters, as shown in the example below. Save the changes and republish the model. Note that when you create the parameter for passwords, it's important to mark it as a hidden value for security. Selecting Yes for this option prevents assigned values from being displayed in plain text. Once the values are saved, they cannot be viewed anymore. It's also not possible to change the Hide Values option to No once the parameter has been created.
Analytical Need For a metric, I need to calculate the month difference between the min and max date of purchase for each brand in my dataset. I want the dates to be dynamic in respect to filtering, etc. I am using the attached .ecdata and .dash files for this solution. See this article on importing a .dash file, and this on importing an .sdata file. Challenge The MIN() and MX() dashboard functions do not work on date fields. And even if we create a Numeric Representation Of Date Fields , a simple subtraction of the results won't work because we can't just use the difference in days (think of the case where the dates are two days apart, but land in different months). How can we identify the earliest and latest date and calculate the month difference between them? Elasticube Prep(Step 1) Data Requirements In the Elasticube, create a custom field called [Date Numeric] to convert your date field to an integer using 10000*getyear(Date)+100*getmonth(Date)+getday(Date) Create this field in the fact table since it is a value we will be performing a calculation on. Dashboarding Solution (Step 2) We need to extract the year and month values from our [Date Numeric] field in order to calculate the month difference between to date values. To do this, we will reverse the logic used to convert the field to an integer. The formula syntax below achieves this for all three components of the earliest date. To generate the year, month and day corresponding to the latest date value, we can swap out MIN for MAX. Year of MIN Date Integer: FLOOR(min([Date Numeric])/10000) Return the Month of MIN Date: FLOOR((min([Date Numeric])-(10000*(FLOOR(min([Date Numeric])/10000))))/100) Return the Day of MIN Date: (min([Date Numeric])-(10000*(FLOOR(min([Date Numeric])/10000))))-(100*(FLOOR((min([Date Numeric])-(10000*FLOOR(min([Date Numeric])/10000)))/100))) For this specific use case, we want to get the month difference between the min and max: Final Solution - Month Difference Formula: ((FLOOR(max([Date Numeric])/10000) - FLOOR(min([Date Numeric])/10000) )*12) + (floor((max([Date Numeric])-(10000*(FLOOR(max([Date Numeric])/10000))))/100) - floor((min([Date Numeric])-(10000*(FLOOR(min([Date Numeric])/10000))))/100)) ((FLOOR(max([Date Numeric])/10000) - FLOOR(min([Date Numeric])/10000) )*12) + (floor((max([Date Numeric])-(10000*(FLOOR(max([Date Numeric])/10000))))/100) - floor((min([Date Numeric])-(10000*(FLOOR(min([Date Numeric])/10000))))/100))