SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSAS Nested Case statment


SSAS Nested Case statment

Author
Message
rajenkan
rajenkan
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 64
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.
Attachments
Untitled.png (18 views, 74.00 KB)
PB_BI
PB_BI
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1964 Visits: 2517
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




I'm on LinkedIn
rajenkan
rajenkan
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 64
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.
sneumersky
sneumersky
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2486 Visits: 487
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search