MS SQL Server OLAP - one fact table devided into two cubes

Author

one fact table devided into two cubes

Kristoffer Mortensen

2005-04-19, 7:23 am

Hi group
I'm having a problem with a huge dimension. The users of the cube are
not excited about the idea of adding levels to the dimension, but they
would rather have 2 cubes instead.

The large dimension is customers, and they wish to have the cube divided
into two cubes by departments.

I've not been able to find any info on this that gives me an idea on how
to do this.
The Filter Source Table I think should be used, but how do I restrict
the customer dimension to only show the customers related to the
specific departments in that subcube?

So I need to "cut the fact table in half" including the values in the
dimensions. Is that possible at all?
There are only one tabel in this cube, the fact table. Each row has a
customer and a department field.

I hope I made myself understandable, and someone could tell me if this
is possible, and if it is, then give a clue on how to do this ;)

Greets and thanx
/Kristoffer

verbani

2005-04-19, 9:23 am

It is possible. I'm not sure if I unstand you correctly but here goes:

When you create a cube you also have a partition. When you edit that
partition on the last screen of the wizard you get a button advanced. If you
click on that you get a box where you can add a 'where' clause to the select
analysis executes again your fact table. Here you can then choose for
departementcode = 'xxx'

I hope this helps.

Regards,
Nico

"Kristoffer Mortensen" wrote:

> Hi group
> I'm having a problem with a huge dimension. The users of the cube are
> not excited about the idea of adding levels to the dimension, but they
> would rather have 2 cubes instead.
>
> The large dimension is customers, and they wish to have the cube divided
> into two cubes by departments.
>
> I've not been able to find any info on this that gives me an idea on how
> to do this.
> The Filter Source Table I think should be used, but how do I restrict
> the customer dimension to only show the customers related to the
> specific departments in that subcube?
>
> So I need to "cut the fact table in half" including the values in the
> dimensions. Is that possible at all?
> There are only one tabel in this cube, the fact table. Each row has a
> customer and a department field.
>
> I hope I made myself understandable, and someone could tell me if this
> is possible, and if it is, then give a clue on how to do this ;)
>
> Greets and thanx
> /Kristoffer
>

David Botzenhart

2005-04-20, 3:23 am

You can do it that way as verbani suggested or you can create views on the
data warehouse that will produce the rows that you want and base the cubes
and dimensions off of the views.

David

"verbani" <verbani@discussions.microsoft.com> wrote in message
news:0C80134A-542A-44E3-8C8F- DFC02C7E5209@microso
ft.com...[color=darkred]
> It is possible. I'm not sure if I unstand you correctly but here goes:
>
> When you create a cube you also have a partition. When you edit that
> partition on the last screen of the wizard you get a button advanced. If
> you
> click on that you get a box where you can add a 'where' clause to the
> select
> analysis executes again your fact table. Here you can then choose for
> departementcode = 'xxx'
>
> I hope this helps.
>
> Regards,
> Nico
>
> "Kristoffer Mortensen" wrote:
>

Kristoffer Mortensen

2005-04-20, 3:23 am

A view is just another name for a virtual cube right? Just checking ;)

As far at I know, it is not possible to select a subselection of data
from the fact table in a virtual cube, but only say what measures, and
dimensions there has to in/excluded.

Please if I'm wrong, correct me. I would prefer virtual cubes, but i'm
trying to make verbani's suggestion.

David Botzenhart wrote:
> You can do it that way as verbani suggested or you can create views on the
> data warehouse that will produce the rows that you want and base the cubes
> and dimensions off of the views.
>
> David
>
> "verbani" <verbani@discussions.microsoft.com> wrote in message
> news:0C80134A-542A-44E3-8C8F- DFC02C7E5209@microso
ft.com...
>
>
>
>

Kristoffer Mortensen

2005-04-20, 7:23 am

I've tried this. and changed in the partition for the cube.

The data get's filtered ok, but there are still elements in the customer
dimension, that are not used.
How can I make sure that these elements are removed from the dimension?

Do I really have to make two new Fact-tables where I only copy the
departments I want? (the department and customer are in the same row, so
sorting on departments should result in fewer customers).
It will result in 3 tables, 1 containing all data, 1 containing half of
that data, and the last for the last half of the data.

