Re: Best design practice for temporal facts...

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Giri (gtharman_at_newsgroups.nospam)
Date: 05/26/04 

Date: Wed, 26 May 2004 10:04:10 +0100

useful information thanks.. But it doesn't really address my question about
how best to store a fact that changes over time to allow

1) Time based analysis
2) Snapshot reporting at any given moment in time.

I haven't made this clear enough so maybe an example would be a good idea...

lets say you have data tracking utilization of a resource. It is easy to
calculate the percentages etc..

Now do you store in a fact table the following columns of data:

DateTimeStamp (When was this data captured, FK to time dimension)
Which Machine
TotalUtilization

This is fine for any analysis that restricts itself to that particular time
slice.. but what happens when you want to start analyzing over different
time periods.. say rollup to a qtr on qtr analysis? the other option is
just to show the change in utilization at any given moment.

DateTimeStamp
WhichMachine
ChangeInUtilization

This way, a sum of all utilizations up to a given point would give you a net
utilization at that moment in time.. and would also allow you to do time
based analysis...

Which of the two, or another, is the better approach?

Thanks

Giri

""Yuan Shao"" <v-yshao@online.microsoft.com> wrote in message
news:2G%23IvtsQEHA.3804@cpmsftngxa10.phx.gbl...
> Hi Giri,
>
> As I understand, you want to store the information in the data warehouse.
> The data in the fact table changes often and you want to update the cube
in
> time.
>
> You may try to use Real-Time cubes in your data warehouse. Real-time OLAP
> resolves this issue by enabling ROLAP dimensions and partitions to
> automatically refresh themselves when data in their underlying dimension
or
> fact tables changes. When working in concert with SQL Server 2000 as the
> relational data source, Analysis Services can periodically poll the data
> source for notifications about updates to dimension or fact tables
> associated with specific ROLAP dimensions or partitions enabled for
> real-time updates. If the Analysis server finds that a change to a
> dimension or fact table has occurred, it can respond to the notifications
> by flushing the Analysis server cache and automatically reprocessing the
> associated ROLAP dimensions or partitions.
>
> For more information regarding Real-Time Cubes, please refer to the
> following article:
> Real-Time Cubes
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/ag
> cubevarieties_0o4z.asp
>
> Best wishes,
>
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>
>

Relevant Pages

  • Re: Switch MDX according to VALUE
    ... I need you to post the entire MDX, not just the IIF part. ... If it is stored under the Measures dimension, ... > I followed your advice entering to MDX Sample Application, find "STORE EL" ...
    (microsoft.public.sqlserver.olap)
  • Re: how to get a properties of another dimension?
    ... Looking at the schema of the Foodmart HR Cube, ... "Reference" dimension in AS 2005 terms, ... One way to infer the relationship between an employee and the ... corresponding store ...
    (microsoft.public.sqlserver.olap)
  • custom rollup formula slow
    ... Let's say I've got a cube with these 2 dimensions: ... ...Store ... it's aggregating the Time dimension before the Store dimension... ... Looking at a slice closer to the leaf performs OK. ...
    (microsoft.public.sqlserver.olap)
  • Advice needed to build a lean-to shed...
    ... I need to store my car somewhere for at least 3 years. ... Austin Healey 3000 and is about 12x5 in overall dimension. ...
    (alt.home.repair)