Cross Tab performance

Hi,

I've recently created a cross tab in my report and the performance is really bad. I initially created a basic table to display the data set (raw records) which returned the data in under a minute. I then created a data cube based on this data set and a corresponding cross tab to display, and it either hangs or returns records (if i filter the data) much much slower.

Any idea why the raw data is able to be retrieved quickly yet the data cube/cross tab takes orders of magnitued longer..or not at all?

Thanks in advance,
Dan

Find more posts tagged with

Comments

Virgil Dodson

Hi Dan,

How many rows of data are you using... and number of dimensions/measures? There will be some extra processing obviously since some grouping and aggregation needs to take place but I have not experienced noticeable differences in time.

dtrue

Hi,

Only one measure but a grouping on date (by month) and then about 7 data fields. Number of rows is probably 20k.

Example would look like this

sales rep first name, last name, territory, city, state, zip, et sum(sales qty by month)

Virgil Dodson

So how many sales reps would be grouped down the rows and then how many different periods across the top does this data cover.

In my simple crosstab example, it takes about 30 seconds to generate the report with 20k worth of data. My example is pretty simple with 5 sales reps and 112 periods over 9 years.