Hi,
I have designed a Data Mart to track potato contracting and receiving. It is running well except for one scenario. Here is a brief introduction to my data mart and the problem.
For each load I receive:
1- I add a row to the fact table with all the corresponding dimension surrogate keys like date, grower, season, load, etc., and the measures which are either weights (net weight, gross weight) or quality attributes (color, gravity, etc)
2- I also add a row to a dimension called LOAD (used in the fact table above) to store 20 different attributes of the load like load number, status, Certificate Number, etc.
As you can see I add one row to the LOAD dimension table for each row I add to the FACT table. I don't like this, but I cannot figure out how I can do it otherwise, since this is the very nature of the process I am modeling.
I use MS Analysis Services cubes and Crystal Analysis 9.0 to browse them. The load dimension is divided into levels and everything is fine when I drill down. However, when I try to select all loads at the same time (all base dimension members in Crystal) to compare all received loads, Crystal Analysis hangs. The total number of records I have in both my fact table and my load dimension table is 470 000, which is "nothing" for a Data Mart, from what I have read.
This doesn't seem to be a Crystal problem because MS cube doesn't give me a good performance every time I use the load dimension either.
Any ideas how to change the design OR optimize my Analysis Services dimensions and cubes to solve my problem? Users need to compare ALL loads, so even though I include some levels in the Load dimension they will try all loads for particular analysis, using the functionality provided by Crystal Analysis to show all base members of a dimension.
Any help would be greatly appreciated. I have been stuck with this for a while now.
jc
I have designed a Data Mart to track potato contracting and receiving. It is running well except for one scenario. Here is a brief introduction to my data mart and the problem.
For each load I receive:
1- I add a row to the fact table with all the corresponding dimension surrogate keys like date, grower, season, load, etc., and the measures which are either weights (net weight, gross weight) or quality attributes (color, gravity, etc)
2- I also add a row to a dimension called LOAD (used in the fact table above) to store 20 different attributes of the load like load number, status, Certificate Number, etc.
As you can see I add one row to the LOAD dimension table for each row I add to the FACT table. I don't like this, but I cannot figure out how I can do it otherwise, since this is the very nature of the process I am modeling.
I use MS Analysis Services cubes and Crystal Analysis 9.0 to browse them. The load dimension is divided into levels and everything is fine when I drill down. However, when I try to select all loads at the same time (all base dimension members in Crystal) to compare all received loads, Crystal Analysis hangs. The total number of records I have in both my fact table and my load dimension table is 470 000, which is "nothing" for a Data Mart, from what I have read.
This doesn't seem to be a Crystal problem because MS cube doesn't give me a good performance every time I use the load dimension either.
Any ideas how to change the design OR optimize my Analysis Services dimensions and cubes to solve my problem? Users need to compare ALL loads, so even though I include some levels in the Load dimension they will try all loads for particular analysis, using the functionality provided by Crystal Analysis to show all base members of a dimension.
Any help would be greatly appreciated. I have been stuck with this for a while now.
jc