Hi !

Using Analysis services 2000.

I'm creating a inventory cube. The fact table has date, itemnumber,
stocklocation and some more dimensions, and then it has quantity moved and
value moved as measures.

By 'moved' in the measure names, I mean that I do not store any inventory
status numbers in the fact table, just movements. Those numbers can then
both be positive and negative.

I have added a calculated member that calculates the Quantity on stock at
the end of any given time period. The formula for that is:
Sum(PeriodsToDate([Time].[(All)],
[Time].CurrentMember), -[Measures].[Quantity])

That formula works. Then my next problem was to add another calculated
member to calclulate the average on stock for a time interwal, and that's
where my problems start.

Lets say we just have one itemnumber and that itemnumber having 1 item on
stock at 1.jan and 99 on 31.dec and no other movements in between. The usual
average function would take 1+99 and divide by 2 to get an average stock of
50, which is not the number I'm looking for. I would like to sum the stock
at the end of each day of the year (or any interval in general) and then
dividing by number of days.

It seems that my problem exists because there are no records in the fact
table in between the jan and dec records, so maybe the solution would be to
add empty records to the fact tables, which contain zero or blank for every
field, except for the date, and thus filling in the missing dates. But, I
don't know if that will fix things and I don't like to go that way unless I
have to.

In general, I need to be able to produce a set of day-members without having
records for that particular day in the fact table.

Set Expressions like:
Count( {OpeningPeriod([Time].[Day], [Time].Currentmember) :
ClosingPeriod([Time].[Day], [Time].Currentmember) }, IncludeEmpty )

will only count the number of days having data behind then in that period.

So, how should I accomplish this?

a) By adding empty "date" records to the fact tables
b) Some other way, and then how?

Regards,
Palli

Can you clarify whether your [Time] dimension is built from a dimension
table with all days populated, because then an MDX expression like below
should count all days in 2004, regardless of whether the fact table has
data for them:

ClosingPeriod([Time].[Day], [Time].[Year].[2004]) )

- Deepak

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

Nope, NOT all days were populated, and that is the problem.

What I did was to run a SQL script on the fact table before the cube is
build, which makes sure every day of every year is populated. Where the
script finds an unpopulated day, it adds a record to the fact table with all
empty fields, except for the date field, which contains the missing date.

After that my period calculations work correctly.

Is this the correct way to go?

Is this the only way to go ?

Regards,
Palli

The recommended approach is a separate time dimension table, populated
with all relevant dates. This paper by Tom Chester discusses in detail
why:

http://www.sqljunkies.com/Article/D1...F20D60951395.s
cuk
Conclusion

To summarize the main recommendations:

Always use a dimension table for time; do not derive the dimension from
the fact table.

Do not derive the dimension from a date/time column.

Use a meaningful key for dates, e.g., YYYYMMDD. This makes it easier to
backload historical data.

Decide in advance if the All member is meaningful; remove it, if not.

- Deepak

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