Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSAS Nested Case statment Expand / Collapse
Author
Message
Posted Thursday, January 23, 2014 8:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 04, 2014 7:38 AM
Points: 2, Visits: 62
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.


  Post Attachments 
Untitled.png (11 views, 74.90 KB)
Post #1534319
Posted Friday, January 24, 2014 8:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 3:45 AM
Points: 390, Visits: 700
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





I'm on LinkedIn
Post #1534515
Posted Friday, January 24, 2014 10:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 04, 2014 7:38 AM
Points: 2, Visits: 62
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.
Post #1534589
Posted Wednesday, January 29, 2014 11:14 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:39 PM
Points: 2,141, Visits: 486
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.


Post #1536053
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse