January 23, 2014 at 8:04 pm
The table attached is the fact table. The dimension tables would be dim policy, dim accounting period, dim asl, dim actuarial pricing, dim exposure state, dim agency. In Force Premium Balance is the measure. Here is my question:
If I connect to the cube via excel:
If I drop Accounting Period (dim accounting period), Policy number (dim policy) on row labels , In Force Premium Balance (fact table) on values label, it should show as $4838.00
If I drop Accounting Period (dim accounting period), Policy number (dim policy) , actuarial Pricing code =27(dim actuarial pricing) on row labels , In Force Premium Balance(fact table) on values label, it should show as $913.00
If I drop Accounting Period (dim accounting period), Policy number (dim policy) , actuarial Pricing code =109(dim actuarial pricing) , exposure state cd =8400 (dim exposure state) on row labels , In Force Premium Balance (fact table) on values label, it should show as $155.00
So If I slice the fact table based on the different combinations of dimensions, the measure In Force Premium Balance should pick up the corresponding value from the fact table.
Any help is highly appreciated. Please let me know if I need to provide more information. Thank you so much for your time.
January 24, 2014 at 8:22 am
According to your image, if you have dimensions that correspond to the foreign keys in your fact table and you have them properly related to your relevant measure group on the Dimension Usage tab, then yes, that behaviour is what should happen.
I'm not sure if that answers your query but I couldn't really tell what your question was :ermm:
January 24, 2014 at 10:45 am
Thank you for your quick response Yayomayn.
If I select exposure_state_key=8400 from the dimension in the row label, it is adding up the in_force_premium_balance.
I have created the measure in_force_premium_balance as "sum". That's the reason it is adding up wherever exposure_sate_key=8400. For example: 4838+4838+372+372+913+913+155+155+3398
(I know this is the not the right logic)
But if I drop just exposure state in the row label in the excel it should show up just 4838 instead of adding up. Not sure how this can be achieved in the cube. I thought writing a nested case statement would help.
Thank You.
January 29, 2014 at 11:14 am
Sounds like your measure is not additive across time but additive across other dimensions? Would I be correct in making that assumption? If so, you could try changing your aggregation function from "SUM" to "LastNonEmpty" or "LastChild" and remove your nested case statement depending on what you are doing.
These aggregation functions are usually used for end of period balances or inventory counts. Aggregating along all other dimensions use the SUM function.
AverageOfChildren is usually used in calculating things like average inventory on hand at the end of a period or average daily balances at the end of a month or other period. Aggregating along all other dimensions use the SUM function.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply