none
As of date processing RRS feed

  • Question

  • Consider the following ...

    A transaction table containing universe of all transactions life time.  I have a requirement to summarize a balance amount on monthly bases.  Moreover, the months' summaries are based on a specific as of end of months.  As of end of month reflects what all preceding monthly balances looked like as of that end of month.  For instance, an end user needs to see  what monthly balances, beginning on 01/01/2017, looked like on 7/31/2017.

    I designed a data model where, I would have date dimension (monthly), various other dimensions, and a single fact table pointing to those dimensions summarizing monthly balances.  However, I can't see how to gracefully integrate the as of date design to this model.  

    Any help with this will be greately appreciated.  Thanks in advance.

    Thursday, November 23, 2017 1:56 PM

All replies

  • Consider the following ...

    A transaction table containing universe of all transactions life time.  I have a requirement to summarize a balance amount on monthly bases.  Moreover, the months' summaries are based on a specific as of end of months.  As of end of month reflects what all preceding monthly balances looked like as of that end of month.  For instance, an end user needs to see  what monthly balances, beginning on 01/01/2017, looked like on 7/31/2017.

    I designed a data model where, I would have date dimension (monthly), various other dimensions, and a single fact table pointing to those dimensions summarizing monthly balances.  However, I can't see how to gracefully integrate the as of date design to this model.  

    Any help with this will be greately appreciated.  Thanks in advance.

    Wednesday, November 22, 2017 8:49 PM
  • Are you talking about SSAS model? Looks like Power BI is a good friend for this project

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, November 23, 2017 5:45 AM
  • You can add cumulative calculations and pre calculate it in table itself, if as of date conditions are static. In case it need to be interactive based on user input, then you can calculate it on the fly using OLAP models like SSAS tabular

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, November 23, 2017 6:42 AM
  • Apparently the fact table has at least two date foreign keys, one for balance date and the second for record date (used for 'looked like'). Your monthly balance is conceptually a sum of all transactions where record date <= 'looked liked on date'.

    Expect me to help you solve your problems, not to solve your problems for you.

    Thursday, November 23, 2017 4:04 PM
  • Hi rgelfand,

    Thanks for your question.

    According to your description, you are looking for pattern of how to handle different granularities, right?

    If so, you might want to refer to below blog:
    http://www.daxpatterns.com/handling-different-granularities/


    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, November 24, 2017 4:48 AM
    Moderator