Union Query With a Crosstab

building reports desktopintelligence
1

I just wanted to bounce this off you guys who may have more experience than me.

I have picked up a report with multiple data providers in it. The 2 key DPs return in excess of 100k rows and are joined on truckloads of dimensions (15-20). The report has loads of tabs as well as filters and ranks. Moving from tab to tab is a nightmare because of the recomputations.

These 2 DPs are identical in format. One returns this year’s data and the second previous year’s data. My suggestion to make this into 1 DP using a union query (with an object to identify the year) and then use crosstabs and where to compare this year’s data to last year’s.

Without having to join these 2 large data sets on all of the dimensions, I think this would cut down the re-computation time, even with the filters and the ranks (not forgetting the other smaller data providers).

Or is this wishful thinking? Anyone with any experience of doing this?

Thanks in advance. :stuck_out_tongue:

Grantie :uk: (BOB member since 2004-07-01)

2

I think it will certainly help. Let the server do the work.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

3

Absolutely. You will only have 1 cube. No linking means faster reports.

Steve Krandel :us: (BOB member since 2002-06-25)

4

Thanks guys. I’ll give it a whirl.

Grantie :uk: (BOB member since 2004-07-01)