Our company recently went through a merger. As a result, our new corporate
fiscal calendar is different than the old pre-merger calendar and my fact
table now has rows that should be associated with either the new or old
calendar - not both!

We tried implementing this several different ways and we consistantly have
the one of the same two problems:
1) We end up with rows that are associated with either the new calendar
dimension or the old calendar dimension and thus are mutually exclusive to
one another and our cubes have no data.
2) The solution to #1 is to populate the two calendar dimensions with dates
for all time. When we do this, our cubes have data, but it's not accurate
(from a metadata standpoint)
example:
.....................time dim.........................
pre-merger FY2005 Grand_Total
measure 4,000,000 1,000,000 5,000,000

From a program standpoint, the correct value for Grand_Total is 5,000,000,
but from a metadata standpoint it should be 1,000,000 because the calendar
was active in FY2005 only, not for pre-merger dates.

Does anyone have any ideas as to this can be done correctly? The best
option we have come up with so far is to create two time dimensions and two
sets of cubes, one set using the old dimension, one set with the new. It
will work, but it's cumbersome.

Thanks much!

A couple of ideas to evaluate:

- The 2 cubes solution that you mentioned could be wrapped in a virtual
cube, with both time dimensions included. Assuming that the usage
pattern for the time dimensions is that only one will have a non-default
member selected, all the visible measures of the virtual cube can be
calculated to return a measure from the appropriate underlying cube,
depending on user selections on time dimensions. Or a navigation
dimension can be added to the virtual cube, with members like:
(All/PreMerger/PostMerger). The cube's calculated measures would then
select the appropriate underlying cube, based on this navigation
dimension.

- If the 2 time dimensions have compatible levels and structures (say
Year/Month/Day), then create a consolidated time dimension, with
PreMerger and PostMerger as the 2 top-level members. Each of these
members will have its own Year/Month/Day hierarchy under it, and any
fact table row is located under only one of these 2 trees. A variation
of this would be to have a single time dimension (Year/Month/Day), but a
separate Pre/PostMerger selection dimension. This would require a
consolidated time dimension table, with a consistent date key that spans
both periods, but whose other field values (Year/Month/Day) reflect the
period (Pre or Post) that each date entry falls into.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

I have a similar problem...In my case the user selects multiple dates (there
is only 1 time dimension) and the data has to be displayed against them for
comparison. I have no idea how to handle multiple select in MDX. The result
come sout something like this.

Year 1 Year 2 Year 3
x -- -- --
y -- -- --
z -- -- --

Can anyone help me in this?

In article <c3a575a115ed41c8a36adcacc0656bbe (AT) ureader (DOT) com>,
shilpy_anu (AT) yahoo (DOT) com says...
You use curly brackets {} to denote a set of members.

So using your above example, then MDX query would be.

SELECT
{[Time].[Year 1],[Time].[Year 2],[Time].[Year 3]} ON COLUMNS,
{x,y,z} on ROWS
FROM cube

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

Thanks Darren,

There is something else also.. one of my query is taking around 6 min to
execute without which the rest of the query runs in 1.5 min. The part which
slows the query is :

Member [Measures].[M] As
'Count(NonEmptyCrossJoin({[A].[App]},
{[ID].[All ID].Children}
))'

Here the ID is on Rows like:

M
Year ID 0
1 0
2000 2 1
3 0

I want to show the count of all App records for that particular ID. A simple
cross join does not give me the required data. Is there any other way to
write this query?

We probably have to start thinking a bit laterally.

* If there is a Many-to-one relationship between ID and App you might be
able to extend the App dimension to make ID a level under App and then
do a count of children.

* Could you create a cube with just the minimum dimensions you need for
this measure App, ID and maybe time and either use a virtual cube or the
LookupCube() function to pull the count from this cube.


Note: AS2k5 has a number of optimizations built into the engine so it
will be interesting to see how that would go.

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