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

View previous topic View next topic Go down

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

Post  pmdci on Wed Mar 30, 2016 5:05 am

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:

http://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.

pmdci

Posts : 2
Join date : 2016-03-30

View user profile

Back to top Go down

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

Post  ngalemmo on Wed Mar 30, 2016 10:48 am

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  pmdci on Wed Mar 30, 2016 10:59 am

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

pmdci

Posts : 2
Join date : 2016-03-30

View user profile

Back to top Go down

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

Post  ngalemmo on Wed Mar 30, 2016 5:16 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum