Solution for Drill-Across (Multi Pass SQL)
14 December, 2015
Hi there, I've been researching YF for our organization and reviewing the myriad tutorials you have on Youtube. There's one DW/BI concept that I haven't come across from your videos or online manuals and was curious to see if you have a direct solution or a "YF" way of dealing with the concept. Specifically, I was looking to see if you have a solution for drill-across queries. I've read through your drill-down/drill-anywhere/drill-through queries extensively but drill-across doesn't seem to be captured.
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
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
Hi 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
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 Dustin...I was hoping to figure out how to replicate "Drill-Across" but have some ideas on how to just do this with Co-Display or Drill-Through. Not quite the same thing, but might work out.
Thanks for the reply!
Craig
Thanks for the reply!
Craig
