Read a thread where a Tom Chester sugested, mainly for performance reasons,
using pre-populated time dim.

If yo do this, is there a way to show only the times up to a specific time
- such as "now"? e.g. On October 31 I don't want November and December to
show in the time dim yet.

Also, is there a function in MDX to retrieve the current system time. I
tried Now and GetDate but ...

Les McPhee

When you prepopulate a time dimension, the only way you will see a future
date with a query is if your fact table has a value that links to a future
date.

--
Ray Higdon MCSE, MCDBA, CCNA
---
"Les McPhee" <lmcphee69 (AT) hotmail (DOT) com> wrote

This has not been my experience. This is true with cub files, but not with
cubes. The only way to ensure that future times do not show up is to run a
SQL query to delete unused time dimensions. Note: If you are using the
non-empty function unsed ones will not show up but they are still in the
cube. This is important if you are using closingperiod to determine a YTD
to date total. An example I have is an inventory cube. I always want the
measure closing inventory to display my current inventory if I have picked
the current year or period. If the time dimension is fully populated and I
pick the current year, closing period will choose december and my closing
measure will be 0. If I remove unused dimension members from time then my
closing period will be November and closing will reflect its current value.

I do this by storing a temp time table off to the side. This table contains
all of the dates I will ever need for time. Each night during the data load
I truncate my time table, copy my temp table over, and delete the unused
members. This seems to work fine.

Dave McChristie


"Ray Higdon" <rayhigdon (AT) higdonconsulting (DOT) com> wrote

Les McPhee wrote:

Time dimension table in the relational database that selected all
records from the time dimension where the time key was in a list of
distinct time keys from the fact table. I used that view to feed the
time dimension in AS. This might be a burden on the RDBMS, but you could
find a way around it with some ETL ore something. The point is that this
way your time dimension is completely data driven.

you can use syntax like:

VBA!Now()

This will return the current date and time like a string. If you want
the date like yyyymmdd because your days in the time dimension have
names like that, use:

VBA!Format(VBA!Now(), "yyyymmdd")

in combination with StrToMember() and Ancestor() you can find the
current week, month etc.

Search for this syntax in google groups and you'll find all you need.

Cheers,

Coen

I am using a SQL view on the date table that only returns dates upto
yesterday. In SQL Server, the filter for a 12-month + current month
window (upto yesterday) looks like:

DateAdd(mm,-12,
DateAdd(dd,1-DatePart(dd,dbo.PrevDay(getdate())),
dbo.PrevDay(getdate())))
and dbo.PrevDay(getdate())

- Deepak

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