none
Creating Cube RRS feed

  • Question

  • I have a fact table that is of the form  key1, key2, key3, postingYYYYMM, serviceYYYYMM, amount where key1, key2, key3, postingYYYYMM, serviceYYYYMM is unique.

    I am looking to summarize based on unique key.  But before I do so, I need to filter based on postingYYYYMM, serviceYYYYMM.

    of the form

    select a.Key1, postingYYYYMM asofdate, serviceYYYYMM period, sum(amount) amount
    from #aging a
    where postingYYYYMM <= 201712 and serviceYYYYMM >= 201706
    group by a.Key1, serviceYYYYMM

    As you can see the filtering is done before the summary and I am using 201712 year date key as litteral.  I wanted it to be a variable.  How would the cube need to be setup? Also, if there was another query similar to this one unioned to this one, how would I setup a cube then.

    Friday, December 29, 2017 4:53 AM

Answers

  • Hi Rgelfand,

    Thanks for your question.

    >>>As you can see the filtering is done before the summary and I am using 201712 year date key as litteral. 
    In this scenario, you can create view in the underlying database or create named query in the data source view of your SSAS projects based on the above SQL query. Then you can create cube based on the view or named query .

    >>>I wanted it to be a variable.  How would the cube need to be setup?

    I do not think you can create a cube that based on a variable.

    >>>Also, if there was another query similar to this one unioned to this one, how would I setup a cube then.

    You just need union these two fact tables to one fact table. It is exactly the same union in T-SQL query.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, December 29, 2017 8:04 AM
    Moderator