SSAS Get measure value based on another table start end

Posted on 2011-10-19
Last Modified: 2016-02-14
I need help in finding the best solution for my task.
I will have invoice information with fields like:
 InvoiceID, MeterID, Amount, PeriodStart, PeriodEnd
 1, 1, 30, 2011-01-01, 2011-01-03
 2, 1, 45, 2011-01-04, 2011-01-07
I will also have existing measure group with "raw data" and calculation with fields like
 MeterID, Date, Amount
 1, 2011-01-01, 10
 1, 2011-01-02, 10
 1, 2011-01-03, 10
 1, 2011-01-04, 10
 1, 2011-01-05, 10
 1, 2011-01-06, 10
 1, 2011-01-07, 10
My task is to compare invoice information with data from the existing fact table to get the following output:
InvoiceId, MeterID, InvoiceAmount, RawDataAmount, Different
What is the best way to achieve that?
Many thanks in advance
Question by:itcouple
    LVL 8

    Expert Comment

    Try this one :

    Select Invoice.InvoiceID, Invoice.MeterID, Invoice.Amount,SUM(RawData.Amount) as 'RawDataAmount', Invoice.Amount-SUM(RawData.Amount)  as Difference FROM Invoice LEFT JOIN RawData ON Invoice.MeterID=RawData.MeterID WHERE RawData.Date between Invoice.PeriodStart and Invoice.PeriodEnd GROUP BY Invoice.InvoiceID, Invoice.MeterID, Invoice.Amount
    LVL 10

    Author Comment


    I should have been more explicit. This needs to be done in SSAS.

    LVL 10

    Author Comment

    As there are no suggestion yet. I will post two approaches I have in mind.

    1) Calculated Member. See below something that seems to work but the problem is performance. It is fine for 40 members but 100 is very slow so that might be a problem.

    with member measures.[AmountForInvoice] as
                linkmember([meter].[Supply Start Date].currentmember, [Date].[Date]):
                linkmember([meter].[Supply End Date].currentmember, [Date].[Date])
    select {measures.[AmountForInvoice],[Measures].[Amount]} on 0,
    non empty {
    *[meter].[Supply Start Date].[Supply Start Date]
    *[meter].[Supply End Date].[Supply End Date]
     on 1
    from Mycube

    2) Build table which will split invoice time period into individual rows (one row = one day) and then somehow build relationships between my fact table and new table and make it work correctly..... I think that might work but as I haven't done anything like that before I'm not sure.

    Any thoughts will be appreciated
    LVL 10

    Accepted Solution

    The solution I will try is to aggregate "lower level" fact table into higher level fact table (invoice)
    LVL 10

    Author Closing Comment

    this is what I will try

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

         When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now