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

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 (AT) online (DOT) 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_hotmail.com> wrote

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_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <9nssi191od6e71b4flgc8psefp8jd0jt2t (AT) 4ax (DOT) com>, PDI
<precipice_intl_at_hotmail.com> says...