Fact table and a duplicate one, please clarify

View previous topic View next topic Go down

Fact table and a duplicate one, please clarify

Post  platforminc on Fri Aug 10, 2012 10:33 am

Hi All,
I have a question that I had like to ask, we have a fact table that stores teh values of capacity of a storage unit. This information comes in say every 5 minutes, for example. Most of the time the capacity value of the storage unit is always fixed, it can however fluctuate a bit, for example the unit can have repairs carried out to it, or maybe a section of it is actually down. This then affects its capacity which needs to be reported by the source systems and then loaded into the WH.
The business has requested that they want the data to be viewed in a certain way, rather that see the data in ticks of every 5 minutes, they want to see the data in block of time based on changes to the capacity value.
So imagine the fact table having entries for every 5 minutes of the day, they now want to see blocks in this format.
Unit_ID Start_Date End_Date Capacity
1 01-07-2012 00:00 04-07-2012 04:00 5
1 04-07-2012 04:00 19-07-2012 04:00 3
1 19-07-2012 05:00 NULL 5

Although it might look like a typical SCD type 2, this table will actually hold measures that will duplicate the original fact table created, they both have FKs to dimensions and measures too, Is this approach a common one/best practice ?
Thanks in advance.

platforminc

Posts : 7
Join date : 2012-05-25

View user profile

Back to top Go down

Potential approach

Post  vickyejain on Mon Aug 20, 2012 3:53 am

I can't comment much on how common such a design practice is - I have seen cases where data is maintained similar to an SCD but you will really need to consider how growing data volume will affect your design. One option I would recommend is to continue storing the fact in the form of the 5 minute ticks you currently have and then create a view or aggregate table that summarizes the data as the business wants. In this manner, you can avoid the complications of maintaining an SCD on a large data volume as well as have a way to accommodate future business needs to slice/dice if needed (say a future report to show units with 100% capacity during time A to time B on a particular day').

Hope this helps.

vickyejain

Posts : 7
Join date : 2012-08-20

View user profile

Back to top Go down

Re: Fact table and a duplicate one, please clarify

Post  sgudavalli on Wed Aug 22, 2012 7:55 am


Its a periodic snapshot.. where in for every 5 minutes you know what is the storage capacity (It may be same or fluctuate)

now; the reporting users want to view the above storage capacity or (its storage fluctuations) per every hour/day/week etc...

you can achieve it by creating an aggregate on top of the base fact table (as per user requirements)...

and it should be okay because the grain is different from the base fact table..

sgudavalli

Posts : 29
Join date : 2010-06-10
Age : 33
Location : Pune, India

View user profile

Back to top Go down

Re: Fact table and a duplicate one, please clarify

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