While creating a Dashboard where the data comes from both OLTP(transaction data) and OLAP (analysis data), the common issue we face is OLAP chart shows "No Data Available" error while the OLTP chart gives the data. So we need to find a solution where we can find the last available value for the OLAP data, so that the dashboard becomes meaningful. This issue has been illustrated in the following example.
Scenario:
I have two tables-
1.FactAssetAllocation: Its a OLTP table. This table records all the asset allocation transaction details. The data is captured every day (or every month depending on your system).
2. FactCommitments. Its a OLAP table. This table records the commitment amount details for each of the client. The data is captured when ever there is a new commitment is done with the client.
Now i had to create a dashboard which has two charts:
1. Is an asset allocation bar-chart.
2. Is a table which show the commitment details for a given client.
The dashboard filter is Date and Client.
Problem:
When i select a Client (example Alan) and a Date (example 2011.11.09) You find there is Asset Allocation data for the given client and date but there is no commitment details.
This is because the Asset Allocation has data for Client - Alan, Date - 2011.01.09, but there is no data for Client -Alan on the same date. the commitment for Alan was done on 2011-04-12. So this data has to be shown in the commitment status chart.
So now we need to get Last Non Empty (Last Available) Commitment value for the given Asset_Allocation[Date_key].
If i had to write a SQL query it will be:
SELECT Commitment_Amount
FROM DW.Fact_Commitment
INNER JOIN DW.Fact_AssetAllocation A ON A.Client_Key = C.Client_Key
WHERE Date_key in
(
SELECT MAX(C.Date_Key)
FROM DW.Fact_Commitment C
INNER JOIN DW.Fact_AssetAllocation A ON A.Client_Key = C.Client_Key
)
If the same query needs to be written in DAX:
CALCULATE(
MAX(Commitments[Commitment])
,FILTER(
all(Commitments)
,Commitments[Client_Key] = MAX(AUM[Client_Key])
)
,FILTER(
ALL(Commitments)
,Commitments[Date_Key] =CALCULATE(
MAX(Commitments[Date_Key])
,FILTER(
ALL(Commitments)
,Commitments[Date_Key] <= MAX(AUM[Date_Key])
)
,FILTER(
all(Commitments)
,Commitments[Client_Key] = MAX(AUM[Client_Key])
)
)
)
)
The
Blue section highlighted gives the Last Available Commitment Date for which we need to find the commitment value.
More Links: http://sqlblog.com/blogs/alberto_ferrari/archive/2011/03/08/thinking-in-dax-counting-products-in-the-current-status-with-powerpivot.aspx
