All Articles

Governed Data Models in Qlik

Photo by Kenny Eliason on Unsplash
Photo by Kenny Eliason on Unsplash

Overview

Creating a sharable enterprise model in Qlik involves a few unfamiliar steps. Hopefully this starting point will help get going on an enterprise and more sharable data model.

In Qlik, the modeling and sheets are tied a bit closer in coupling compared to Power BI (a tool I’m more familiar with). For replicating something like a governed enterprise data model separate from end user visualizations, something like the below approach can be taken.

Governed Model Steps and Example

In our example, we have a simple dim and fact table mocking up employee pay. Our goal is to load these into a model and have that model be usable by others. Assume we already have a connection to a snowflake database established in our Qlik space.

Our enterprise data model load script might look something like this. A few things worth noting:

  1. The sets at the top are defaults initially generated by Qlik but can be changed as needed.
  2. We uploaded master_measures.qvs with some starting variables to define measures.
  3. You’ll see the next piece of the load script references the master measures and connection already established.
  4. We load in our tables, making sure to keep all columns unique besides those that should be joined.
  5. We hide any join keys so that users do not reference them.
  6. We hide any fields that we want a “measure only” approach to be taken with. 
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$ ###0.00;-$ ###0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

// === Master Measures ===
$(Include=lib://Test Managed Space:DataFiles/master_measures.qvs);

// === Connection ===
LIB CONNECT TO 'Test Managed Space:Snowflake_SnowflakeAcctName.snowflakecomputing.com';

// === Employee Dimension ===
[DimEmployee]:
LOAD
    "DIM_EMPLOYEE_KEY",
    "EMPLOYEE_ID" as EmployeeId,
    "NAME" as Name,
    "DEPARTMENT" as Department;
SELECT
    "DIM_EMPLOYEE_KEY",
    "EMPLOYEE_ID",
    "NAME",
    "DEPARTMENT"
FROM "LEARNING_DB"."DW"."DIM_EMPLOYEE";

// === Employee Pay Fact ===
[FactEmployeePay]:
LOAD
    "DIM_EMPLOYEE_KEY",
    "PAY_DATE" as PayDate,
    "PAY_AMOUNT" as PayAmount;
SELECT
    "DIM_EMPLOYEE_KEY",
    "PAY_DATE",
    "PAY_AMOUNT"
FROM "LEARNING_DB"."DW"."FACT_EMPLOYEE_PAY";

TAG FIELD [DIM_EMPLOYEE_KEY] WITH '$hidden', '$system';

STORE DimEmployee INTO [lib://Test Managed Space:DataFiles/Enterprise_Employee_Pay_Model/DimEmployee.qvd] (qvd);
STORE FactEmployeePay INTO [lib://Test Managed Space:DataFiles/Enterprise_Employee_Pay_Model/FactEmployeePay.qvd] (qvd);

master_measures.qvs:

SET vTotalPay = 'Sum(PayAmount)';
SET vAvgPay = 'Avg(PayAmount)';
SET vMaxPay = 'Max(PayAmount)';
SET vMinPay = 'Min(PayAmount)';
SET vPayCount = 'Count(PayAmount)';
SET vDistinctEmployees = 'Count(DISTINCT EmployeeId)';
SET vAvgPayPerEmployee = 'Sum(PayAmount) / Count(DISTINCT EmployeeId)';

SET vTotalPayYTD = 'Sum({<PayDate={">=$(=YearStart(Today()))<=$(=Today())"}>} PayAmount)';
SET vTotalPayMTD = 'Sum({<PayDate={">=$(=MonthStart(Today()))<=$(=Today())"}>} PayAmount)';

End User App Steps and Example

When we get to the end user model, the load script is now simple. We just need to reference the qvs master measures and the qvds we stored as a wildcard.

$(Include=lib://Test Managed Space:DataFiles/master_measures.qvs);

LOAD *
FROM [lib://Test Managed Space:DataFiles/Enterprise_Employee_Pay_Model/*.qvd] (qvd);

Finally, we need to add measures that reference the variables. One example is TotalPay, which we can include $(vTotalPay) as the expression and ='$(vTotalPay)' in the description.

Automated Reloads

Now that we have a main governed app with data and dependent datasets, next up is scheduling reloads. We can setup an automation to first refresh the main governed app then do a loop through other apps (List Spaces -> List Apps -> Get Load Script -> Condition on ‘Q Script contains FROM [lib://Test Managed Space:DataFiles/Enterprise_Employee_Pay_Model/*.qvd] (qvd);’ -> Do Reload if yes).

Conclusion

With this approach, your enterprise data model stays centralized and maintainable in one place. End users benefit from a clean, simple load script that pulls pre-built QVDs and standardized measures. There’s no need to understand the underlying database structure or write complex queries. When business logic changes, you only need to update the governed model and its master measures file, and downstream apps pick up those changes on their next reload. This separation of concerns makes Qlik feel a bit more like the semantic layer approach you might be used to in other BI tools.