I have problem which creates double counting.

I have a cube which has information regarding securities (mostly bonds)

The problem the properties of these securities can change on a day-to-day
basis.
(In the OLTP database we store have a history table and current table.).
So I entered a row in my fact table for every day we own the security, with
the properties of the security at the end of each day.

so if we own security a for 3 days there are 3 entries for that security in
the fact table

This basically allows us to query the data by an as-of-date, for example
what is the average yield on all t-bills we hold as-of march 25, 2004. The
is ussually different every business day, even if don't change the actual
securities we are holding

However if want a value with out using the as-of-date (for example - a total
count of the securities we purchased in march) i get the wrong value it
basically aggregate all the rows in the fact table that match the query.

Which is technically count the security for every day we held it.

Any ideas on how to handle this..

Thanks in advance,
HJ

There are well-known techniques for dealing with semi-additive
"snapshot" measures, which are discussed in this MSDN paper. Please note
that Analysis Services 2005 now has a built-in


http://msdn.microsoft.com/library/de.../en-us/dnsql2k
/html/semiadd2.asp

Amir Netz
Microsoft Corporation

Updated May 18, 2004

Applies to:
Microsoft SQL Server 2000
Microsoft SQL Server 2000 Analysis Services

Summary: Focusing on a classic inventory problem, this article describes
the implementation techniques of semiadditive measures in online
analytical processing.
...
http://www.microsoft.com/technet/pro...uate/dwsqlsy.m
spx
One of the greatest arguments for using an analytical server such as
Analysis Services is the ability to define complex calculations
centrally. Analysis Services has always delivered rich analytics, but
some complex concepts have been difficult to implement.

One such concept is that of a semi-additive measure. Most common
measures, such as [Sales], aggregate cleanly along all dimensions:
[Total Sales] for all time is the sales for all products, for all
customers, and for all time. A semi-additive measure, by contrast, may
be additive in some dimensions but not in others. The most common
scenario is a balance, such as the number of items in a warehouse. The
aggregate balance for yesterday plus today is not, of course, the sum of
yesterday's balance plus today's balance. Instead it's probably the
ending balance, although in some scenarios it is the beginning balance.
In Analysis Services 2000 you would have to define a complex MDX
calculation to deliver the correct measure. With Analysis Services 2005,
beginning and end balances are native aggregation types.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thank you very much

HJ

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