Drill Across Fact Tables with Report Builder

I have recently built my first data warehouse with SQL Server 2008 Enterprise Edition using Kimball methodology. I have created a report model for it with Microsoft's BIDS and deployed to Report Manager so that my users can do ad-hoc reporting with ReportBuilder 1.0. I have used my report model to generate some very simple reports, but have run into an issue that may be related to my design. I am unable to generate "drill-across" reports properly with ReportBuilder when I want to group on more than one conformed dimension. By "drill-across" I mean that I want to obtain measures from 2 different fact tables by using conformed dimensions.

Here is an example: I have 2 Fact Tables. FactPledges includes (among other measures) a PledgeAmount field. FactProjectFinancials includes a BudgetAmount field. Both use conformed dimensions of FiscalYear (DimYr) and Project (DimProject). I can easily build a report that shows me Project Name (from DimProject), Fiscal Year (from DimYr) and the aggregated Pledge Amounts. I can also build a similar report that shows Project Name, Fiscal Year and the Budget Amounts. However, I am unable to create an accurate report that shows the 2 measures (PledgeAmount and BudgetAmount) aggregated properly by Project Name and Fiscal Year.

This is the area where I believe there is a problem....When creating this report with ReportBuilder using my ReportModel, I drag the PledgeAmount from FactPledges, then from the associated DimProject and DimYr, I drag the ProjectName and FiscalYear fields respectively. To get the BudgetAmount field for the report, I then have to go back to either the DimProject -OR-DimYr entity to get to the associated FactProjectFinancials entity. When I use the FactPledges>DimProject>FactProjectFinancials path, the BudgetAmount on the end report is aggregated (as you would expect) only by Project. And, when I use the FactPledges>DimYr>FactProjectFinancials path, the BudgetAmount is only aggregated by Fiscal Year. I do not know how to indicate to ReportBuilder that I want the BudgetAmount aggregated by both ProjectName and FiscalYear.

This is some very basic functionality, so I am guessing that I may have an issue with my DW Design, the Report Model that I created or in how I am using ReportBuilder. Can anyone provide some guidance on where I should be focusing my efforts?

Have you thought about creating a fact view consolidating two different facts into single one based on dimension conformance using TSQL. I guess FactProjectFinancials can be used as the driving set, and I believe ReportBuilder should have no problem reporting against views.

Thanks for the reply.
Yes, I had considered and modeled that scenario. And...you are correct that would allow me to aggregate both measures properly.
However, (you knew that was coming)...Report Builder 1.0 can automatically generate click-thru reports for the aggregated measures so the user can drill-down into the detail measures.
Since the FactProjectFinancials table grain is by Project and FiscalYear it is already at the lowest level of detail I can drill-to. But, the FactPledges table grain is by Project, Fiscal Year and Account. By placing the PledgeAmount with the BudgetAmount in FactProjectFinancials, I would need to rollup the PledgeAmount. By doing this I am losing that drill-down level of detail to the Account level which I want to provide via the click-thru report. Hope that makes sense. This seems like such a basic thing that I am trying to do. Any more ideas?

Not sure if GROUP BY ... WITH ROLLUP in your view would help as the function seems to preserve the aggregates at different levels.

Thanks hang.

That may be possible, but ReportBuilder is generating the T-SQL for the report. I was trying to determine if I needed to change my DW design, a Report Model property, or possibly the way I was building the layout with ReportBuilder. I am able to easily generate a SQL statement that accomplishes what I want (see below), so I thiink the design is OK. It's just that I cannot get ReportBuilder using my report model to generate this.

SELECT ProjectName, Yr, SUM(NoSuppAssessedBud) as NoSuppAssessedBud, SUM(TotalPldgAmt) as TotalPldgAmt
FROM
(SELECT DP.ProjectName, DY.Yr, SUM(FPF.NoSuppAssessedBud) as NoSuppAssessedBud, 0 as TotalPldgAmt
FROM FactProjectFinancials as FPF
INNER JOIN DimProject as DP ON FPF.ProjectKey=DP.ProjectKey
INNER JOIN DimYr as DY ON FPF.FiscYrKey=DY.YrKey GROUP BY DP.ProjectName, DY.Yr
UNION ALL
SELECT DP.ProjectName, DY.Yr, 0 as NoSuppAssessedBud, SUM(FGP.TotalPldgAmt) as TotalPldgAmt
FROM FactFiscYrGivingPledges as FGP
INNER JOIN DimProject as DP ON FGP.ProjectKey=DP.ProjectKey
INNER JOIN DimYr as DY ON FGP.FiscYrKey=DY.YrKey GROUP BY DP.ProjectName, DY.Yr) as A
GROUP BY ProjectName, Yr
order by ProjectName, yr

I thought ReportBuilder is designed for end-users who don’t bother or are not able to use TSQL to prepare datasets. I know it is able to generate TSQL, and maybe MDX as well. But just like other automation tools, this kind of dummy user tools will never deliver sophisticated reporting solutions. In my view, any significant query logic should be wrapped in a view or stored procedure at the backend instead of letting SSRS take over. I believe backend implementation is much more efficient, transparent and maintainable.

I also tend to adopt powerful SQL features to write more efficient and readable SQL scripts. Using WITH ROLLUP option is a typical example of such a mindset.

Just Like your SQL, to build the report you will need to build 2 queries and then join the result set.

If you just drag the second set of columns into the original query, I doubt you will get the correct result due to the join/grouping issues.

I'm not sure if ReportBuilder supports multiple queries for a single report .... but I know this is how to solve the issue in other tools.

If the tool doesn't support it, or the approach is too complicated for your end users, you may need to looks at a database level solutions (aggregate table, view etc) in order to combine the two facts tables at a common grain.

Why not create an Analysis Services cube that does the aggregations for you, then allow Report Builder to use the cube for reporting?

The problem (based on your query) is that looks like Report Builder is not able to manage what (at least myself, based on experience in BO) I call the "context".

Basically, and roughly (it is not an accurate definition), we can say there is a context for each fact table. What this means? The context defines what dimensions (roles) are accesible through that fact table. In a general definition, it defines the group of tables and relationships between them for querying purposes.

With tools like Business Objects you can define your contexts in the semantic layer (the universe). When creating a report based on an universe with contexts, if you set the appropriate properties, the type of report you are trying to build would result from 2 different queries (one query per context, per fact table). The same happens with Cognos: namespaces (similar to contexts) and "stitched queries" (the way Cognos uses to link 2 queries from 2 namespaces).

I am really not familiar with MS Report Builder, but there should be some kind of mechanism for implementing the above (or at least something similar). Drill across fact tables is a very common in dimensional modelling. If not, we can always implement a manual join using 2 different queries within Report builder (I guess this can be done).

Hope this helps

apermag