Monday, 19 June 2023

Tableau vs Power BI | Fact Constellation Data Model Case Study

Hello Everyone

There is a use case to model the data at the reporting tool level which involves two different Fact tables that have some common dimensions and some dimensions specific to each Fact. So I have to create a Fact Constellation data model at reporting level. Primary tool for the requirement is Tableau but since, I was unable to achieve this using Tableau and I tried exploring the same with Power BI which seems to be possible there.

Use Case : Consider a dataset of an Organization, ABC. With organization there will be Headcount (total employees in an organization) and Exits (employees leaving the organization, which could be Voluntarily, Involuntary, Internal Transfer or Contingent Exits)

Some dimensions you can think of are Date, Location, ExitType (this tells what kind of exit, the employee has taken which means, Voluntarily, Involuntary etc.)

If you think of Facts and Dimensions, then these are the ones

  1. Fact_Headcount
  2. Fact_Exits
  3. Dim_Location
  4. Dim_Date
  5. Dim_ExitType

The common dimensions between the Headcount and Exits will be Dim_Date & Dim_Location. Dim_ExitType is a specific dimension for Fact_Exits.

You can question, why we are not having a single fact table for headcount and exits and the answer would be, Exit% is reported as [Exits / Total Headcount] and this analysis is done using the ExitType dimensions. Like Voluntary Exit%, Involuntary Exit% etc. If both, the Headcount and Exits are maintained in the same table, then slicing the Headcount and Exits would be challenging using the ExitType dimension. If you select any particular exit type, then records will be shown only for that exit type and the rest of the total headcount will be filtered out, Then how to get the Exit%?

Reporting Requirement: Business needs a report that shows trend lines showing the Voluntary Exit% (VE), Involuntary Exit% (IVE), Internal Transfer% (IF) etc using Tableau. These trends can be YTD, QTD, MTD, Last Thirteen Months, 12 Months Rolling etc. Now these are the solutions I can think of

  1. Approach 1 : Get a single table from the data source that has all the information of Headcount and Exits and write individual measures for VE, IVE and IF and show on the report. But, imagine, you have 10 exit types, or a new exit type gets added or you have to show a crosstab with grand total as well. All these things become difficult to manage if we go in this direction. Otherwise, use Exclude LOD (it also has a limitation).
  2. Approach 2: Data Model : If we model our data by separating Headcount and Exits and create a fact constellation model, then ExitType dimension will act only upon the Exits and will not filter the Headcount, solving our slicing issue. So how would the model look like?

What I'm trying to say is, the above model can be created in Power BI (it is actually a screenshot from Power BI) but creating the similar model using Tableau Data Model is not possible unless you combine the Headcount and Exits into single table through UNION or any other technique and then use in that way. But, that doesn't seem be an ideal solution here considering again filtering the facts becomes an obstacle.

So, as of today, (when this blog is written, 05th March 2023), Tableau doesn't support having two facts in its logical model (Using Relationships).

If you have any solutions, then please suggest me.

Why Approach 1 Explained above will not work?

-Yadnesh Madiwale

  1. Writing individual measures for each exit type? Assume if you have to show the trend for 10 Exit type or if you have another 15 sub-category of Exits for which you have to trend, will it be manageable to having these many measures? Another issue with this approach is, if you create a crosstab, then how will you show the grand total? It at least cannot be shown in the same crosstab, you have to maintain a separate sheet for grand total, always.
  2. Exclude LOD? It works fine as long as long there is a record available for the exit type for each month, otherwise, it fails and doesn't return correct results in case of QTD or YTD or 12 months rolling. You can do data densification/scaffolding, but that doesn't seem to help either.

No comments:

Post a Comment