We are a retailer and our primary sales cube is defined at the order line level. I have the need to integrate some much lower grained numbers (month) from the accounting system into our reporting environment.
The new data is at the grain of month, brand, and GL code.
In development I've successfully integrated the brand and GL code into the cube, so that the relevant data only appears when the user is viewing data by brand or GL code.
However, the same design technique (which I will describe below) is not working for the time dimension (i.e., the relevant measure appears empty at all levels of time, even though I've linked the GL fact table to the time dimension in the same way that that brands are linked to products.
Here is how I've set up the cube:
Brands are part of the products dimension which has a hierarchy which rolls up from sku to brand to parent company of the brand. (This is not it's only hierarchy...the others are related to product categories).
I link the gl fact table to brands via the dimension attributes tab of the cube, mapping the brand key of the gl fact table to the products dimensions via the brand attribute of the product dimension.
This works wonderfully.
However, the analogous operation on the two different time dimensions (order date and shipping date), mapping the month key of the gl fact table to the month attribute of the time dimensions doesn't work, ion the sense that I get empty values in the cube.
FWIW, the time dimension is defined as a time dimension, while the products dimension is a regular dimension.
I don't see any other significant difference, other than that the all of the attributes of the products dimension are part of hierarchies, while there are quite a few time attributes which are not. I don't see whey that would matter.
So, what am I doing wrong?
Thanks, --sw
