I have been asked to move an Excel sheet into SQL Server and create a
cube based on this data to present in the same tool we use to present
all of our other analytical data. The Excel source data is only 27k
records, and 50 columns. In Analysis Services, I have converted this
to 40 dimensions and 1 measure.

In Excel, if we create a PivotTable out of this data, it takes less
than 2 seconds to build, and the response time for moving dimensions,
filtering, sorting, or anything else is equally quick.

While designing storage in Analysis Services, I selected a performance
gain of 20%. After 2 hours or churning, the process had gotten to 6%
and had been on 6% for an hour and a half. I stopped the aggregations,
and processed the cube.

Next I tried viewing the cube through our online tool (which basically
uses OWC to display a PivotTable of the data), every function is slow:
The cube returns slowly, changing around dimensions happens slowly, and
expanding dimensions happens incredibly slowly.

On this same machine are 10 other cubes based on tables with millions
of records which all process rather quickly...this is the only one
causing problems.

Any ideas on how I can get this cube to work as fast as it does in
Excel?

SQL Server 2000
AS 2000
8 gigs RAM (4 dedicated to SQL Server)


Thanks in advance.

40 dimensions really sticks out. That's quite a few dimensions. Does your
fact table have 1 measure and 40 foreign keys into the dimensions?

Can you share your dimensional data mode with us?

"Chris Kiklas" <ckiklas (AT) gmail (DOT) com> wrote

Hi Tim,

Actually, all we did was upload the data into one table in SQL Server,
and created a cube based on that one table since that was how the data
was modeled to begin with. Almost every field in the table ends up
being a dimension, with very few having multiple levels.

If I create a cube through Excel, connected to the same SQL Server
table and creating the same dimensions, it takes about 5 seconds to
process, so I am still not sure where the problem is coming into play.

Oh, while designing the cube storage, we picked MOLAP, but tried all
three just in case that was the problem...still no joy.

Thanks,


If we link Excel directly
Tim Dot NoSpam wrote:

Chris,

OLAP cubes should really be built on top of dimensional databases. A
dimensional database would divide your data into Dimension tables (tables of
"things" you'd like to see/filter your measures "by") and Fact tables
(tables that store the measures you want to see). What you end up with is
essentially 1 or more tables that have attributes and a key (dimension) and
one or more fact tables that have foreign keys to its dimension tables and
semi-additive attributes such as sales total, sales quantity, etc etc.

What I would suggest you do is analyze the Excel spreadsheet and break your
data out into Dimension tables (things like Organization, Person, Time,
etc). Then break out the measures into a Fact table. I'm assuming you can
use one fact table since as you mentioned earlier, there's only one measure.

50 dimensions is a little excessive though. Group your attributes by
"thing". As an example, you wouldn't have a City, State and ZipCode
dimension, you'd have a Demographics dimension containing the attributes
mentioned.

If you go through the exercise and you still have more than 6 or 7
dimensions, you might want to go through it again. I don't know the
contents of your spreadsheet, but if you can share that without violating
any NDAs, I might be able to help you further.

-Tim

"Chris Kiklas" <ckiklas (AT) gmail (DOT) com> wrote