Hi There,

I am using SSAS (SQL Server 2005 Analysis Services).

For performance reasons I'd like to build a cube having a different
grain to that of the fact table without creating a data mart at an
aggregated level in the data warehouse. The preferred Storage method
will be MOLAP. I am limited by hardware and have a workable solution
using Analysis Services 2000 on the available hardware.

For the sake of simplicity, I'll only include one dimension, the
Product dimension, to illustrate the case. It can be assumed that I
have a time dimension.

The fact table has the following columns (# indicates key):

#Date
#SKU
Sales

The Product Dimension table has the following columns:
#SKU
STYLE
CATEGORY
DEPARTMENT
SEASON


The Product Dimension has the following Hierarchy:

DEPARTMENT-->CATEGORY-->STYLE-->SKU

It also has the following attribute: SEASON.

I would like to build a cube with the following grain:

DATE
CATEGORY
SEASON
Sales

I have achieved this in various ways, but all the measure groups are
either too big and perform slowly during query time, or requires me to
put both CATEGORY AND SEASON into the fact table causing processing to
run too long, as it has to either be put into a Mart, or designed using
views.

A cube of this grain could quite easily be built in Analysis Services
2000. :-(

I would appreciate any answer that could point me in the right
direction. If you need any more info, please let me know.

Regards,

Faizel Mohidin

"too big" and "perform slowly"

what do you expect and what do you have?
how many rows in the fact table?
what is the hardware?
have you used the usage based optimization wizard?


<fmohidin (AT) truworths (DOT) co.za> wrote

I don't actually want to go into hardware discussions, as I can get the
performance I want if I built the cube at an aggregated level only,
preventing access to lower level data. All I really want to know is
whether I can change the granularity of the measure group to a level
that is not a key in the fact table, but an attribute of the product
dimension table.

I successfully built the cube/measure group with the correct query
performance by 'adding' SEASON and CATEGORY to the fact table via a
view. The measure group can then granulate on them.

By introducing the joins in the views though, the cube processing takes
too long, as the largest fact table has about 400million rows
(partitioned).

Regards,

Faizel

first option:
you can simply disallow the access to the lowest level of your dimension.

second option:
if you want only the category level:
instead of aggregating data in a view, have you try to just return the
categoryid?
select DIM.categoryid, FACT.* from Fact inner join dim on dim.product =
fact.product
no group by clause.

this query should return data quickly.

Jerome.


<fmohidin (AT) truworths (DOT) co.za> wrote

Thanks Jerome,

I've tried both methods.
Your first mehod works, but the partitions are too big.
The second method works reasonably well if I was only including product
category. As I've stated the test case above is limited to make the
understanding easier. I also need SEASON for example as well as other
attributes. This still works OK until I add another dimension.

Tuning the views on 30 fact tables with five years of data in each of
them could become a full time job.:-)

The other concern I had, was that I had to change my UDM to match the
design. Putting dimension attributes back into the fact table in the
UDM kind of defeats the purpose of the UDM for me.

Regards,

Faizel

can we get the data element names into ISO-11179 format and get actual
DDL?

CREATE TABLE SalesFacts
(sales_date DATE NOT NULL,
sku CHAR(13) NOT NULL, --upc?
sales_qty INTEGER NOT NULL,
PRIMARY KEY (sales_date, sku));

CREATE TABLE ProductDim
(sku CHAR(13) NOT NULL PRIMARY KEY,
style_code INTEGER NOT NULL,
product_cat INTEGER NOT NULL,
dept_nbr INTEGER NOT NULL);

Let's make a season into its own temporal range dimension

CREATE TABLE SeasonDim
(season_name CHAR (10) NOT NULL
start_date DATE NOT NULL,
end_date DATE NOT NULL);

Now the join to the fact table is

SalesFacts.sales_date BETWEEN SeasonDim.start_date
AND SeasonDim.end_date

and you do not have to carry seasonal data in the other tables.

Thanks CELKO,

Your DDL assumptions accurately reflects my test case.

Creating a season dimension as you have stated does not work, as the
Season I'm referring to is not purely time-based. Whe have the
time-based season as part of our Time Dimension.
The season I'm referring to is a characteristic of the product. i.e.
the season that the Product belongs to. We are a fashion business and
each Product gets a Season Descriptor, which doesn't change for the
life of the Product.

SKU (Stock Keeping Unit) is a standard term used in most retail
businesses. It is the Primary Key for the product used in all source
systems. UPC is not the same thing in our case.

Regards,

Faizel

Does anybody have any other ideas?

Regards,
Faizel