Hi all,

In a cube with a large fact table there is a Customer dimension, a Time
dimension and other dimensions (products, organization, ...). Customers have
a property called "Customer Status" which can be one of the following:
Active, Inactive, New. However, this property is changing in time, meaning
that a specific customer can be Active for a period, then Inactive and then
back Active.

In order to filter the cube data by "Customer Status", we added a column in
the fact table that specifies the "Customer State" value for each
transaction. Considering that the fact table is extremly large and also
considering that the "Customer Status" is depending only on Customers and
Time (it should not be necessary to be saved at transaction level), we want
to remove the column from the fact table and to do the filtering in a more
efficient way. Is this possible ? Maybe with a helper cube containing only
the "Customer State" information and no transactions ?

TIA,
Rud

Hi Rud,

It sounds like you could set up another "event" fact table which records
changes in Customer Status, using a common Time dimension (assuming that
it has fine enough granularity that customers can't change status
multiple times on the same time member). Then a second cube (or measure
group, in the case of AS 2005) based on this fact table could be used to
track the status of customers over time, and to identify appropriate
time dimension ranges.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks a lot for your answer.

Yes, this is what i have expected: to have another cube with "Customer
Status" changes.
You are right, the "Customers Status" is changing one time at most for each
time member.

However, i don't know how to filter the original cube data by looking in
the event cube data.
I am thinking to have the original cube and the "event" cube grouped in a
single virtual cube,
and afterwards to have some sort of formulas or something else (calculated
cells, ) in the virtual cube to filter the original cube data by the events
data.
The "Customer Status" dimension will reside only in events cube in this
case.
I'm not sure what to use and how to do this from here on. The ideal solution
will have the logic inside the virtual cube, so the Excel can
be used as OLAP client. Currently, there is no other OLAP client to use a
custom MDX. Btw, we have MSAS2000.

Thanks again for your support,
Rud

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Hi Rud,

Virtual cubes would be the typical approach with AS 2000 - in case you
need some suggestions from this newsgroup, can you give an idea of how
the event cube data should be used to filter the main cube, with
examples?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi Rud,

What you are describing is a slowly changing dimension. There are a number
of ways of handling this, usually referred to as Type I, II, or III.

Have a look on the web.

www.ralphkimball.com is a good place to start

Regards,
Wreck.


"Deepak Puri" wrote:

Thanks a lot for answering.

Let's assume we will use the virtual cube approach with 2 inner cubes (sales
and events) based on the following sample fact data:

Sales Cube (customer, product, time, sales value):
Customer1 Product1 1/3/2005 5$
Customer1 Product2 1/5/2005 7$
Customer2 Product1 1/3/2005 3$
Customer2 Product2 1/7/2005 9$
Customer2 Product3 1/9/2005 4$

Events Cube (customer, status, change date):
Customer1 Active 1/1/2005
Customer1 Inactive 1/4/2004
Customer2 Active 1/1/2005
Customer2 Inactive 1/8/2005

The expected result when filtering for Active Customers will be:
Customer1 Product1 1/3/2005 5$
Customer2 Product1 1/3/2005 3$
Customer2 Product2 1/7/2005 9$

I hope this is clear, but something else came into my mind right now: would
it be any help if in the events cube data we will fill the status for a
Customer for each existing time member (and not marking only the actual
change) ? It will be easier to do the filtering ? This can be easily
implemented in the events cube fact data.

Anyway, what will be the best aproach for the above problem ?

Thanks,
Rud


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Hi Wreck,

Thanks for answering, but:
- Type I is no good because i need to track historical changes
- Type II - probably the one to be used, however, is it possible to
implement this WITHOUT affecting the fact table ? This method implies that
some kind of Customer "versions" will be available in a separate table ( in
fact, these should be the Event cube data that Deepak mentioned earlier). In
the fact table there is already all the information needed ( Customer ID and
Time ID), which should be enough for deciding which Customer "version" to be
considered, right ?
- Type III - not usable as there can be more than 2 changes and i need
them all

Thanks,
Rud

"Wreck" <Wreck (AT) community (DOT) nospam> wrote

Hi Rud,

One approach to this scenario would be with calculated cells at the
[Day] level of date and leaf level of customer: cells with inactive
customers would be assigned as null, so those values wouldn't get
aggregated. But performance may suffer when rolling up from leaf cells.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks a lot for answering.

I will try this below solution, along with this one:
in the Evens cube i will place a measure having the value of 1 in the
entire cube and Max/Min as aggregation. Then in the virtual cube, i will
create calculated members for each measure from the data cube like this :

[Calculated Member] = [Event Cube Measure] * [Original Measure]

The [Event Cube Measure] can be either 1 or empty.

This should empty the cells where the Events cube has no data, right ?

Thanks,
Rud

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Sorry for my previous post, it is working only at the lowest level of the
customers dimension.

Can you please give me a clue of how should i do it using calculated cells
approach ?

In the meantime, I'm trying to look at the whole problem from the beggining:
i have a 100 000 000+ fact table and 10 slowly changing dimensions
representing customers properties for which we need historycal data. But by
applying the Type II approach of the slowly changing dimensions, i will end
up with a extremly large fact table not only in depth, but in width also.
So, what will be the solution ?

Thanks,
Rud


"Rud" <x@x.x> wrote