Advice on schema. Is the table in question dimensional or factual?

[pmdci]

Hello,

I have a DW that contains data from our managed support department. One of the facts that I hold in our data warehouse is support incidents. Another is a fact table for server availability (uptime) per month.

This DW is never accessed directly by users. Instead there is a SQL Server Analysis Services (SSAS) tabular model in-between the DW and client tools such as Excel and Power BI.

I am planning to expand our current DW to hold SLA definitions. Since not every SLA applies to every customer, and the SLA targets might differ (and change through time) for each customer, I think I will employ it as follows:

https://i.imgur.com/zGzVGIH.png

I then plan to have measures stored in the Customer SLA table (green table), as it makes more sense for users navigating fields and measures through client tools such as Excel or Power BI to see measures under one table. Otherwise I would have different SLAs at different fact tables. For example some SLAs would be at the fact table containing support incidents, while others would be at the fact table containing server uptime.

It just seems easier to have all SLA related measures coming from one table. I also plan to hide the orange table from client tools.

Now for my question: Would you consider the green table to be a fact table or a dimension table? At first I thought of it as a slow changing dimension, but it does contain measures. So would the fact that it contain measures make it a fact table?

I am just thinking of best practices

Thank you for all for your time and support.

Regards,
P.

[ngalemmo]

What makes it a fact table is that it represents a business state. Dimensions provide context.

[pmdci]

What if it provides both, such as in this case.

[ngalemmo]

No, it does not provide both. The fact is a record of business events and states, in this case, the state of a service level agreement with it's performance metric.

While there is context to the metric (as with any fact table) the metric itself is not a context, it is a performance goal... it is a measure by which other incidents are compared.

[Sponsored content]