Hi,
I need to create a cube with 3 dimensions: Product, Time and Customer.
This cube contains some measures that cannot be rolled up with the basic features of Transformer (sum, average, etc).
I have to use external rollup, but it is my first time with this function and I'd like to be sure of something.
As far as the product and customer dimensions are concerned, the 'sum' rollup is the one to use but the rollup regarding the time will have to be calculated outside the cube.
Is it possible to tell transformer to use a normal rollup for the product and customer dimension?
I created an iqd that gives the lowest level category code of the products and of the customers for each level of the dimension time. As I have Year, Quarter, Month and Day, I have four times the couples products – customers in the query (this means 4 query with 'union all' between them). When I configured the model, I set the external rollup column only in the time dimension. I assumed that transformer would simply use a sum rollup in the 2 other dimension.
The resulting cube is ok if you look the data from the all products all customers point of view but every other level of these 2 dimensions contains 0.
Did I try to achieve something impossible in Transformer or did I do it wrong?
Another option would be to calculate every couples for every level but the resulting query would be quite long and somewhat impossible to create, I have 5 levels in product and customer, this means I would have to create more or less 4x5x5=100 select statements to populate all the possibilities.
Anyone knows an easier way?
I need to create a cube with 3 dimensions: Product, Time and Customer.
This cube contains some measures that cannot be rolled up with the basic features of Transformer (sum, average, etc).
I have to use external rollup, but it is my first time with this function and I'd like to be sure of something.
As far as the product and customer dimensions are concerned, the 'sum' rollup is the one to use but the rollup regarding the time will have to be calculated outside the cube.
Is it possible to tell transformer to use a normal rollup for the product and customer dimension?
I created an iqd that gives the lowest level category code of the products and of the customers for each level of the dimension time. As I have Year, Quarter, Month and Day, I have four times the couples products – customers in the query (this means 4 query with 'union all' between them). When I configured the model, I set the external rollup column only in the time dimension. I assumed that transformer would simply use a sum rollup in the 2 other dimension.
The resulting cube is ok if you look the data from the all products all customers point of view but every other level of these 2 dimensions contains 0.
Did I try to achieve something impossible in Transformer or did I do it wrong?
Another option would be to calculate every couples for every level but the resulting query would be quite long and somewhat impossible to create, I have 5 levels in product and customer, this means I would have to create more or less 4x5x5=100 select statements to populate all the possibilities.
Anyone knows an easier way?