SSAS Get measure value based on another table start end

Posted on 2011-10-19
Medium Priority
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
  • 4

Expert Comment

ID: 36991656
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

ID: 36991814

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

LVL 10

Author Comment

ID: 36992608
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

itcouple earned 0 total points
ID: 37000437
The solution I will try is to aggregate "lower level" fact table into higher level fact table (invoice)
LVL 10

Author Closing Comment

ID: 37023064
this is what I will try

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

809 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