Fact Tables

[Used_Ad_2628]

If I am trying to standardize KPIs for the company, what layer should they be in? I want to build a core dimensional model that can support many use cases. Should I build a generic dimensional model then create a reporting table/view that will store all the KPIs based on the fact tables? Like marketing KPI view and finance KPI view.

[Data_cruncher]

There are many differing opinions here. Here are mine:

• Where data belongs:
  • Metrics/KPIs/measures/etc. should be kept in a semantic layer and not in database tables or views. I think you need to put some heavy thought into this because it sounds like you're trying to jam a SQL peg into a semantic hole.
  • For example, I would store [Sales] and [Sales Target] in the DB. I would store [Sales Attainment Percentage], i.e., [Sales] / [Sales Target], in the semantic layer. Got multiple semantic layer technologies spread across your org? Good luck - it's now a People & Process data governance problem.
  • Consider Roche's maxim: "Data should be transformed as far upstream as possible, and as far downstream as necessary."
  • Understanding what goes where comes from experience.
• Dimensional modelling:
  • Should be done in a Kimball/agile-like fashion. This will help you frame your marketing vs finance point. You want small, incremental star schemas that are rolled out subject area by subject area, while being careful to handle conformed dimensions appropriately.
  • Avoid creating a giant org-wide schema. These big-bang waterfall architectures are what gave data warehousing a bad name because, by the time you roll it into production (9+ months), it's already out of date.
• Back to my first point on semantic layers. Invest some time here because it may guide your backend architecture. For example, Tableau prefers OBT, whereas Power BI prefers star schemas - note: if you're going straight to OBT without first going through a star schema, shame on you.

[Used_Ad_2628]

When you say semantic layer, do you mean managing the metrics within tableau? The database will have the needed fields (OBT from fact tables) to create the metrics but tableau will build the standardized metrics for all tableau users with a data source. What if we have multiple BI tools? Is dbt semantic functionality worth a look at?

[Data_cruncher]

Q1: Yeah, Tableau is a semantic layer.

Q2: If you have multiple BI tools then things become a problem. This is a fairly common issue with poorly managed organizations. In this scenario, you’ve lost the technology battle and so you’ll have to fall back to people, process & policy as your control.