Second opinion on how to build this cube

I am building an OLAP cube for a report using SSAS 2005 and SSRS 2005. On a warehouse database there is a single de-normalized table containing the "production" data with 30+ columns and 3 million+ rows of data. However only 4 of the columns, 10_years_age_group, calendar_year, customer_id and city, is required for this report. Also another "lookup" table containing the data for all the cities population with the columns, 10_years_age_group, calendar_year, city and population, and it is used to calculate the percentage of the "distinct customer count" over the population of that city, 10_years_age_group and calendar_year. On the report, the "calendar_year" will be the report parameter, the "city" is on the row, "10_years_age_group" is on the column and the detail is "distinct customer_id/poopulation" express as a percentage.

Mt quesrtion is:

If I create two OLAP cubes how do I link the cubes in the report designer

I am developing the cube and report using VS 2005

Thanks

Hi,

You don't need to create two different cubes, you can reference more than one fact table in a cube. The difference is that the measures from the two tables will fall into different measure groups. Although it does sounds like you only need to bring in the one table and perhaps get AS to perform the same functionality as the lookup. Anyway back to the two facts in one cube; linking up the dimensions is simple, in the cube you can share dimensions using the dimension usage tab. I would have a look at the adventure works project, in there it uses more than one fact in a cube.

Hope that helps

Matt

Thank you for your reply.

Please can you guide me on how to "get AS to perform the same functionality as the lookup" mentioned in your reply. Or do I need to perform this "lookup" functionality if I attached the "population" measure to the main "production" table cube and shared the dimensions in the "dimension usage" tab. I would think once I share the dimensions between the two measures, AS will automatically know where the "population" measure is associated with the dimensions. Thanks.

Hi,

I think the way i understood your two tables, they were identical and the lookup was a rollup/grouping of the main table. If this is correct, you can create a measure which is a distinct count of customers and depending on how you slice it will give you the same answer as the rollup. Although the population thing has confused me.

If above is correct and you slice it by the 10_year.., calendar and city you will get a distinct count of customers for that slice.

Hope that helps

Matt

i Matt:

Sorry it is my mistake that I did not clearly explained in the post.

The two tables are not directly related and there is NO PK to FK relationship. The lookup table is just the summary of the population for all the cities with the year and 10 years age grouping breakdown and this data is static until next time I update it from other source. I tried to use the population information with the year, city and age grouping, mapped to the production olap cube to calculate the percentage, "customer distinct count/population" for the same age grouping, year and city.

Thanks.