multiselect result changes while add additional dimension to filter?

Every Query I introduce with multiselect to restrict the my rows works only as long as I do not add a second dimension to the filter fields ?

My Query (derived from Management Studio) - if you run it once like this and once with the comment line enabled the count of columns returned change - what I really did not understand, because only the values should change (different slice)?

Could anyone explain this to me? or point me to a location with detailed explanation? (I have checked http://www.mosha.com/msolap/articles/mdxmultiselectcalcs.htm this but it does not hit specially my question)

WITH
MEMBER [Sales Territory].[Sales Territory Group].[{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Hier164MultiFilter__] AS 'AGGREGATE({ [Sales Territory].[Sales Territory Group].&[North America], [Sales Territory].[Sales Territory Group].&[Pacific] })'
SET [{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Axis1Set0] AS '{ { [Sales Territory].[Sales Territory].[All Sales Territories] }, AddCalculatedMembers([Sales Territory].[Sales Territory].[Group].MEMBERS)}'
SELECT
[{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Axis1Set0] DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME , [Sales Territory].[Sales Territory].[Country].[Sales Territory Group] ON COLUMNS
FROM [Adventure Works]
WHERE
(
[{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Hier164MultiFilter__]
// , [Sales Channel].[Sales Channel].&[Internet]
)
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

Noone with an explanation or hint to further documentation ?

Who is it possible that an aditional slice of an independ dimension changes the rows / columns if not filter or empty is used ?

HANNES

multiselect result changes while add additional dimension to filter?

Every Query I introduce with multiselect to restrict the my rows works only as long as I do not add a second dimension to the filter fields ?

My Query (derived from Management Studio) - if you run it once like this and once with the comment line enabled the count of columns returned change - what I really did not understand, because only the values should change (different slice)?

Could anyone explain this to me? or point me to a location with detailed explanation? (I have checked http://www.mosha.com/msolap/articles/mdxmultiselectcalcs.htm this but it does not hit specially my question)

WITH
MEMBER [Sales Territory].[Sales Territory Group].[{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Hier164MultiFilter__] AS 'AGGREGATE({ [Sales Territory].[Sales Territory Group].&[North America], [Sales Territory].[Sales Territory Group].&[Pacific] })'
SET [{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Axis1Set0] AS '{ { [Sales Territory].[Sales Territory].[All Sales Territories] }, AddCalculatedMembers([Sales Territory].[Sales Territory].[Group].MEMBERS)}'
SELECT
[{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Axis1Set0] DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME , [Sales Territory].[Sales Territory].[Country].[Sales Territory Group] ON COLUMNS
FROM [Adventure Works]
WHERE
(
[{2120AF0D-0FD5-4B1F-8562-51CCF95CE627}Pivot8Hier164MultiFilter__]
// , [Sales Channel].[Sales Channel].&[Internet]
)
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

Noone with an explanation or hint to further documentation ?

Who is it possible that an aditional slice of an independ dimension changes the rows / columns if not filter or empty is used ?

HANNES

Multiple time dimensions and inventory measures.

I got 2 time-dimentions:

[Week]
Hierarchised dimension based on Year - Halfyear - Week - Day !

The supply calculated based on the Week-dimension
sum(
generate
(
ascendants([Week].currentmember),
iif(
[Week].currentmember IS
[Week].firstsibling,
{},
[Week].firstsibling:
[Week].prevmember
)
) + [Week].currentmember
,
[Measures].[Mutation]
)

[Posting Period]
Hierarchised dimension based on Year - Quarter - Month - Day !

The supply calculated based on the Posting Period-dimension
sum(
generate
(
ascendants([Posting Period].currentmember),
iif(
[Posting Period].currentmember IS
[Posting Period].firstsibling,
{},
[Posting Period].firstsibling:
[Posting Period].prevmember
)
) + [Posting Period].currentmember
,
[Measures].[Mutation]
)

My question:

iif(Is the Week-time-dimension used in the pivottable or cube-browse ?,
--Yes
sum(
generate
(
ascendants([Week].currentmember),
iif(
[Week].currentmember IS
[Week].firstsibling,
{},
[Week].firstsibling:
[Week].prevmember
)
) + [Week].currentmember
,
[Measures].[Mutation]
)
,
--No, the posting period time-dimension will be used probably
sum(
generate
(
ascendants([Posting Period].currentmember),
iif(
[Posting Period].currentmember IS
[Posting Period].firstsibling,
{},
[Posting Period].firstsibling:
[Posting Period].prevmember
)
) + [Posting Period].currentmember
,
[Measures].[Mutation]
)
)

Is there a way to merge the 2 time-dimensions without having duplicates in my measures ?

Kind regards,
GG

Here is an example from Adventure Works that shows how you can detect whether a user has selected members from the "Calendar" or "Fiscal" hierarchy. You should be able to adapt this to the problem you are trying to solve.

WITH

MEMBER Measures.[Time Hierarchy Used]

AS

CASE

WHEN [Date].Fiscal.CurrentMember.Level.Ordinal > 0 THEN

"Fiscal"

ELSE

"Calendar"

END

SELECT

{[Date].Calendar.DefaultMember.Children} ON ROWS,

--{[Date].Fiscal.DefaultMember.Children} ON ROWS,

{Measures.[Time Hierarchy Used]} ON COLUMNS

FROM

[Adventure Works]

HTH,

Steve

Multiple Sources, One Dimension

Hi
I have several DB's, and only one datawarehouse. I need to make sure that I'm not replicating dimensions. And then i need to map the references in the sources, to the references from the fact tables to the dimension table.
I use SQLServer2K
ThanksNot sure on your question, I got two possible answers.

1) if you are trying to use multiple facts or dim tables from multiple data sources you can create a cube based on a standard SQL view.

2) You typically use a surrogate key and a natural key for each dimension, the fact table should be able to reference your dim table by its natural key.

If neither addresses what you are asking, let me know.

HTH|||Table Worker on source1
ID name
---- ----------------
1 John
2 Paul
3 George

Table Worker on source2
ID name
---- ----------------
1 Paul
2 John
3 Micheal

what sort of primary key should I use in de Dimension table?

And having a table with sales referencing the worker by it's ID, how can I get the dimension table primary key?

Thanks|||You typically use a natural and surrogate key, surrogate key would be arbitrary identity field while your natural key is what your OLTP system uses to reference the row. DO you mean how to get the natural key in your fact table? This you do by just specifying to pull the primary key of the transaction for all your dims in your ETL process (process used to pull your data from the OLTP store)

HTH|||I'm making a tool to do the ETL process... and my problem is that the same member of one dimension may by present in multiple OLPT's, naturally with different natural keys.

What key sould I use in the DW as natural key?

When loading the fact table, what sould how can I get the surrogate key?

Thanks|||a Surrogate key could be just a sequential identity column, some people don't use them but they can be helpful in case there are changes in your natural key among other things.

I see what you are saying in multiple natural keys, would like an example of what you are seeing but I'll try to take a stab at what you mean.

Let's say I have 4 applications each with customer addresses. Each of thsoe applications would typically have their own data mart consisting of subject oriented cubes. You could either have multiple dimensions for each customer address (one for each application) or you could store within your dimension table all natural keys for that row, Something like this:

Dim_cust_address
Columns - sur_key (only one if you are storing all natural keys) - nat_key1 - nat_key2 - nat_key3 - nat_key4 - any other dimension properties (street, house_nbr, prefix,suffix,etc)

Now for your subject oriented data marts you would only need to store one natural key per fact table. (nat_key1, nat_key2, etc.)

Then when building your cubes you can either use a view to link the dims to the different facts or make shared dimensions and access that that way. I'll admit I haven't done something like this but it should work OK.

HTH

BTW: The best book for these type of questions and much more is Ralph Kimball's Data Warehouse Lifecycle toolkit.