newbie: How to specify cell values for non-leaf dimension members in the data source? 
Author Message
 newbie: How to specify cell values for non-leaf dimension members in the data source?

Hello All!

I'm a newbie to data warehouse modelling, so my question might seem a little
silly :)
Normally, whan building a cube Analysis Services get the values from the
data source for the cells corresponding to the last hierarchy level members
and then uses aggregation functions to calculate the values for non-leaf
members.
Now my question is, can I override this behavior and explicitly specify in
the data source values corresponding to some of the non-leaf dimension
members?
Here is the problem I'm planning to solve this way
We need to analyse sales facts and compare them to sales plan values
The problem is that sales facts are specified with granurality per day, and
sales plan values are specified per month
In the data source I would have the following facts:

Row, TypeOf Fact, Store, Product, Time, Value

1, SalesPlan, Levi's, Jeans,  2003 April,  $2000
2, ActualSales, Levi's, Jeans, 2003 April 2,   $100
3, ActualSales, Levi's, Jeans, 2003 April 3,   $120
4, ActualSales, Levi's, Jeans, 2003 April 4,   $130
  ....

So the value for the tuple (Time.2003.April, Levi's, Jeans, SalesPlan)
should be explicitly taken from the data source, and the value for the tuple
(Time.2003.April, Levi's Jeans, ActualSales) should be aggregated from
per-day values
How do I achieve this in Analysis Services? Or is there a beeter solution?

Good luck!
Andrew Surinov



Tue, 27 Sep 2005 15:09:22 GMT
 newbie: How to specify cell values for non-leaf dimension members in the data source?

Hi Andrew,
the issue of how to enter planning values into analysis
services has not really been answered yet. It would have
to do with write-back functionality and this is only
writing in a separate table of the database, not in the
cube itself.

If you have data on different levels it is a good idea to
create two separate cubes -  one for real data and one
for planning data. Then, you can always add leaf data
values, since you can then decide where the leaf is. You
can then bring the two cubes together in a virtual cube.
Of course you still will not have data on the planning
side below the entered data.

You could also think of splitting the planning data into
a daily level (even or following a distribution plan).
Then you would have data on the same level and could
enter it in one single cube.

I've heard that Analysis Services will have the planning
part implemented much better with Yukon than it has now.

HTH Lutz



Tue, 27 Sep 2005 21:59:59 GMT
 newbie: How to specify cell values for non-leaf dimension members in the data source?
It is very simple in cases like this.

Move your plan values into a separate cube, and in that cube disable the day
level in the cube editor. (This is an option in the advanced properties tab when
you select the day level in the cube editor.) Now this cube will join to facts
at the month level, not the day level. Move your monthly plan sales into a table
or a view that uses month-level keys for the data. Now, build a virtual cube
that combines the ActualSales by day with the SalesPlan by month.

HTH

Quote:

> Hello All!

> I'm a newbie to data warehouse modelling, so my question might seem a little
> silly :)
> Normally, whan building a cube Analysis Services get the values from the
> data source for the cells corresponding to the last hierarchy level members
> and then uses aggregation functions to calculate the values for non-leaf
> members.
> Now my question is, can I override this behavior and explicitly specify in
> the data source values corresponding to some of the non-leaf dimension
> members?
> Here is the problem I'm planning to solve this way
> We need to analyse sales facts and compare them to sales plan values
> The problem is that sales facts are specified with granurality per day, and
> sales plan values are specified per month
> In the data source I would have the following facts:

> Row, TypeOf Fact, Store, Product, Time, Value

> 1, SalesPlan, Levi's, Jeans,  2003 April,  $2000
> 2, ActualSales, Levi's, Jeans, 2003 April 2,   $100
> 3, ActualSales, Levi's, Jeans, 2003 April 3,   $120
> 4, ActualSales, Levi's, Jeans, 2003 April 4,   $130
>   ....

> So the value for the tuple (Time.2003.April, Levi's, Jeans, SalesPlan)
> should be explicitly taken from the data source, and the value for the tuple
> (Time.2003.April, Levi's Jeans, ActualSales) should be aggregated from
> per-day values
> How do I achieve this in Analysis Services? Or is there a beeter solution?

> Good luck!
> Andrew Surinov

--
George Spofford
Microsoft MVP
Chief Architect / OLAP Solution Provider
DSS Lab
http://www.dsslab.com

ISVs & IT organizations: Find out how DSS Lab can speed your development!


Wed, 28 Sep 2005 01:25:53 GMT
 newbie: How to specify cell values for non-leaf dimension members in the data source?
Hello, George!

Please explain what are 'month-level keys'
Do you mean that my time dimension table should look like this:

ID, Year, Month, Day
1, 2003, April, NULL  - month level key
2, 2003, April, 1
3, 2003, April, 2
4, 2003, April, 3
...
32, 2003, May, NULL
33, 2003, May, 1
34, 2003, May, 2
...
and that SalesPlan facts should reference records 1 and 32 ?

 GS> Move your plan values into a separate cube, and in that cube disable
 GS> the day level in the cube editor. (This is an option in the advanced
 GS> properties tab when you select the day level in the cube editor.) Now
 GS> this cube will join to facts at the month level, not the day level.
 GS> Move your monthly plan sales into a table or a view that uses
 GS> month-level keys for the data. Now, build a virtual cube that combines
 GS> the ActualSales by day with the SalesPlan by month.

 GS> HTH


 ??>> Hello All!
 ??>>
 ??>> I'm a newbie to data warehouse modelling, so my question might seem a
 ??>> little silly :) Normally, whan building a cube Analysis Services get
 ??>> the values from the data source for the cells corresponding to the
 ??>> last hierarchy level members and then uses aggregation functions to
 ??>> calculate the values for non-leaf members. Now my question is, can I
 ??>> override this behavior and explicitly specify in the data source
 ??>> values corresponding to some of the non-leaf dimension members? Here
 ??>> is the problem I'm planning to solve this way We need to analyse sales
 ??>> facts and compare them to sales plan values The problem is that sales
 ??>> facts are specified with granurality per day, and sales plan values
 ??>> are specified per month In the data source I would have the following
 ??>> facts: Row, TypeOf Fact, Store, Product, Time, Value 1, SalesPlan,
 ??>> Levi's, Jeans,  2003 April,  $2000 2, ActualSales, Levi's, Jeans, 2003
 ??>> April 2,   $100 3, ActualSales, Levi's, Jeans, 2003 April 3,   $120 4,
 ??>> ActualSales, Levi's, Jeans, 2003 April 4,   $130  .... So the value
 ??>> for the tuple (Time.2003.April, Levi's, Jeans, SalesPlan) should be
 ??>> explicitly taken from the data source, and the value for the
 ??>> tuple (Time.2003.April, Levi's Jeans, ActualSales) should be
 ??>> aggregated from per-day values How do I achieve this in Analysis
 ??>> Services? Or is there a beeter solution? Good luck! Andrew Surinov




Fri, 30 Sep 2005 18:30:15 GMT
 newbie: How to specify cell values for non-leaf dimension members in the data source?
Hi Andrew

One easy solution to this is to change the month level data to day level
data - for example by allways entering the month data for the first day of
the month. e.g:

Facts in Cube with Plan:

2003-01-01
2003-02-01
2003-02-01
2004-03-01

Facts in cube with sales
2003-01-23
2003-02-15
2003-02-06
2004-03-21

In this way you can allways use day-level keys.

A small design trick:

Let the time key in your dimension table be:

10000 * year + 100 * month + day

So your time dimension looks like:

ID, year, month, day
20000101, 2000, 01,01
20000102, 2000, 01,02
20000103, 2000, 01,03

If you browse your fact table directly its is much easier to search ranges
of data.

Sincerely
Thomas Kejser
MCDBA


Quote:
> Hello, George!

> Please explain what are 'month-level keys'
> Do you mean that my time dimension table should look like this:

> ID, Year, Month, Day
> 1, 2003, April, NULL  - month level key
> 2, 2003, April, 1
> 3, 2003, April, 2
> 4, 2003, April, 3
> ...
> 32, 2003, May, NULL
> 33, 2003, May, 1
> 34, 2003, May, 2
> ...
> and that SalesPlan facts should reference records 1 and 32 ?

>  GS> Move your plan values into a separate cube, and in that cube disable
>  GS> the day level in the cube editor. (This is an option in the advanced
>  GS> properties tab when you select the day level in the cube editor.) Now
>  GS> this cube will join to facts at the month level, not the day level.
>  GS> Move your monthly plan sales into a table or a view that uses
>  GS> month-level keys for the data. Now, build a virtual cube that
combines
>  GS> the ActualSales by day with the SalesPlan by month.

>  GS> HTH


>  ??>> Hello All!
>  ??>>
>  ??>> I'm a newbie to data warehouse modelling, so my question might seem
a
>  ??>> little silly :) Normally, whan building a cube Analysis Services get
>  ??>> the values from the data source for the cells corresponding to the
>  ??>> last hierarchy level members and then uses aggregation functions to
>  ??>> calculate the values for non-leaf members. Now my question is, can I
>  ??>> override this behavior and explicitly specify in the data source
>  ??>> values corresponding to some of the non-leaf dimension members? Here
>  ??>> is the problem I'm planning to solve this way We need to analyse
sales
>  ??>> facts and compare them to sales plan values The problem is that
sales
>  ??>> facts are specified with granurality per day, and sales plan values
>  ??>> are specified per month In the data source I would have the
following
>  ??>> facts: Row, TypeOf Fact, Store, Product, Time, Value 1, SalesPlan,
>  ??>> Levi's, Jeans,  2003 April,  $2000 2, ActualSales, Levi's, Jeans,
2003
>  ??>> April 2,   $100 3, ActualSales, Levi's, Jeans, 2003 April 3,   $120
4,
>  ??>> ActualSales, Levi's, Jeans, 2003 April 4,   $130  .... So the value
>  ??>> for the tuple (Time.2003.April, Levi's, Jeans, SalesPlan) should be
>  ??>> explicitly taken from the data source, and the value for the
>  ??>> tuple (Time.2003.April, Levi's Jeans, ActualSales) should be
>  ??>> aggregated from per-day values How do I achieve this in Analysis
>  ??>> Services? Or is there a beeter solution? Good luck! Andrew Surinov





Fri, 30 Sep 2005 19:33:41 GMT
 newbie: How to specify cell values for non-leaf dimension members in the data source?
Hello, Thomas!

This solution works well with Month-Day hierarchy - ok, I can associate
monthly plan values with the first or with the last day of the month.
However, I also have to deal with the Brand-Model hierarchy , where plan
values are associated with Brand and sales values are associated with
Models. How to deal with this?

 TK> One easy solution to this is to change the month level data to day
 TK> level data - for example by allways entering the month data for the
 TK> first day of the month. e.g:

 TK> Facts in Cube with Plan:

 TK> 2003-01-01
 TK> 2003-02-01
 TK> 2003-02-01
 TK> 2004-03-01

 TK> Facts in cube with sales
 TK> 2003-01-23
 TK> 2003-02-15
 TK> 2003-02-06
 TK> 2004-03-21

 TK> In this way you can allways use day-level keys.

 TK> A small design trick:

 TK> Let the time key in your dimension table be:

 TK> 10000 * year + 100 * month + day

 TK> So your time dimension looks like:

 TK> ID, year, month, day
 TK> 20000101, 2000, 01,01
 TK> 20000102, 2000, 01,02
 TK> 20000103, 2000, 01,03

 TK> If you browse your fact table directly its is much easier to search
 TK> ranges of data.

 TK> Sincerely
 TK> Thomas Kejser
 TK> MCDBA



 ??>> Hello, George!
 ??>>
 ??>> Please explain what are 'month-level keys'
 ??>> Do you mean that my time dimension table should look like this:
 ??>>
 ??>> ID, Year, Month, Day
 ??>> 1, 2003, April, NULL  - month level key
 ??>> 2, 2003, April, 1
 ??>> 3, 2003, April, 2
 ??>> 4, 2003, April, 3
 ??>> ...
 ??>> 32, 2003, May, NULL
 ??>> 33, 2003, May, 1
 ??>> 34, 2003, May, 2
 ??>> ...
 ??>> and that SalesPlan facts should reference records 1 and 32 ?
 ??>>
 GS>>> Move your plan values into a separate cube, and in that cube disable
 GS>>> the day level in the cube editor. (This is an option in the advanced
 GS>>> properties tab when you select the day level in the cube editor.) Now
 GS>>> this cube will join to facts at the month level, not the day level.
 GS>>> Move your monthly plan sales into a table or a view that uses
 GS>>> month-level keys for the data. Now, build a virtual cube that
 TK> combines
 GS>>> the ActualSales by day with the SalesPlan by month.
 ??>>
 GS>>> HTH
 ??>>

 ??>>




Fri, 30 Sep 2005 20:17:01 GMT
 newbie: How to specify cell values for non-leaf dimension members in the data source?
Andrew,

No, I don't mean that. I mean:

Day_ID  Month_ID Year Month Day
1            1             2003    April  1
2            1             2003    April  2
3            1             2003    April  3
35          2             2003    May   5

and salesplan facts reference the Month_ID column. If you use unique keys for
each month as well as each day, albeit denormalized in the table, and you tell
AS2K that the month level uses unique keys, then you won't have any join
problems.

This is a good discipline to use for many dimensions, not just the time
dimension.

You should review star schema design (for example, from Ralph Kimball's Data
Warehouse Lifecycle Toolkit) for table structures that work well with AS2K.

HTH

Quote:

> Hello, George!

> Please explain what are 'month-level keys'
> Do you mean that my time dimension table should look like this:

> ID, Year, Month, Day
> 1, 2003, April, NULL  - month level key
> 2, 2003, April, 1
> 3, 2003, April, 2
> 4, 2003, April, 3
> ...
> 32, 2003, May, NULL
> 33, 2003, May, 1
> 34, 2003, May, 2
> ...
> and that SalesPlan facts should reference records 1 and 32 ?

>  GS> Move your plan values into a separate cube, and in that cube disable
>  GS> the day level in the cube editor. (This is an option in the advanced
>  GS> properties tab when you select the day level in the cube editor.) Now
>  GS> this cube will join to facts at the month level, not the day level.
>  GS> Move your monthly plan sales into a table or a view that uses
>  GS> month-level keys for the data. Now, build a virtual cube that combines
>  GS> the ActualSales by day with the SalesPlan by month.

>  GS> HTH


>  ??>> Hello All!
>  ??>>
>  ??>> I'm a newbie to data warehouse modelling, so my question might seem a
>  ??>> little silly :) Normally, whan building a cube Analysis Services get
>  ??>> the values from the data source for the cells corresponding to the
>  ??>> last hierarchy level members and then uses aggregation functions to
>  ??>> calculate the values for non-leaf members. Now my question is, can I
>  ??>> override this behavior and explicitly specify in the data source
>  ??>> values corresponding to some of the non-leaf dimension members? Here
>  ??>> is the problem I'm planning to solve this way We need to analyse sales
>  ??>> facts and compare them to sales plan values The problem is that sales
>  ??>> facts are specified with granurality per day, and sales plan values
>  ??>> are specified per month In the data source I would have the following
>  ??>> facts: Row, TypeOf Fact, Store, Product, Time, Value 1, SalesPlan,
>  ??>> Levi's, Jeans,  2003 April,  $2000 2, ActualSales, Levi's, Jeans, 2003
>  ??>> April 2,   $100 3, ActualSales, Levi's, Jeans, 2003 April 3,   $120 4,
>  ??>> ActualSales, Levi's, Jeans, 2003 April 4,   $130  .... So the value
>  ??>> for the tuple (Time.2003.April, Levi's, Jeans, SalesPlan) should be
>  ??>> explicitly taken from the data source, and the value for the
>  ??>> tuple (Time.2003.April, Levi's Jeans, ActualSales) should be
>  ??>> aggregated from per-day values How do I achieve this in Analysis
>  ??>> Services? Or is there a beeter solution? Good luck! Andrew Surinov



--
George Spofford
Microsoft MVP
Chief Architect / OLAP Solution Provider
DSS Lab
http://www.dsslab.com

ISVs & IT organizations: Find out how DSS Lab can speed your development!


Sat, 01 Oct 2005 01:38:04 GMT
 newbie: How to specify cell values for non-leaf dimension members in the data source?

If I understand your question correctly you are looking for something like:

Fact table for sales:

Time, Brand, Plan Value
2001-01-01, Green car, 1250$

Fact table for plan:

Time, Model, Sales Value
2001-01-01, Cars, 489$

Model/Sales dimension hierarchy
- Cars
    - Green cars
    - Red cars
    - etc...

But you want to compare sales and plan on the model/sales dimension, which
is no possible if models are not aggregated to the same level.

One possible solution to your problem is add a new brand member for each
model to the model sales dimension, making it look like:

- Cars
    - Green cars
    - Red cars
    - Cars         <-- new bottom level member
    - etc...

(let the member have the same name as the level above it)

Now change your fact load to do the following in the plan cube (dont change
the sales cube)

Time, Model/Brand, Sales Value
2001-01-01, (Dimension Key of the "cars" member at the lowest level), 489$

If I have explained myself correctly - you should see how this approach
makes you able to compare the sales and the plan cubes on the model/branch
dimension (since the bottom (Brand) level "cars" member will sum up nicely
to the model level)

If you enable the "hide member if same name as parent" on the lowest level
of the brand/model dimension you will even make the dimension display nicely
in clients (you are actually creating a ragged hierarchy)

HTH

Sincerely
Thomas Kejser
MCDBA.


Quote:
> Hello, Thomas!

> This solution works well with Month-Day hierarchy - ok, I can associate
> monthly plan values with the first or with the last day of the month.
> However, I also have to deal with the Brand-Model hierarchy , where plan
> values are associated with Brand and sales values are associated with
> Models. How to deal with this?

>  TK> One easy solution to this is to change the month level data to day
>  TK> level data - for example by allways entering the month data for the
>  TK> first day of the month. e.g:

>  TK> Facts in Cube with Plan:

>  TK> 2003-01-01
>  TK> 2003-02-01
>  TK> 2003-02-01
>  TK> 2004-03-01

>  TK> Facts in cube with sales
>  TK> 2003-01-23
>  TK> 2003-02-15
>  TK> 2003-02-06
>  TK> 2004-03-21

>  TK> In this way you can allways use day-level keys.

>  TK> A small design trick:

>  TK> Let the time key in your dimension table be:

>  TK> 10000 * year + 100 * month + day

>  TK> So your time dimension looks like:

>  TK> ID, year, month, day
>  TK> 20000101, 2000, 01,01
>  TK> 20000102, 2000, 01,02
>  TK> 20000103, 2000, 01,03

>  TK> If you browse your fact table directly its is much easier to search
>  TK> ranges of data.

>  TK> Sincerely
>  TK> Thomas Kejser
>  TK> MCDBA



>  ??>> Hello, George!
>  ??>>
>  ??>> Please explain what are 'month-level keys'
>  ??>> Do you mean that my time dimension table should look like this:
>  ??>>
>  ??>> ID, Year, Month, Day
>  ??>> 1, 2003, April, NULL  - month level key
>  ??>> 2, 2003, April, 1
>  ??>> 3, 2003, April, 2
>  ??>> 4, 2003, April, 3
>  ??>> ...
>  ??>> 32, 2003, May, NULL
>  ??>> 33, 2003, May, 1
>  ??>> 34, 2003, May, 2
>  ??>> ...
>  ??>> and that SalesPlan facts should reference records 1 and 32 ?
>  ??>>
>  GS>>> Move your plan values into a separate cube, and in that cube
disable
>  GS>>> the day level in the cube editor. (This is an option in the
advanced
>  GS>>> properties tab when you select the day level in the cube editor.)
Now
>  GS>>> this cube will join to facts at the month level, not the day level.
>  GS>>> Move your monthly plan sales into a table or a view that uses
>  GS>>> month-level keys for the data. Now, build a virtual cube that
>  TK> combines
>  GS>>> the ActualSales by day with the SalesPlan by month.
>  ??>>
>  GS>>> HTH
>  ??>>

>  ??>>





Sat, 01 Oct 2005 01:49:12 GMT
 newbie: How to specify cell values for non-leaf dimension members in the data source?
Hello, George!
You wrote  on Mon, 14 Apr 2003 13:38:04 -0400:

 GS> Day_ID  Month_ID Year Month Day
 GS> 1            1             2003    April  1
 GS> 2            1             2003    April  2
 GS> 3            1             2003    April  3
 GS> 35          2             2003    May   5

 GS> and salesplan facts reference the Month_ID column. If you use
unique
 GS> keys for each month as well as each day, albeit denormalized in the
 GS> table, and you tell AS2K that the month level uses unique keys,
then
 GS> you won't have any join problems.

This problems is also interesting for me and I've tried your solution.
Maybe I did something wrong but during join operations I've got
incorrect result - the values in this situation was added as many as
records with this unique month_id (in your sample for April 3 times).

Please, explain how to correct this problem.

thanks

-- -
Andrey Savchenko



Sat, 01 Oct 2005 19:27:40 GMT
 newbie: How to specify cell values for non-leaf dimension members in the data source?

I think that the answer you need is embedded in one of the other
responses and you are getting it overcomplicated. If you consider what
the sales plan means in a business sense, it could be considered as the
sales to be achieved by the last day of the month so you could replace
the APRIL by APRIL 30 and when aggregating April you will get the sum of
the sales but one line for the plan. Keep the plan and actuals in
separate columns in the fact so for the actuals rows the plan column is
zero and for the plan rows the actual column is zero. We have exactly
the same problem when data is submitted included on a yearly basis by
setting the date to DEC 31.
Perhaps a simpler alternative, if you do not need to drill down in the
cube to the individual days for the actuals, is to create a fact table
that aggregates the figures for each month. Then you will have a much
smaller fact table with the month but not day of the month.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sat, 01 Oct 2005 21:31:02 GMT
 newbie: How to specify cell values for non-leaf dimension members in the data source?
Declare the keys for the month level as Unique in the dimension editor, and
then optimize the join in the cube editor by selecting Tools / Optimize
Joins.

HTH

Quote:

> Hello, George!
> You wrote  on Mon, 14 Apr 2003 13:38:04 -0400:

>  GS> Day_ID  Month_ID Year Month Day
>  GS> 1            1             2003    April  1
>  GS> 2            1             2003    April  2
>  GS> 3            1             2003    April  3
>  GS> 35          2             2003    May   5

>  GS> and salesplan facts reference the Month_ID column. If you use
> unique
>  GS> keys for each month as well as each day, albeit denormalized in the
>  GS> table, and you tell AS2K that the month level uses unique keys,
> then
>  GS> you won't have any join problems.

> This problems is also interesting for me and I've tried your solution.
> Maybe I did something wrong but during join operations I've got
> incorrect result - the values in this situation was added as many as
> records with this unique month_id (in your sample for April 3 times).

> Please, explain how to correct this problem.

> thanks

> -- -
> Andrey Savchenko

--
George Spofford
Microsoft MVP
Chief Architect / OLAP Solution Provider
DSS Lab
http://www.dsslab.com

ISVs & IT organizations: Find out how DSS Lab can speed your development!


Sat, 01 Oct 2005 22:21:25 GMT
 
 [ 11 post ] 

 Relevant Pages 

1. Strange cell values with hidden non-leaf data members

2. Using non-leaf member data to override aggregated values

3. Finding non leaf members with data

4. custom rollup formulas for non leaf data members

5. Possibly dumb question - fact data in non leaf hierarchy members

6. parent-child dimensions with non-leaf data

7. AS2K: Custom Roll-up for non-leaf data across multiple dimensions

8. AS2K: Custom Roll-up for non-leaf data across multiple dimensions

9. ragged dimension with the missing members on the leaf level of the tree

10. ragged dimension with the missing members on the leaf level of the tree

11. Leaf members of a parent-child dimension?

12. Filtering with virtual dimension at non-leaf level


 
Powered by phpBB® Forum Software