In my previous 3 blog posts I have discussed Kimball Principles in QlikView projects with a particular focus on Dimensions and how changes are managed in this type of table. In this post we will shift focus and focus on Fact tables and how these differ from Dimensions.
Fact tables are joined in a Star schema through Foreign key relationships. Wheras Dimensions contain descriptive attributes, Fact tables consist of Measurements, Metrics or Facts for a Business process. Fact tables are defined by the level of granularity for a particular Business process. Atomic grain refers to the lowest level at which data is captured by a particular Busines process. The grain of a SALES Fact table might be stated as ”Sales volume per Day per Article per Store”.The level of granularity is very important when designing Fact tables, as ultimately this level will help you to decide which Dimensions the Fact table will be connected to.
The contents of the Fact table, that is to say the measures, are usually categorized as follows:
- Additive – Measures that can be added across any dimension.
- Non Additive – Measures that cannot be added across any dimension.
- Semi Additive – Measures that can be added across some dimensions.
Special care must be taken with Non additive measures, for example percentages and ratios. A general rule of thumb here is to calculate these measures in the Front end tools.
As we have seen, the Kimball toolkit is a valuable template for modelling Dimension and Fact tables. The toolkit provides us with the necessary metadata from which we can build our tables in QlikView.
Scripting the Fact table in QlikView is a simple process. We use the Metadata from the template to define our columns and apply the correct formatting. Our Foreign key relations to the dimension tables will be mapped to the Fact table with the help of mapping tables.
Once the fact table has been loaded it is easily viewable in QVD Viewer. The contents of the Fact Table are as we would expect. The surrogate keys (Playeridx and Dateidx) give us the relationships to the Dimensions DimPlayer and DimDate and the metrics are clearly distinguishable. The InsertAuditKey is also clearly visable, thus enabling traceability through the ETL process.
In the datamodel view the Fact table sits comfortably between the two associated Dimensions.
In this blog post we have discussed Fact tables, what data they contain and the importance of granularity. In the next post I will be looking at how we can build an EDW structure on the file system with help of the Qlik Deployment Framework (QDF).