Or am I simply to stupid to see how to accomplish this?
I hope it's a bit more clear on what I need...

/Kristoffer

verbani wrote:[color=darkred
]
> It is possible. I'm not sure if I unstand you correctly but here goes:
>
> When you create a cube you also have a partition. When you edit that
> partition on the last screen of the wizard you get a button advanced. If you
> click on that you get a box where you can add a 'where' clause to the select
> analysis executes again your fact table. Here you can then choose for
> departementcode = 'xxx'
>
> I hope this helps.
>
> Regards,
> Nico
>
> "Kristoffer Mortensen" wrote:
>
>

OLAPMonkey

2005-04-20, 11:23 am

If I'm understanding you issue correctly...I think your problem is that
you do not want to have 2 separate Customer dimensions...one for
Deparment A and one for Department B? Is that accurate?
Otherwise...you can achieve what you want by having 2 customer
dimensions...one for each cube. Unfortunately the SourceTable Filter
property of a dimension is not exposed for editing within a cube
editor...as such...you must have 2 separate dimensions. Then in the
dimension editor you can set the SourceTable Filter expression
appropriately for each dimension. You will also need to use the filter
expression in the 2 cubes as well.

Kristoffer Mortensen wrote:
> I've tried this. and changed in the partition for the cube.
>
> The data get's filtered ok, but there are still elements in the
customer
> dimension, that are not used.
> How can I make sure that these elements are removed from the
dimension?
>
> Do I really have to make two new Fact-tables where I only copy the
> departments I want? (the department and customer are in the same row,
so
> sorting on departments should result in fewer customers).
> It will result in 3 tables, 1 containing all data, 1 containing half
of[color=darkred]
> that data, and the last for the last half of the data.
>
> Or am I simply to stupid to see how to accomplish this?
> I hope it's a bit more clear on what I need...
>
> /Kristoffer
>
>
>
> verbani wrote:
goes:[color=darkred]

that[color=darkred]
advanced. If you[color=darkred]
the select[color=darkred
]
for[color=darkred]
are[color=darkred]
they[color=darkred]
divided[color=darkre
d]
on how[color=darkred]
restrict[color=darkr
ed]
the[color=darkred]
a[color=darkred]
this[color=darkred]

Kristoffer Mortensen

2005-04-21, 3:24 am

Right now I have one cube, with all data in it.

The customer dimension is too big to list all at a single level; I have
to group the customers by their first letter.
The users of the cube do not like this, and they would prefer to have
two cubes.

One cube for some selected departmens, and another cube for all the
other departments.

The cubes will be identical, except for what data to use from the fact
table. If I could I would prefer using the same fact-table.

I'm trying to get only the relevant data from the fact data (which I can
do), but I need to limit what data are in the dimensions as well.
Instead of all the for instance, customers, I want only those related to
the specific departments of that cube. Also all the departments are also
shown, but only the selected ones should be shown.

If I understand you right OLAPMonkey, then I should reenter the
departments in the filter for dimensions?
So I'll have the departments listet two separerated plaves, in the
dimensions (since they all show elements that's not related to the
chosen departments), and in the advanced settings for departments?

OLAPMonkey wrote:
> If I'm understanding you issue correctly...I think your problem is that
> you do not want to have 2 separate Customer dimensions...one for
> Deparment A and one for Department B? Is that accurate?
> Otherwise...you can achieve what you want by having 2 customer
> dimensions...one for each cube. Unfortunately the SourceTable Filter
> property of a dimension is not exposed for editing within a cube
> editor...as such...you must have 2 separate dimensions. Then in the
> dimension editor you can set the SourceTable Filter expression
> appropriately for each dimension. You will also need to use the filter
> expression in the 2 cubes as well.
>
> Kristoffer Mortensen wrote:
>
>
> customer
>
>
> dimension?
>
>
> so
>
>
> of
>
>
> goes:
>
>
> that
>
>
> advanced. If you
>
>
> the select
>
>
> for
>
>
> are
>
>
> they
>
>
> divided
>
>
> on how
>
>
> restrict
>
>
> the
>
>
> a
>
>
> this
>
>
>

Sponsored Links