KoryS


I have several dimensions that contain useful information to be used in calculations. For example, if I had a product dimension that included Cost, but my fact table was at a transaction level, I would still like to compute "Average Cost" at various levels within the cube. Cost per transaction would be the total cost of all products (or a subset of selected products) within a specific time frame, which would allow me to total the # of transactions (from the fact table) divided by the total costs of all products within the same selection (by totaling the cost value contained at the product dimension level).

This seems like a very common scenario, but seems to elude me how to model and create the calculations. I can't, for example, store the product cost in the fact table because it is at a lower level of granularity and would overstate the cost. The obvious place to have the cost value would be within the product dimension, but in what form Member property I have tried this approach, but get inconsistent or bad results when I try to filter along other dimensions, or multiselect many products.

Example:

Code Snippet
CREATE
MEMBER CURRENTCUBE.[MEASURES].[Product Cost] AS Val(Product.CurrentMember.Properties("Product Cost")))

This "seems" to get me partways, by displaying the cost along the product dimension, but doesn't behave like a regular measure in that it doesn't automatically aggregate along other dimensions.

Am I missing something obvious here

Thanks

-Kory




Re: Design advice needed

PedroCGD


Dear Friend,

Be carefuly in the design of your project. I need to know your project requirements, but for example, I'm agree that the product is a Dimension but without the cost. The cost of the product will be the same for all the time never be changed

And what hapen if you change the product cost this month The report for this month is OK but the reports for past months will be based in the cost that you updated today So, you must consider this cost as FACT relating to the Product dimension with the attributes for example (Name and Description).

In your example, on thing you must see is to create named calculations inside the datasource view in spite of calculated member inside cube.

I hope I helped you!!

regards!!







Re: Design advice needed

KoryS

Thanks for the reply. My example was simplistic, but if the product cost changed, and we wanted to keep history of changes, the dimension table would be a type II slowly-changing dimension. So a new record with a new surrogate key would be inserted in the dimension table with the new product cost.

By putting the product cost in the fact table, even as a named calculation in the DSV, you would be duplicating the cost across many fact records, and any calculation using this fact amount would be overstated.

An alternative would be to create a fact table at the product level of granularity, but then I can see ending up with many, many fact tables out of what appear to be dimensions.

I'm still not convinced there is a good way to handle this- I just haven't found it yet.

-Kory






Re: Design advice needed

PedroCGD

Korys,

Check my post on my blog and tell me waht you think.

http://pedrocgd.blogspot.com/2007/07/ssas-slowly-changing-values.html

Are you more convinced

regards!






Re: Design advice needed

Thomas Ivarsson

Hello KoryS. I would recommend you to have the product cost as a measure in the fact table.

This means that you would have to solve this problem in the ETL-process with Integration Services.

It is a lot more complicated to solve this problem with MDX in a cube by using a product cost on each product in the dimension table.

If your leaf level in the fact table is the transaction level that should not be a hard ETL-problem to solve.

HTH

Thomas Ivarsson





Re: Design advice needed

PedroCGD

I'm agree wit you Thomas.

I did not understood very well what's the problem, because I'm very busy, but i'm with you!

regards to both!!!