September 24, 2010 at 12:46 am
Hi,
i have been asked to build a datawarehouse cube which includes data from three product master tables. So i have to bring together each product that exists in all 3 product master tables into one table using SSIS and include that table into my cube . but the no. of columns in each product master table are different (like 7 cols, 8 cols, 9 cols). If i bring together data of each of these columns (max = 9) then remaining cols will be null which is not allowed in cube processing.
So are there any alternative solution to this scenario ??
Regards,
Amar
BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
September 24, 2010 at 1:02 am
amarsale (9/24/2010)
Hi,i have been asked to build a datawarehouse cube which includes data from three product master tables. So i have to bring together each product that exists in all 3 product master tables into one table
Use SQL Joins on Product key
Raunak J
September 24, 2010 at 1:17 am
Hi,
There are different products in different product master(pM) tables so if join is used on PK the query will return zero rows.
The Grand product master table will have maximum columns of all the 3 product master tables(i.e., 9). so the remaining columns will remain empty as one pM table contains 7 cols (2 cols will remain NULL)
and other conatins 8 cols (1 col will remain NULL).
Null value is not allowed in SSAS cube.
Regards,
Amar Sale
BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
September 24, 2010 at 9:04 am
Can you use a default value to show it is an non-existing record? Like 'N/a' or a "-1' key?
September 24, 2010 at 12:24 pm
I am pretty sure that you are allowed nulls in dimensions, in fact tables it is generally a bad idea. There are several null processing configuration options available in SSAS.
You could also put in dummy values such as N/A if you want/need those columns populated.
September 25, 2010 at 5:11 am
amarsale (9/24/2010)
Hi,i have been asked to build a datawarehouse cube which includes data from three product master tables. So i have to bring together each product that exists in all 3 product master tables into one table using SSIS and include that table into my cube . but the no. of columns in each product master table are different (like 7 cols, 8 cols, 9 cols). If i bring together data of each of these columns (max = 9) then remaining cols will be null which is not allowed in cube processing.
So are there any alternative solution to this scenario ??
Regards,
Amar
Amar,
I was reading through your post and I have a question
Why do you need 3 master tables for an single entity which in this context is (Products)? The master table is not any general table?
Ideally there should be One dimensional table and Many(or one) relational tables
Raunak J
September 25, 2010 at 4:28 pm
You certainly can have NULL values in a dimension. You just have to adjust the NULL processing settings on the dimension. Check under the Error configuration of the dimension
September 25, 2010 at 10:16 pm
Raunak Jhawar (9/25/2010)
amarsale (9/24/2010)
Hi,i have been asked to build a datawarehouse cube which includes data from three product master tables. So i have to bring together each product that exists in all 3 product master tables into one table using SSIS and include that table into my cube . but the no. of columns in each product master table are different (like 7 cols, 8 cols, 9 cols). If i bring together data of each of these columns (max = 9) then remaining cols will be null which is not allowed in cube processing.
So are there any alternative solution to this scenario ??
Regards,
Amar
Amar,
I was reading through your post and I have a question
Why do you need 3 master tables for an single entity which in this context is (Products)? The master table is not any general table?
Ideally there should be One dimensional table and Many(or one) relational tables
different data exists at 3 different sources and according to their requirement they have their own product master which has only the specific data that they require. So the no. of columns differ.
BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
September 27, 2010 at 11:55 am
I am pretty sure that you are allowed nulls in dimensions, in fact tables it is generally a bad idea.
Although perhaps technically allowed, it would be better to subsitute values such as UNKNOWN or N/A in dimensions to better utilize the drill down capability, or even just to enhance the properties if 2005+. The careful use of Nulls in a dimension can be useful for creating a ragged dimension, however.
This combining of three otherwise disparate tables is exactly what data warehouses are good for. It gets the entire company on the same page.
In fact tables, however, I use NULLS frequently in columns. Jobs with no first contact, for example, will have a Null. Without it, the calculation of the average first contact time would be problematic.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply