Data Source Column Changes

I have some Dynamic SQL that builds a table with the top 20 item numbers as columns, Stores as rows and an X marking the intersection of Store/Item. This produces a table which is a matrix showing which stores are low on which items.
This table is generated once a week and each week will have different column names because the item numbers which the stores are low on changes from week to week.
How can I write a report that will handle the column names of the data table changing without having to do maintenance on the report every week?

Thanks,
Mark Redman.

One way of doing this is to move the dynamic SQL into a stored procedure and return a dataset which always has fixed column names. The actual item information (item number) would be e.g. the first row in that generated dataset.

-- Robert

Data not display in dropdown lists

I have 2 drop down lists in my report. When you select an item from
the 1st list, the 2nd list gets populated accordingly. For some
records in list 1 you get only a single record in the 2nd drop down.
In these cases, you don't see the item in the drop downlist, but I
tried checking in the source of the page n could see the particular
item. Any idea why this is happening?On Mar 1, 10:01 am, cham...@.gmail.com wrote:
> I have 2 drop down lists in my report. When you select an item from
> the 1st list, the 2nd list gets populated accordingly. For some
> records in list 1 you get only a single record in the 2nd drop down.
> In these cases, you don't see the item in the drop downlist, but I
> tried checking in the source of the page n could see the particular
> item. Any idea why this is happening?
It sounds kind of like you are returning a null value in the 2nd drop-
down list (and possibly the correct one after that). Have you checked
the dataset to determine what the possibilities are for the second
drop-down list given the first one's values?
Enrique Martinez
Sr. SQL Server Developer

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!

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!