Data Modeling for FACTS with Different Granularity

What is the best approach in data modeling FACTS with
different granularity?
For instance, I have Volume measures (item count, item
amount) at daily intervals, but Cost measures (Processing
costs, service costs for those items) at monthly and
quarterly intervals.
How best to construct the data model with FACT table(s)
able to query calculated measures like "daily unit cost"?
Is it inevitable that I would have to separate the volume
counts and cumulative costs into 2 separate FACT tables?
I am looking for ideas how to best to consolidate volume
and costs into ONE FACT table. I would imagine this is a
common situation where the volume info are not coming at
the same interval and aggregate as your costs info (vendor
sends invoices monthly or quarterly). So, please chime in
your thoughts.
Thanks in advance!This is what Analysis Services does for you behind the scenes -- It creates
data structures with whatever levels of aggregation you require so that
real-time aggregation doesn't need to take place when you query the data.
If you're using SQL Server only, you should look into using indexed views,
which SQL Server can use when appropriate, automatically (you do not need to
explicitely reference them in your queries).
You should not model different levels of aggregation in the same table, in
my opinion, as it creates logical ambiguities (the table will no longer
represent a single entity type).
"Steven Wong" <anonymous@.discussions.microsoft.com> wrote in message
news:ae6701c436b0$1139a130$a101280a@.phx.gbl...
> What is the best approach in data modeling FACTS with
> different granularity?
> For instance, I have Volume measures (item count, item
> amount) at daily intervals, but Cost measures (Processing
> costs, service costs for those items) at monthly and
> quarterly intervals.
> How best to construct the data model with FACT table(s)
> able to query calculated measures like "daily unit cost"?
> Is it inevitable that I would have to separate the volume
> counts and cumulative costs into 2 separate FACT tables?
> I am looking for ideas how to best to consolidate volume
> and costs into ONE FACT table. I would imagine this is a
> common situation where the volume info are not coming at
> the same interval and aggregate as your costs info (vendor
> sends invoices monthly or quarterly). So, please chime in
> your thoughts.
> Thanks in advance!|||Hi :
When u have 2 different fact tables with different granularities, the best
choice is to use them separatelly. The other way u should get into the
allocation problem. Remember u can connect them using 2 conformed dimensions
(Shared Dims). If u need help, just let me know
ALEJANDRO LEGUIZAMO
Colombia - SQL Server MVP
"Steven Wong" <anonymous@.discussions.microsoft.com> wrote in message
news:ae6701c436b0$1139a130$a101280a@.phx.gbl...
> What is the best approach in data modeling FACTS with
> different granularity?
> For instance, I have Volume measures (item count, item
> amount) at daily intervals, but Cost measures (Processing
> costs, service costs for those items) at monthly and
> quarterly intervals.
> How best to construct the data model with FACT table(s)
> able to query calculated measures like "daily unit cost"?
> Is it inevitable that I would have to separate the volume
> counts and cumulative costs into 2 separate FACT tables?
> I am looking for ideas how to best to consolidate volume
> and costs into ONE FACT table. I would imagine this is a
> common situation where the volume info are not coming at
> the same interval and aggregate as your costs info (vendor
> sends invoices monthly or quarterly). So, please chime in
> your thoughts.
> Thanks in advance!|||The following assumes that the Cost fact tables include unit cost
information. If not, you would have to derive them as something like last
know unit cost.
It sounds like you would need at least two fact tables: a monthly cost fact
table, a quarterly cost fact table, and the volume fact table. Populate the
two cost fact tables first, then reference them in your ETL for the volume
fact table. Add measures to your volume fact table for latest known unit
costs.
It is not a good idea to ever try to mix granularities in a fact table. You
either need to aggregate to a common level, or allocate down to a rate (as I
did above).
"Steven Wong" <anonymous@.discussions.microsoft.com> wrote in message
news:ae6701c436b0$1139a130$a101280a@.phx.gbl...
> What is the best approach in data modeling FACTS with
> different granularity?
> For instance, I have Volume measures (item count, item
> amount) at daily intervals, but Cost measures (Processing
> costs, service costs for those items) at monthly and
> quarterly intervals.
> How best to construct the data model with FACT table(s)
> able to query calculated measures like "daily unit cost"?
> Is it inevitable that I would have to separate the volume
> counts and cumulative costs into 2 separate FACT tables?
> I am looking for ideas how to best to consolidate volume
> and costs into ONE FACT table. I would imagine this is a
> common situation where the volume info are not coming at
> the same interval and aggregate as your costs info (vendor
> sends invoices monthly or quarterly). So, please chime in
> your thoughts.
> Thanks in advance!