If you have two fact tables that share a dimension is it better to duplicate
the dimension by adding a copy of it to the warehouse or should you share
the single dimension between the fact tables?based on what you are asking - in my opinion i would opt to share the
dimension. trying to keep a second copy in sync seems like it would be added
overhead with very little benefit.
time dimension would be a good example of this - no sense in having multiple
time dimensions.
there are some good books out there on this stuff - look for books from
ralph kimball.
hth
Sal
"Stephen" <swrothfuss@.hotmail.com> wrote in message
news:%23JNCPSVAEHA.392@.TK2MSFTNGP12.phx.gbl...
> If you have two fact tables that share a dimension is it better to
duplicate
> the dimension by adding a copy of it to the warehouse or should you share
> the single dimension between the fact tables?
>
Showing posts with label warehouse. Show all posts
Showing posts with label warehouse. Show all posts
Tuesday, March 27, 2012
Dimensional Design Question
I have done some reading on dimensional modeling and am a bit confused as to
how I create a dimension in our data warehouse using our transactional sourc
e.
I have a customer table which contains the customer id, customer name,
region codes and industry codes. Naturally I also have a region table and
industry table which has the region/industry descriptions.
I have read that the data warehouse should be designed using a star schema.
If that is the case, should I be creating a customer dimension with the
customer id, customer name, region desc, and industry name? Or should I
have a customer dimension, an industry dimension, and a region dimension?It depends on the kind of your analysis.
If Region and Industry are attributes of the customer and not
independent attributes of the fact you want to measure, they should be
normalized into the Customer dimension. If you want to track historycal
changes of Region/Industry of the customer, you should use SCD Type II
(Slowly Changing Dimension).
Only in particular scenarios you should consider a snowflake schema
instead of a star schema, but this is an exception and in my experience
you can safely avoid it in most of the models you have to manage.
I suggest you to read Kimball's books
(http://www.kimballgroup.com/html/books.html) as a reference for DW
modeling.
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
JP wrote:
> I have done some reading on dimensional modeling and am a bit confused as
to
> how I create a dimension in our data warehouse using our transactional sou
rce.
> I have a customer table which contains the customer id, customer name,
> region codes and industry codes. Naturally I also have a region table an
d
> industry table which has the region/industry descriptions.
> I have read that the data warehouse should be designed using a star schema
.
> If that is the case, should I be creating a customer dimension with the
> customer id, customer name, region desc, and industry name? Or should I
> have a customer dimension, an industry dimension, and a region dimension?|||thanks marco. i do have the msft data warehouse toolkit but it doesn't
address this issue directly.
the region and industry are attributes of the customer so sounds like i
should have them "normalized" within the customer attribute. the fact tabl
e
is based on customer but i want to be able to roll-up the data to report by
industry, region, sub-industry, etc.
one item i left out was sub-industry. each sub-industry belongs to an
industry. so my customer dimension should look like the following?
customerkey (surrogate)
customerid (business key from source system)
region
industry
subindustry
"Marco Russo" wrote:
> It depends on the kind of your analysis.
> If Region and Industry are attributes of the customer and not
> independent attributes of the fact you want to measure, they should be
> normalized into the Customer dimension. If you want to track historycal
> changes of Region/Industry of the customer, you should use SCD Type II
> (Slowly Changing Dimension).
> Only in particular scenarios you should consider a snowflake schema
> instead of a star schema, but this is an exception and in my experience
> you can safely avoid it in most of the models you have to manage.
> I suggest you to read Kimball's books
> (http://www.kimballgroup.com/html/books.html) as a reference for DW
> modeling.
> Marco Russo
> http://www.sqlbi.eu
> http://www.sqljunkies.com/weblog/sqlbi
>
> JP wrote:
>|||Yes, you're right.
Flatten all customer attributes (Region, Industry, Subindustry, ...)
into Customer dimension.
You will find it's a very good schema to query now and to maintain in
the future, when you will add some other attributes.
Just to make an example: if you would have started with several tables,
imagine how adding a subindustry attribute would have impacted on your
schema and on your ETL implementation...
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
JP wrote:[vbcol=seagreen]
> thanks marco. i do have the msft data warehouse toolkit but it doesn't
> address this issue directly.
> the region and industry are attributes of the customer so sounds like i
> should have them "normalized" within the customer attribute. the fact ta
ble
> is based on customer but i want to be able to roll-up the data to report b
y
> industry, region, sub-industry, etc.
> one item i left out was sub-industry. each sub-industry belongs to an
> industry. so my customer dimension should look like the following?
> customerkey (surrogate)
> customerid (business key from source system)
> region
> industry
> subindustry
>
> "Marco Russo" wrote:
>
how I create a dimension in our data warehouse using our transactional sourc
e.
I have a customer table which contains the customer id, customer name,
region codes and industry codes. Naturally I also have a region table and
industry table which has the region/industry descriptions.
I have read that the data warehouse should be designed using a star schema.
If that is the case, should I be creating a customer dimension with the
customer id, customer name, region desc, and industry name? Or should I
have a customer dimension, an industry dimension, and a region dimension?It depends on the kind of your analysis.
If Region and Industry are attributes of the customer and not
independent attributes of the fact you want to measure, they should be
normalized into the Customer dimension. If you want to track historycal
changes of Region/Industry of the customer, you should use SCD Type II
(Slowly Changing Dimension).
Only in particular scenarios you should consider a snowflake schema
instead of a star schema, but this is an exception and in my experience
you can safely avoid it in most of the models you have to manage.
I suggest you to read Kimball's books
(http://www.kimballgroup.com/html/books.html) as a reference for DW
modeling.
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
JP wrote:
> I have done some reading on dimensional modeling and am a bit confused as
to
> how I create a dimension in our data warehouse using our transactional sou
rce.
> I have a customer table which contains the customer id, customer name,
> region codes and industry codes. Naturally I also have a region table an
d
> industry table which has the region/industry descriptions.
> I have read that the data warehouse should be designed using a star schema
.
> If that is the case, should I be creating a customer dimension with the
> customer id, customer name, region desc, and industry name? Or should I
> have a customer dimension, an industry dimension, and a region dimension?|||thanks marco. i do have the msft data warehouse toolkit but it doesn't
address this issue directly.
the region and industry are attributes of the customer so sounds like i
should have them "normalized" within the customer attribute. the fact tabl
e
is based on customer but i want to be able to roll-up the data to report by
industry, region, sub-industry, etc.
one item i left out was sub-industry. each sub-industry belongs to an
industry. so my customer dimension should look like the following?
customerkey (surrogate)
customerid (business key from source system)
region
industry
subindustry
"Marco Russo" wrote:
> It depends on the kind of your analysis.
> If Region and Industry are attributes of the customer and not
> independent attributes of the fact you want to measure, they should be
> normalized into the Customer dimension. If you want to track historycal
> changes of Region/Industry of the customer, you should use SCD Type II
> (Slowly Changing Dimension).
> Only in particular scenarios you should consider a snowflake schema
> instead of a star schema, but this is an exception and in my experience
> you can safely avoid it in most of the models you have to manage.
> I suggest you to read Kimball's books
> (http://www.kimballgroup.com/html/books.html) as a reference for DW
> modeling.
> Marco Russo
> http://www.sqlbi.eu
> http://www.sqljunkies.com/weblog/sqlbi
>
> JP wrote:
>|||Yes, you're right.
Flatten all customer attributes (Region, Industry, Subindustry, ...)
into Customer dimension.
You will find it's a very good schema to query now and to maintain in
the future, when you will add some other attributes.
Just to make an example: if you would have started with several tables,
imagine how adding a subindustry attribute would have impacted on your
schema and on your ETL implementation...
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
JP wrote:[vbcol=seagreen]
> thanks marco. i do have the msft data warehouse toolkit but it doesn't
> address this issue directly.
> the region and industry are attributes of the customer so sounds like i
> should have them "normalized" within the customer attribute. the fact ta
ble
> is based on customer but i want to be able to roll-up the data to report b
y
> industry, region, sub-industry, etc.
> one item i left out was sub-industry. each sub-industry belongs to an
> industry. so my customer dimension should look like the following?
> customerkey (surrogate)
> customerid (business key from source system)
> region
> industry
> subindustry
>
> "Marco Russo" wrote:
>
Dimensional Design Question
I have done some reading on dimensional modeling and am a bit confused as to
how I create a dimension in our data warehouse using our transactional source.
I have a customer table which contains the customer id, customer name,
region codes and industry codes. Naturally I also have a region table and
industry table which has the region/industry descriptions.
I have read that the data warehouse should be designed using a star schema.
If that is the case, should I be creating a customer dimension with the
customer id, customer name, region desc, and industry name? Or should I
have a customer dimension, an industry dimension, and a region dimension?
It depends on the kind of your analysis.
If Region and Industry are attributes of the customer and not
independent attributes of the fact you want to measure, they should be
normalized into the Customer dimension. If you want to track historycal
changes of Region/Industry of the customer, you should use SCD Type II
(Slowly Changing Dimension).
Only in particular scenarios you should consider a snowflake schema
instead of a star schema, but this is an exception and in my experience
you can safely avoid it in most of the models you have to manage.
I suggest you to read Kimball's books
(http://www.kimballgroup.com/html/books.html) as a reference for DW
modeling.
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
JP wrote:
> I have done some reading on dimensional modeling and am a bit confused as to
> how I create a dimension in our data warehouse using our transactional source.
> I have a customer table which contains the customer id, customer name,
> region codes and industry codes. Naturally I also have a region table and
> industry table which has the region/industry descriptions.
> I have read that the data warehouse should be designed using a star schema.
> If that is the case, should I be creating a customer dimension with the
> customer id, customer name, region desc, and industry name? Or should I
> have a customer dimension, an industry dimension, and a region dimension?
|||thanks marco. i do have the msft data warehouse toolkit but it doesn't
address this issue directly.
the region and industry are attributes of the customer so sounds like i
should have them "normalized" within the customer attribute. the fact table
is based on customer but i want to be able to roll-up the data to report by
industry, region, sub-industry, etc.
one item i left out was sub-industry. each sub-industry belongs to an
industry. so my customer dimension should look like the following?
customerkey (surrogate)
customerid (business key from source system)
region
industry
subindustry
"Marco Russo" wrote:
> It depends on the kind of your analysis.
> If Region and Industry are attributes of the customer and not
> independent attributes of the fact you want to measure, they should be
> normalized into the Customer dimension. If you want to track historycal
> changes of Region/Industry of the customer, you should use SCD Type II
> (Slowly Changing Dimension).
> Only in particular scenarios you should consider a snowflake schema
> instead of a star schema, but this is an exception and in my experience
> you can safely avoid it in most of the models you have to manage.
> I suggest you to read Kimball's books
> (http://www.kimballgroup.com/html/books.html) as a reference for DW
> modeling.
> Marco Russo
> http://www.sqlbi.eu
> http://www.sqljunkies.com/weblog/sqlbi
>
> JP wrote:
>
|||Yes, you're right.
Flatten all customer attributes (Region, Industry, Subindustry, ...)
into Customer dimension.
You will find it's a very good schema to query now and to maintain in
the future, when you will add some other attributes.
Just to make an example: if you would have started with several tables,
imagine how adding a subindustry attribute would have impacted on your
schema and on your ETL implementation...
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
JP wrote:[vbcol=seagreen]
> thanks marco. i do have the msft data warehouse toolkit but it doesn't
> address this issue directly.
> the region and industry are attributes of the customer so sounds like i
> should have them "normalized" within the customer attribute. the fact table
> is based on customer but i want to be able to roll-up the data to report by
> industry, region, sub-industry, etc.
> one item i left out was sub-industry. each sub-industry belongs to an
> industry. so my customer dimension should look like the following?
> customerkey (surrogate)
> customerid (business key from source system)
> region
> industry
> subindustry
>
> "Marco Russo" wrote:
how I create a dimension in our data warehouse using our transactional source.
I have a customer table which contains the customer id, customer name,
region codes and industry codes. Naturally I also have a region table and
industry table which has the region/industry descriptions.
I have read that the data warehouse should be designed using a star schema.
If that is the case, should I be creating a customer dimension with the
customer id, customer name, region desc, and industry name? Or should I
have a customer dimension, an industry dimension, and a region dimension?
It depends on the kind of your analysis.
If Region and Industry are attributes of the customer and not
independent attributes of the fact you want to measure, they should be
normalized into the Customer dimension. If you want to track historycal
changes of Region/Industry of the customer, you should use SCD Type II
(Slowly Changing Dimension).
Only in particular scenarios you should consider a snowflake schema
instead of a star schema, but this is an exception and in my experience
you can safely avoid it in most of the models you have to manage.
I suggest you to read Kimball's books
(http://www.kimballgroup.com/html/books.html) as a reference for DW
modeling.
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
JP wrote:
> I have done some reading on dimensional modeling and am a bit confused as to
> how I create a dimension in our data warehouse using our transactional source.
> I have a customer table which contains the customer id, customer name,
> region codes and industry codes. Naturally I also have a region table and
> industry table which has the region/industry descriptions.
> I have read that the data warehouse should be designed using a star schema.
> If that is the case, should I be creating a customer dimension with the
> customer id, customer name, region desc, and industry name? Or should I
> have a customer dimension, an industry dimension, and a region dimension?
|||thanks marco. i do have the msft data warehouse toolkit but it doesn't
address this issue directly.
the region and industry are attributes of the customer so sounds like i
should have them "normalized" within the customer attribute. the fact table
is based on customer but i want to be able to roll-up the data to report by
industry, region, sub-industry, etc.
one item i left out was sub-industry. each sub-industry belongs to an
industry. so my customer dimension should look like the following?
customerkey (surrogate)
customerid (business key from source system)
region
industry
subindustry
"Marco Russo" wrote:
> It depends on the kind of your analysis.
> If Region and Industry are attributes of the customer and not
> independent attributes of the fact you want to measure, they should be
> normalized into the Customer dimension. If you want to track historycal
> changes of Region/Industry of the customer, you should use SCD Type II
> (Slowly Changing Dimension).
> Only in particular scenarios you should consider a snowflake schema
> instead of a star schema, but this is an exception and in my experience
> you can safely avoid it in most of the models you have to manage.
> I suggest you to read Kimball's books
> (http://www.kimballgroup.com/html/books.html) as a reference for DW
> modeling.
> Marco Russo
> http://www.sqlbi.eu
> http://www.sqljunkies.com/weblog/sqlbi
>
> JP wrote:
>
|||Yes, you're right.
Flatten all customer attributes (Region, Industry, Subindustry, ...)
into Customer dimension.
You will find it's a very good schema to query now and to maintain in
the future, when you will add some other attributes.
Just to make an example: if you would have started with several tables,
imagine how adding a subindustry attribute would have impacted on your
schema and on your ETL implementation...
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
JP wrote:[vbcol=seagreen]
> thanks marco. i do have the msft data warehouse toolkit but it doesn't
> address this issue directly.
> the region and industry are attributes of the customer so sounds like i
> should have them "normalized" within the customer attribute. the fact table
> is based on customer but i want to be able to roll-up the data to report by
> industry, region, sub-industry, etc.
> one item i left out was sub-industry. each sub-industry belongs to an
> industry. so my customer dimension should look like the following?
> customerkey (surrogate)
> customerid (business key from source system)
> region
> industry
> subindustry
>
> "Marco Russo" wrote:
Dimension values without data in a fact table
I have an ODS system and a Data warehouse system
I have a Sales fact table in the ODS system and I have these fields:
SALES
ID_CUSTOMER (PK),
ID_MODEL (PK),
ID_TIME (PK),
SALES,
QUANT_ART,
COST
Then in some records in the fields ID_Time or ID_Model or ID_Customer I
don’t have values (NULL) because in the transactional systems these record
don’t have values (NULL).
The users want to generate aggregate reports with the Sales table...
The question is:
I have to put a “dummy” value in the dimensions Customer, Model and Time
(for example “0”) and put this value in the fact table if the dimensions
fields have NULL values?
Or I have to leave the NULL values?
What is the best choice? Why?
Personally -- and this really does boil down to personal preference -- I
believe that one of the key things that should happen during an ETL is
elimination of all "questionable" data. That includes unknown data -- can
you really report on something that's unknown? At the least, generate
well-known tokens to replace the NULLs with. If possible, get rid of those
rows on the way in (of course, that really depends on context) -- perhaps
they can appear in the aggregate data, but not in the line-level data?
There are various ways of dealing with the problem, but personally I am very
firm when designing data warehouses and ensure that, one way or another,
there will be absolutely no NULLs in the database.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"CLAUDIO" <CLAUDIO@.discussions.microsoft.com> wrote in message
news:937E414E-AB9A-4E4D-8A58-D8251139550E@.microsoft.com...
> I have an ODS system and a Data warehouse system
> I have a Sales fact table in the ODS system and I have these fields:
> SALES
> ID_CUSTOMER (PK),
> ID_MODEL (PK),
> ID_TIME (PK),
> SALES,
> QUANT_ART,
> COST
>
> Then in some records in the fields ID_Time or ID_Model or ID_Customer I
> don't have values (NULL) because in the transactional systems these record
> don't have values (NULL).
> The users want to generate aggregate reports with the Sales table...
> The question is:
> I have to put a "dummy" value in the dimensions Customer, Model and Time
> (for example "0") and put this value in the fact table if the dimensions
> fields have NULL values?
> Or I have to leave the NULL values?
> What is the best choice? Why?
>
sql
I have a Sales fact table in the ODS system and I have these fields:
SALES
ID_CUSTOMER (PK),
ID_MODEL (PK),
ID_TIME (PK),
SALES,
QUANT_ART,
COST
Then in some records in the fields ID_Time or ID_Model or ID_Customer I
don’t have values (NULL) because in the transactional systems these record
don’t have values (NULL).
The users want to generate aggregate reports with the Sales table...
The question is:
I have to put a “dummy” value in the dimensions Customer, Model and Time
(for example “0”) and put this value in the fact table if the dimensions
fields have NULL values?
Or I have to leave the NULL values?
What is the best choice? Why?
Personally -- and this really does boil down to personal preference -- I
believe that one of the key things that should happen during an ETL is
elimination of all "questionable" data. That includes unknown data -- can
you really report on something that's unknown? At the least, generate
well-known tokens to replace the NULLs with. If possible, get rid of those
rows on the way in (of course, that really depends on context) -- perhaps
they can appear in the aggregate data, but not in the line-level data?
There are various ways of dealing with the problem, but personally I am very
firm when designing data warehouses and ensure that, one way or another,
there will be absolutely no NULLs in the database.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"CLAUDIO" <CLAUDIO@.discussions.microsoft.com> wrote in message
news:937E414E-AB9A-4E4D-8A58-D8251139550E@.microsoft.com...
> I have an ODS system and a Data warehouse system
> I have a Sales fact table in the ODS system and I have these fields:
> SALES
> ID_CUSTOMER (PK),
> ID_MODEL (PK),
> ID_TIME (PK),
> SALES,
> QUANT_ART,
> COST
>
> Then in some records in the fields ID_Time or ID_Model or ID_Customer I
> don't have values (NULL) because in the transactional systems these record
> don't have values (NULL).
> The users want to generate aggregate reports with the Sales table...
> The question is:
> I have to put a "dummy" value in the dimensions Customer, Model and Time
> (for example "0") and put this value in the fact table if the dimensions
> fields have NULL values?
> Or I have to leave the NULL values?
> What is the best choice? Why?
>
sql
Sunday, March 25, 2012
Dimension values without data in a fact table
I have an ODS system and a Data warehouse system
I have a Sales fact table in the ODS system and I have these fields:
SALES
ID_CUSTOMER (PK),
ID_MODEL (PK),
ID_TIME (PK),
SALES,
QUANT_ART,
COST
Then in some records in the fields ID_Time or ID_Model or ID_Customer I
don’t have values (NULL) because in the transactional systems these record
don’t have values (NULL).
The users want to generate aggregate reports with the Sales table...
The question is:
I have to put a “dummy” value in the dimensions Customer, Model and Time
(for example “0”) and put this value in the fact table if the dimensions
fields have NULL values'
Or I have to leave the NULL values?
What is the best choice? Why?Personally -- and this really does boil down to personal preference -- I
believe that one of the key things that should happen during an ETL is
elimination of all "questionable" data. That includes unknown data -- can
you really report on something that's unknown? At the least, generate
well-known tokens to replace the NULLs with. If possible, get rid of those
rows on the way in (of course, that really depends on context) -- perhaps
they can appear in the aggregate data, but not in the line-level data?
There are various ways of dealing with the problem, but personally I am very
firm when designing data warehouses and ensure that, one way or another,
there will be absolutely no NULLs in the database.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"CLAUDIO" <CLAUDIO@.discussions.microsoft.com> wrote in message
news:937E414E-AB9A-4E4D-8A58-D8251139550E@.microsoft.com...
> I have an ODS system and a Data warehouse system
> I have a Sales fact table in the ODS system and I have these fields:
> SALES
> ID_CUSTOMER (PK),
> ID_MODEL (PK),
> ID_TIME (PK),
> SALES,
> QUANT_ART,
> COST
>
> Then in some records in the fields ID_Time or ID_Model or ID_Customer I
> don't have values (NULL) because in the transactional systems these record
> don't have values (NULL).
> The users want to generate aggregate reports with the Sales table...
> The question is:
> I have to put a "dummy" value in the dimensions Customer, Model and Time
> (for example "0") and put this value in the fact table if the dimensions
> fields have NULL values'
> Or I have to leave the NULL values?
> What is the best choice? Why?
>
I have a Sales fact table in the ODS system and I have these fields:
SALES
ID_CUSTOMER (PK),
ID_MODEL (PK),
ID_TIME (PK),
SALES,
QUANT_ART,
COST
Then in some records in the fields ID_Time or ID_Model or ID_Customer I
don’t have values (NULL) because in the transactional systems these record
don’t have values (NULL).
The users want to generate aggregate reports with the Sales table...
The question is:
I have to put a “dummy” value in the dimensions Customer, Model and Time
(for example “0”) and put this value in the fact table if the dimensions
fields have NULL values'
Or I have to leave the NULL values?
What is the best choice? Why?Personally -- and this really does boil down to personal preference -- I
believe that one of the key things that should happen during an ETL is
elimination of all "questionable" data. That includes unknown data -- can
you really report on something that's unknown? At the least, generate
well-known tokens to replace the NULLs with. If possible, get rid of those
rows on the way in (of course, that really depends on context) -- perhaps
they can appear in the aggregate data, but not in the line-level data?
There are various ways of dealing with the problem, but personally I am very
firm when designing data warehouses and ensure that, one way or another,
there will be absolutely no NULLs in the database.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"CLAUDIO" <CLAUDIO@.discussions.microsoft.com> wrote in message
news:937E414E-AB9A-4E4D-8A58-D8251139550E@.microsoft.com...
> I have an ODS system and a Data warehouse system
> I have a Sales fact table in the ODS system and I have these fields:
> SALES
> ID_CUSTOMER (PK),
> ID_MODEL (PK),
> ID_TIME (PK),
> SALES,
> QUANT_ART,
> COST
>
> Then in some records in the fields ID_Time or ID_Model or ID_Customer I
> don't have values (NULL) because in the transactional systems these record
> don't have values (NULL).
> The users want to generate aggregate reports with the Sales table...
> The question is:
> I have to put a "dummy" value in the dimensions Customer, Model and Time
> (for example "0") and put this value in the fact table if the dimensions
> fields have NULL values'
> Or I have to leave the NULL values?
> What is the best choice? Why?
>
Thursday, March 22, 2012
Dimension Creation Problem
I am currently developing data warehouse design documentation for our compan
y as our first step in creating a data warehouse. During the design I ran i
nto an issue:
Our users want to answer this question: "What are our Export sales?"
Now that is a tricky question and I'll explain why...Export sales is the com
bination of sales for all products with the Product Type of Export and any s
ales (for any product) that are of type Export. I am stumped on how to acco
midate this very necessary
question and many just like it. How do I design my dimensions so that I can
sum based on 2 different dimensions into one answer?
I am sorry if this is a simple question, but for some reason it has me scrat
ching my head.You can either use a view or design your dim tables to accomodate
Ray Higdon MCSE, MCDBA, CCNA
--
"Jason Fischer" <jfischer@.bi-vetmedica.com> wrote in message
news:AC8E6672-7EAB-4DCB-A9AB-38F02E2A4ACC@.microsoft.com...
> I am currently developing data warehouse design documentation for our
company as our first step in creating a data warehouse. During the design I
ran into an issue:
> Our users want to answer this question: "What are our Export sales?"
> Now that is a tricky question and I'll explain why...Export sales is the
combination of sales for all products with the Product Type of Export and
any sales (for any product) that are of type Export. I am stumped on how to
accomidate this very necessary question and many just like it. How do I
design my dimensions so that I can sum based on 2 different dimensions into
one answer?
> I am sorry if this is a simple question, but for some reason it has me
scratching my head.|||I think you need two dimension tables - salestype and product(which contains
product type field).
You also need one fact table - sales with foreign key to product and one to
salestype.
so when you want to ask question "What are the Export Sales", you can query
fact table sales where salestype =
export and product type = export.
-- Jason Fischer wrote: --
I am currently developing data warehouse design documentation for our compan
y as our first step in creating a data warehouse. During the design I ran i
nto an issue:
Our users want to answer this question: "What are our Export sales?"
Now that is a tricky question and I'll explain why...Export sales is the com
bination of sales for all products with the Product Type of Export and any s
ales (for any product) that are of type Export. I am stumped on how to acco
midate this very neces
sary question and many just like it. How do I design my dimensions so that
I can sum based on 2 different dimensions into one answer?
I am sorry if this is a simple question, but for some reason it has me scrat
ching my head.
y as our first step in creating a data warehouse. During the design I ran i
nto an issue:
Our users want to answer this question: "What are our Export sales?"
Now that is a tricky question and I'll explain why...Export sales is the com
bination of sales for all products with the Product Type of Export and any s
ales (for any product) that are of type Export. I am stumped on how to acco
midate this very necessary
question and many just like it. How do I design my dimensions so that I can
sum based on 2 different dimensions into one answer?
I am sorry if this is a simple question, but for some reason it has me scrat
ching my head.You can either use a view or design your dim tables to accomodate
Ray Higdon MCSE, MCDBA, CCNA
--
"Jason Fischer" <jfischer@.bi-vetmedica.com> wrote in message
news:AC8E6672-7EAB-4DCB-A9AB-38F02E2A4ACC@.microsoft.com...
> I am currently developing data warehouse design documentation for our
company as our first step in creating a data warehouse. During the design I
ran into an issue:
> Our users want to answer this question: "What are our Export sales?"
> Now that is a tricky question and I'll explain why...Export sales is the
combination of sales for all products with the Product Type of Export and
any sales (for any product) that are of type Export. I am stumped on how to
accomidate this very necessary question and many just like it. How do I
design my dimensions so that I can sum based on 2 different dimensions into
one answer?
> I am sorry if this is a simple question, but for some reason it has me
scratching my head.|||I think you need two dimension tables - salestype and product(which contains
product type field).
You also need one fact table - sales with foreign key to product and one to
salestype.
so when you want to ask question "What are the Export Sales", you can query
fact table sales where salestype =
export and product type = export.
-- Jason Fischer wrote: --
I am currently developing data warehouse design documentation for our compan
y as our first step in creating a data warehouse. During the design I ran i
nto an issue:
Our users want to answer this question: "What are our Export sales?"
Now that is a tricky question and I'll explain why...Export sales is the com
bination of sales for all products with the Product Type of Export and any s
ales (for any product) that are of type Export. I am stumped on how to acco
midate this very neces
sary question and many just like it. How do I design my dimensions so that
I can sum based on 2 different dimensions into one answer?
I am sorry if this is a simple question, but for some reason it has me scrat
ching my head.
Sunday, February 19, 2012
Different Data Types in Data Source View
I have 2 views in my data warehouse where the dimension table primary_key has a data type of tinyint and the fact table foreign_key has a data type of tinyint. When I bring these into SSAS 2005 data source view, the data types change. The dimension key is now a system.int32 and the fact key is now a system.byte. I can no longer relate these two tables together because I get an error of "different data types".
Has anyone encountered this yet?
Thanks,
Brian
Brian,
There is a section on this issue in the "Project REAL: Analysis Services Technical Drilldown" whitepaper by Dave Wickert. You can find the paper at the following URL:
http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx
Search for: "Data type mismatches with tinyint keys"
HTH,
- Steve
|||Very helpful, thanks so much|||I'm getting the same problem but with system.decimal keys. Everything was fine until I updated the named query for my fact table. All the numeric fields changed from system.decimal to system.byte. The data warehouse has not changed so I don't know why this has happened. I read the article and tried to recast the data type on one of the related tables to match the fact table, but the cast didn't seem to have any effect. I even tried to set the field on both tables to 1 and they still didn't match. The fact table and the dimension tables are created by named queries. Could this have something to do with SP1?|||Sherrill,
This may have to do with an issue around the data source view definition for the named queries that you modified. The data types for particular columns in your query will not always "recast" themselves in the data source view definition even though the underlying data type in either the table or named query has changed. Try commenting out the definition for the columns that were changed and then save the data source view. Go back and un-comment and then save the data source view again. This should clear out the existing type binding and create a new one that is correct for the changes you made.
HTH,
Steve
|||That didn't fix it. The changes I made really had nothing to do with the data type. All I have to do is change one thing on the named query - such as change a literal from 1016 to 9999 - and when I save the dataview, the type changes on all numeric fields from decimal to byte - blowing away all my relationship links. It's as if it isn't seeing the datatype in the underlying table. I even tried to cast a field to a specific type in the named query, and it still came out as byte.
I spoke with someone else here who also ran into the problem. He said it started after we upgraded to SP1. His solution was to make the changes in the xml code view. The data warehouse I'm using is Oracle and I'm using the .NET provider. The dataview was created before SP1 and changed several times with no problems prior to the upgrade.
Different Data Types in Data Source View
I have 2 views in my data warehouse where the dimension table primary_key has a data type of tinyint and the fact table foreign_key has a data type of tinyint. When I bring these into SSAS 2005 data source view, the data types change. The dimension key is now a system.int32 and the fact key is now a system.byte. I can no longer relate these two tables together because I get an error of "different data types".
Has anyone encountered this yet?
Thanks,
Brian
Brian,
There is a section on this issue in the "Project REAL: Analysis Services Technical Drilldown" whitepaper by Dave Wickert. You can find the paper at the following URL:
http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx
Search for: "Data type mismatches with tinyint keys"
HTH,
- Steve
|||Very helpful, thanks so much|||I'm getting the same problem but with system.decimal keys. Everything was fine until I updated the named query for my fact table. All the numeric fields changed from system.decimal to system.byte. The data warehouse has not changed so I don't know why this has happened. I read the article and tried to recast the data type on one of the related tables to match the fact table, but the cast didn't seem to have any effect. I even tried to set the field on both tables to 1 and they still didn't match. The fact table and the dimension tables are created by named queries. Could this have something to do with SP1?|||Sherrill,
This may have to do with an issue around the data source view definition for the named queries that you modified. The data types for particular columns in your query will not always "recast" themselves in the data source view definition even though the underlying data type in either the table or named query has changed. Try commenting out the definition for the columns that were changed and then save the data source view. Go back and un-comment and then save the data source view again. This should clear out the existing type binding and create a new one that is correct for the changes you made.
HTH,
Steve
|||That didn't fix it. The changes I made really had nothing to do with the data type. All I have to do is change one thing on the named query - such as change a literal from 1016 to 9999 - and when I save the dataview, the type changes on all numeric fields from decimal to byte - blowing away all my relationship links. It's as if it isn't seeing the datatype in the underlying table. I even tried to cast a field to a specific type in the named query, and it still came out as byte.
I spoke with someone else here who also ran into the problem. He said it started after we upgraded to SP1. His solution was to make the changes in the xml code view. The data warehouse I'm using is Oracle and I'm using the .NET provider. The dataview was created before SP1 and changed several times with no problems prior to the upgrade.
Subscribe to:
Posts (Atom)