MS SQL Server OLAP - MDX How would This SemiAdd Situation Work

Author

MDX How would This SemiAdd Situation Work

PDI

2005-09-19, 3:24 am

Greetings All

I have the following situation.

Fact Table [f_Test]

ID DIMID TIMEKEY SALES
1 1 1 5
2 1 2 60
3 1 3 7
4 1 2 8
5 2 2 100
6 2 2 200

Time Dimension [f Test Time]

ID MONTH
1 JAN
2 FEB
3 MAR

Test Dimension [d TEST]
DIMID Division
1 DivA
2 DivB

MEASURES
[SALES]
[FIRST SALES NOT EMPTY] (using lastChildNotEmpty in SQL 2k5)
[LAST SALES NOT EMPTY] (using lastChildNotEmpty in SQL 2k5)

DESIRED RESULT:
DIV JAN FEB MAR
DIVA 5 8 7
DIVB 200

WHAT I GET IS:
DIV JAN FEB MAR
DIVA 5 68 7
DIVB 300

What is the best way to achieve this? Is it even possible with the
type of design shown?

I am using SQL 2k5 Analysis Svc. I have also read the MS article on
inventory and semi-additive measures but it has not cleared up my
confusion.

Thanks

JP

Dave Wickert [MSFT]

2005-09-19, 8:24 pm

What you are seeing is by-design in Analysis Services.
When two facts have exactly the same "grain" in the fact table, i.e. they
have exactly the same foreign keys in the fact table, then they are combined
before they are even stored. In your case, the fact table records #2 and #4
are being combined into a single value of 68 and #5 and #6 are being
combined into a single value of 300. Notice how they have the same FKs in
the fact table. You think they are different records, but when AS processes
them and stores them in the MOLAP structures they are combined. We call them
"duplicated facts" -- not a good term, but that is what we use. This is
happening before semi-additive even kicks in.
--
Dave Wickert [MSFT]
dwickert@online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"PDI" < precipice_intl_at_ho
tmail.com> wrote in message
news:9nssi191od6e71b
4flgc8psefp8jd0jt2t@
4ax.com...
>
> Greetings All
>
>
> I have the following situation.
>
> Fact Table [f_Test]
>
> ID DIMID TIMEKEY SALES
> 1 1 1 5
> 2 1 2 60
> 3 1 3 7
> 4 1 2 8
> 5 2 2 100
> 6 2 2 200
>
>
> Time Dimension [f Test Time]
>
> ID MONTH
> 1 JAN
> 2 FEB
> 3 MAR
>
> Test Dimension [d TEST]
> DIMID Division
> 1 DivA
> 2 DivB
>
> MEASURES
> [SALES]
> [FIRST SALES NOT EMPTY] (using lastChildNotEmpty in SQL 2k5)
> [LAST SALES NOT EMPTY] (using lastChildNotEmpty in SQL 2k5)
>
> DESIRED RESULT:
> DIV JAN FEB MAR
> DIVA 5 8 7
> DIVB 200
>
> WHAT I GET IS:
> DIV JAN FEB MAR
> DIVA 5 68 7
> DIVB 300
>
>
> What is the best way to achieve this? Is it even possible with the
> type of design shown?
>
> I am using SQL 2k5 Analysis Svc. I have also read the MS article on
> inventory and semi-additive measures but it has not cleared up my
> confusion.
>
> Thanks
>
> JP
>

Darren Gosbell

2005-09-20, 7:23 am

If I understand your desired result, what I think you are saying is that
record 6 actually overrides record 5 and record 4 overrides record 2. It
sounds like you want to do a "closing balance" type logic.

Given the current structure of the cube this is not going to work,
because with a standard measure AS will do the equivalent of -

SELECT DIMID, TIMEKEY, SUM(SALES)
FROM f_Test
GROUP BY DIMID, TIMEKEY

I can see 2 possible solutions off the top of my head:

1) alter the routine that loads the fact table (or create a new fact
table) that only loads the latest values.

OR//

Create a view over the existing table that only shows the most latest
values. (similar to option 1)

2) Add another dimension to the table so that the records can be
identified by date or time.

The problem at the moment is that even though the fact table has 2
records for division A in february. there is only one "cell" in the OLAP
storage that can hold this value so the 2 records get aggregated
together.

HTH

--
Regards
Darren Gosbell [MCSD]
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article < 9nssi191od6e71b4flgc
8psefp8jd0jt2t@4ax.com>, PDI
< precipice_intl_at_ho
tmail.com> says...
>
> Greetings All
>
>
> I have the following situation.
>
> Fact Table [f_Test]
>
> ID DIMID TIMEKEY SALES
> 1 1 1 5
> 2 1 2 60
> 3 1 3 7
> 4 1 2 8
> 5 2 2 100
> 6 2 2 200
>
>
> Time Dimension [f Test Time]
>
> ID MONTH
> 1 JAN
> 2 FEB
> 3 MAR
>
> Test Dimension [d TEST]
> DIMID Division
> 1 DivA
> 2 DivB
>
> MEASURES
> [SALES]
> [FIRST SALES NOT EMPTY] (using lastChildNotEmpty in SQL 2k5)
> [LAST SALES NOT EMPTY] (using lastChildNotEmpty in SQL 2k5)
>
> DESIRED RESULT:
> DIV JAN FEB MAR
> DIVA 5 8 7
> DIVB 200
>
> WHAT I GET IS:
> DIV JAN FEB MAR
> DIVA 5 68 7
> DIVB 300
>
>
> What is the best way to achieve this? Is it even possible with the
> type of design shown?
>
> I am using SQL 2k5 Analysis Svc. I have also read the MS article on
> inventory and semi-additive measures but it has not cleared up my

Sponsored Links