Post reply

SQL DWH to SSAS Cube

  • November 19, 2014 at 12:15 pm

    #292577

    I have a requirement to create a cube in SSAS. AS IS process is querying from SQL DWH using dimension table & fact tables, Fact table has around 50 Million recs and taking long time to display in Dashboard.

    Sample query from DWH

    Select dim1.a1, dim2,a2,dim3.a3,dim4.a4,dim5.a5,

    sum(case when dim1.a9=10 then f1.measure1*-1 else 0 end) V1,

    sum(case when dim2.a4=10 then f1.measure1*2 else 0 end) V2,

    sum(case when dim3.a7=10 then f1.measure1 else 0 end) V3,

    from fact f1 join dim1 join dim2 ...

    Group by Dim1, Dim2..

    Result would look something like this

    Dim1.a1Dim2.a2Dim3.a3Dim4.a4Dim5.a5V1V2V3

    -----------------------------------------------------------------------------------

    product1account1location1date1offer10100-200

    product2account2location2date2offer210000

    product3account3location3date3offer3200-1000

    product4account4location4date4offer40300-400

    product5account5location5date5offer510200

    product6account6location6date6offer60020

    product7account7location7date7offer73000

    Now my requirement is to create a cube based on these 5 Dimensions and fact table, which I did and processed it. Now to display the results in SSAS cube browser for the similar requirement as above, I have to create calculation in Cube(Calculated Members). I have write these based on the conditions how to do this in Calculations in SAS Cube.

    Please help me in the approach / creating calculation(Calculated Members) in SSAS Cube based on some conditions. Any help is highly appreciated... need to meet to some deadlines on this. Please let me know for any additional info..

    Thanks

    Vasu

  • November 19, 2014 at 12:59 pm

    #1759622

    Sounds like what you're asking is beyond what this forum was intended for.

    I think you need to find a good book on SSAS and start reading.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • November 20, 2014 at 5:13 am

    #1759785

    I agree with Alvin - you should do some research on MDX and then write your query (assuming you haven't got a problem with your cube).

    As a "shortcut", download Olap Pivot Table Extensions from here: http://olappivottableextend.codeplex.com/

    Then browse to your cube using excel, create the required layout by dragging/dropping in measures/dimensions and then right click and view the MDX in the extensions menu. You can use this MDX as a base for creating your query.

    Please, please learn it properly though. There is a stairway on SQL Server Central you can use.

    I'm on LinkedIn

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply