Solution for Drill-Across (Multi Pass SQL)
14 December, 2015
Following basic principals of the Kimball DW methods, the idea of joining fact tables (presumably in YF, this would be done at the view level), is strictly prohibited as joining 2 facts may violate the grain. The methods I've seen from the Kimball Group's website to deal with drill-across is to write an outer query with derived tables joined to aggregate the measures from the different facts then bring them together with the conformed dimensions
EX (From Kimballgroup.com)
[code]
SELECT Act.Customer, Act.Year, Act.Month, Actual_Amount, Forecast_Amount
FROM
� Subquery �Act� returns Actuals
(SELECT Customer_Name AS Customer, Year, Month_Name AS Month,
SUM(Sale_Amount) Actual_Amount
FROM Sales_Facts A
INNER JOIN Customer C
ON A.Customer_Key = C.Customer_Key
INNER JOIN Date D
ON A.Sales_Date_Key = D.Date_Key
GROUP BY Customer_Name, Year, Month_Name) Act
INNER JOIN
� Subquery �Fcst� returns Forecast
(SELECT Customer_Name AS Customer, Year, Month_Name AS Month,
SUM(Forecast_Amount) Forecast_Amount
FROM Forecast_Facts F
INNER JOIN Customer C
ON F.Customer_Key = C.Customer_Key
INNER JOIN Date D
ON F.Sales_Date_Key = D.Date_Key
GROUP BY Customer_Name, Year, Month_Name) Fcst
� Join condition for our small result sets
ON Act.Customer = Fcst.Customer
AND Act.Year = Fcst.Year
AND Act.Month = Fcst.Month
[/code]
The point of all this is to see if there's a way for YF to issue this statement at runtime. Specifically, if I were to build a view in the form of a star schema, I wouldn't actually be able to run this query because the star schema would be defined at the grain of the fact...so bringing two facts together in a view doesn't seem possible with your system. Is there any method using compositve views, sub queries or some other tool you have to reference two separate facts (from 2 star schemas/views) and pull them together?
Hopefully this is making sense.
Much appreciated!
Craig
Apologies for the delayed response. To be upfront, joining two star schemas together isn't strictly supported, but you should be able to accomplish what you are after through the use of virtual tables which can be built in the view.
Also, although this doesn't help in the short term, star schema functionality is expected to expand quite a bit down the road which should be helpful.
Anyway, hopefully this information has been helpful. If you have any follow up questions please feel free to ask.
Kind Regards,
Dustin
Thanks for the reply!
Craig