Ho to filter dimension in BIDS

Hi,
I have 2 cubes, Cube_A and Cube_B, and a common dimension, Dim_C.
When I browse Cube_A I don't want to see dimension members that have some values (for example some name members that begin with caracter "A" or "Z").

How can I do this using calculation tab of "Business Intelligence Development Studion" (BIDS) ?

(I don't want to define role on dimension)
thanks.

We often limit sets on the axes as part of the query (MDX) statement, but I've never heard of making specific members of a set non-visible for all users and all browsers like this. I realize you said you don't want to create a role, but that may be the only way to accomplish this.

If someone else out there knows a way to pull this off, I'd be interested in hearing the solution.

Good luck,
Bryan

Hierarchy Problem after processing - Help!

Hi,

I'm using AS2005 and have time dimension with hierarchy as follows:

[yr_dt] --> [yr] --> [hyr] --> [qtr] --> [mon] --> [dt]

Normally, it show full hierarchy when I browse the hierarchy :
e.g. 2006 --> 1 half --> 1 quarter --> Jan --> 2006-01-01T00:00:00

However, after I change the data source view to include more data in specified data range and reprocess the cube, The hierarchy only shows [yr] nothing under [yr].
Only correct year is the last year (i.e. 2006). It show full hierarchy as above.

May I know what's wrong with AS2005. Or is it a bug ? Any method to solve ?
Thanks in advanceHi,

my time dimension has the following relationship:

[yr]
|-- [hyr]
cardinality : many
relationship type: rigid

[hyr]

|-- [qtr]

cardinality : many

relationship type: rigid

[mon]

|-- [qtr]

cardinality : many

relationship type: rigid

[dt]

|-- [mon]

cardinality : many

relationship type: rigid

When I remove all the above relationship and reprocess the cube. It works fine and
the full hierarchy show correctly.

May I know what's wrong with the above hierarchy relationship setting ?
thanks a lot .|||

Are your attributes unique?

i.e.

Year -> 2007

Quarters

Q1, 2007

Q2, 2007

Q3, 2007

Month

January, 2007

February, 2007

Day

1/1/2007

1/2/2007

etc

If they are not unique (i.e. A must imply B in your relationships, you can't have just January since January does not imply January in a specific year) then the aggregations don't work properly.

|||Hi mccpres,

thanks for your reply.
Is that you mean the KeyColumn unique ? or the NameColumn Unique ?

My settings for [Mon] attribute
NameColumn : abbreviation of month (i.e. Jan, Feb ...etc)
KeyColumn : integer value of month (i.e. 1 to 12)

My settings for [Qtr] attribute

NameColumn : 'Q'mm + yyyy (i.e. Q1 2002, Q2 2002 ....etc)
KeyColumn : integer value of year (i.e. 2002, 2003 etc)

Here, NameColumn is unique to corresponding year. But the KeyColumn is not.
So, seems make the NameColumn unique would solve the issue.

Also, there's ValueColumn properties for each attribute. It seems that the ValueColumn should be same as KeyColumn, right ? Then why we need ValueColumn ?

Above maybe just simple questions, but just want to clarify my understanding as I new to ssas. Thanks a lot !|||hi mccpres,

thanks for your reply.
Is that you mean Keycolumn unique or NameColumn unique ?

I change my setting for [qtr] attribute:
NameColumn: 'Q'mm+yyyy (ie. Q1 2002, Q2 2002 ...etc)
KeyColumn: integer value of quarter (i.e. 1, 2, 3, 4 )

for [yr] attribute:
NameColumn : 'Y'+yyyy (ie Y2002, Y2003 ...etc)
KeyColumn: integer value of year (ie 2002, 2003 ...etc)

Here NameColumn for [qtr] is unique to corresponding NameColumn of [yr]. But the KeyColumn is not.
I tried the above settings and seems make the NameColumn unique would solve the issue. right?

Also, is that we should set KeyColumn same as the ValueColumn ?

Above maybe just simple questions, appreciate if anyone can help as I'm new to ssas. thanks!|||Hi,

anyone can help ?

Hierarchy Member MDX Name

I created a 'Budgeting Time' hierarchy from a dimension table that looks like:

IDYearMonth08C5DC29-6E61-4625-BA20-CFDFE5D3232D2006Jan

KeyColumns is binded to Year/Month and so is NameColumn. The MDX representation of the year 2006 is now [Budgeting Time].[Budgeting Time].[Year].&[2006] but is there anyway to have the ID as the member identifier so the MDX looks like [Budgeting Time].[Budgeting Time].[Year].&[08C5DC29-6E61-4625-BA20-CFDFE5D3232D]?

If your entity can equally be uniquely identified either by ID or { Year, Month } pair you can choose ID to be the key column of your attribute. MDX code refers to unique names, which often use the key columns of attributes. If you choose ID to be the key column of the attribute then you will have the desired representation of the unique name.

When you browse your dimension the user interface will show captions, which will be resolved through the NameColumn binding. I do not know how you succeeded to bind Name to Year/Month since NameColumn can only be one and not a collection like KeyColumns. Probably you created a calculated column on DSV and provided a formula joining the name of the month and year together. This can stay if you choose ID to be the key column of the attribute.

I am not sure about your goal to have specific unique names, but key columns are chosen by the relationships with a fact table or a dimension table. If we are talking about the granularity attribute then it will participate in some relationship with either a fact table (regular relationship) or another dimension table (referenced relationship). If it is ID, which participates in that relationship then you would better choose ID for the key column. If your fact table or referenced dimension table contains Year/Month pair then you would better choose Year/Month to be the key columns.

Hierarchy Member MDX Name

I created a 'Budgeting Time' hierarchy from a dimension table that looks like:

ID

Year

Month

08C5DC29-6E61-4625-BA20-CFDFE5D3232D

2006

Jan

KeyColumns is binded to Year/Month and so is NameColumn. The MDX representation of the year 2006 is now [Budgeting Time].[Budgeting Time].[Year].&[2006] but is there anyway to have the ID as the member identifier so the MDX looks like [Budgeting Time].[Budgeting Time].[Year].&[08C5DC29-6E61-4625-BA20-CFDFE5D3232D]?

If your

entity can equally be uniquely identified either by ID or { Year, Month } pair

you can choose ID to be the key column of your attribute. MDX code refers to

unique names, which often use the key columns of attributes. If you choose ID to

be the key column of the attribute then you will have the desired

representation of the unique name.

When you

browse your dimension the user interface will show captions, which will be

resolved through the NameColumn binding. I do not know how you succeeded to

bind Name to Year/Month since NameColumn can only be one and not a collection

like KeyColumns. Probably you created a calculated column on DSV and provided a

formula joining the name of the month and year together. This can stay if you

choose ID to be the key column of the attribute.

I am not

sure about your goal to have specific unique names, but key columns are chosen

by the relationships with a fact table or a dimension table. If we are talking

about the granularity attribute then it will participate in some relationship

with either a fact table (regular relationship) or another dimension table

(referenced relationship). If it is ID, which participates in that relationship

then you would better choose ID for the key column. If your fact table or

referenced dimension table contains Year/Month pair then you would better

choose Year/Month to be the key columns.

Hierarchy Equivalent of a Crossjoin

I want to pull back a cellset with two levels of a hierarchy on the same dimension. Using AdventureWorks as an example, let's say I want "Account Level 01" and "Account Level 02" both on the rows dimesion.

When I send MDX like this:

SELECT

{[Account].[Accounts].[Account Level 01].Members * [Account].[Accounts].[Account Level 02].Members}

DIMENSION PROPERTIES MEMBER_TYPE ON AXIS(0) FROM [Adventure Works]

I get this error message:

The Accounts hierarchy is used more than once in the Crossjoin function.

What MDX would I need in this situation? I've tried spying on the MDX generated by BIDs, but it uses so many temporary SETs that it's nearly impossible to follow.

Thanks for any help,

Terry

Terry,

If my understanding of what you are trying to do is correct, one possible solution would be to use the "Generate()" function:

Generate([Account].[Accounts].[Account Level 01].Members,

{[Account].[Accounts].CurrentMember,[Account].[Accounts].CurrentMember.Children})

HTH,

Steve

Hierarchy - Measure question

Hi,

In the cube, i'm using one fact table (I will call just A) as a fact (A) and dimension (A1).

From this table(A) , I created hierarchy ( division - department with primary key from this fact) in the dimension(A1).

And we have another fact table(B) which is not related to the fact (A) and dimension(A1) fisically.

Here is the fact table (B) as under:

Plan Fact : ID(pk), Item_key, Plan_Sales , Date_key

I can join this fact (B) with product dimension which has item_key (pk) and this product dimension has division and deparment key.

My question is that is there anyway to see plan sales measure with the hierarchy I created?

Please let me know.

Thanks in advance.

Hi there,

AFAIK, you could achieve this with Analysis Services 2005.

Your data source view would consist of your department dimension, linked to both of your fact tables. When creating your cube in Analysis Services 2005, the cube creation wizard should pick up that you have two fact tables and a single dimension (you may need to point it in the right direction though!).

This should generate you a cube schema, with a single dimension (department) and two measure groups. One measure group would contain measures from fact table A, and the other measure group would contain measures from fact table B. One use of a measure group is that it can group measures from a single fact table.

Using the cube browser you should be able to drag on your division-department hierarchy, along with the relevant measures from each measure group.

Hope that helps.

Cheers,

Jonathon

|||Actually you can create what is called a referenced relationship in AS2005 where one dimension is joined to a fact table through another dimension. So you could relate the A1 dimension to the Plan fact table through the Item dimension.

|||

Hi Darren,

Sorry for the late response and thanks for your reply.

I've tried to make a referenced relationship but it seems it does not give a right value.

if I select the reference dimension attribute : divdep ( which includes all attributes in divdep dimension) and intermediate dimension attribute : item_key ( pk in product dimension) which I join the fact table with, then the total is fine but in each division level and department level , the numbers are not correct.

If I select reference dimension attribute : division name and intermediate dimension attribute: division name , then I can see the right number only in division level. ( My question is do i need to select lowest level in reference dimension attribute? in this case , department name is the lowest in hierarchy. But it gives an error since this dimension does not have some attributes which appears in fact table.)

I need to view the data with division- department hierarchy and I've tried to do every single possible thing but I don't have any clue yet.

I appreciate if you can give me any comments.

Thanks.

|||

I'm not sure I'm completely understanding how you have things setup.

If you have a product dimension which includes an attribute for the department, you should be able to join the department dimension at it's key to the department attribute in the product dimension.

There is an example of a reference relationship in the Adventure Works cube, have a look at the Geography dimension and you should be able to find an example.

Hierarchy - Measure question

Hi,

In the cube, i'm using one fact table (I will call just A) as a fact (A) and dimension (A1).

From this table(A) , I created hierarchy ( division - department with primary key from this fact) in the dimension(A1).

And we have another fact table(B) which is not related to the fact (A) and dimension(A1) fisically.

Here is the fact table (B) as under:

Plan Fact : ID(pk), Item_key, Plan_Sales , Date_key

I can join this fact (B) with product dimension which has item_key (pk) and this product dimension has division and deparment key.

My question is that is there anyway to see plan sales measure with the hierarchy I created?

Please let me know.

Thanks in advance.

Hi there,

AFAIK, you could achieve this with Analysis Services 2005.

Your data source view would consist of your department dimension, linked to both of your fact tables. When creating your cube in Analysis Services 2005, the cube creation wizard should pick up that you have two fact tables and a single dimension (you may need to point it in the right direction though!).

This should generate you a cube schema, with a single dimension (department) and two measure groups. One measure group would contain measures from fact table A, and the other measure group would contain measures from fact table B. One use of a measure group is that it can group measures from a single fact table.

Using the cube browser you should be able to drag on your division-department hierarchy, along with the relevant measures from each measure group.

Hope that helps.

Cheers,

Jonathon

|||Actually you can create what is called a referenced relationship in AS2005 where one dimension is joined to a fact table through another dimension. So you could relate the A1 dimension to the Plan fact table through the Item dimension.

|||

Hi Darren,

Sorry for the late response and thanks for your reply.

I've tried to make a referenced relationship but it seems it does not give a right value.

if I select the reference dimension attribute : divdep ( which includes all attributes in divdep dimension) and intermediate dimension attribute : item_key ( pk in product dimension) which I join the fact table with, then the total is fine but in each division level and department level , the numbers are not correct.

If I select reference dimension attribute : division name and intermediate dimension attribute: division name , then I can see the right number only in division level. ( My question is do i need to select lowest level in reference dimension attribute? in this case , department name is the lowest in hierarchy. But it gives an error since this dimension does not have some attributes which appears in fact table.)

I need to view the data with division- department hierarchy and I've tried to do every single possible thing but I don't have any clue yet.

I appreciate if you can give me any comments.

Thanks.

|||

I'm not sure I'm completely understanding how you have things setup.

If you have a product dimension which includes an attribute for the department, you should be able to join the department dimension at it's key to the department attribute in the product dimension.

There is an example of a reference relationship in the Adventure Works cube, have a look at the Geography dimension and you should be able to find an example.

Hierarchy - Measure question

Hi,

In the cube, i'm using one fact table (I will call just A) as a fact (A) and dimension (A1).

From this table(A) , I created hierarchy ( division - department with primary key from this fact) in the dimension(A1).

And we have another fact table(B) which is not related to the fact (A) and dimension(A1) fisically.

Here is the fact table (B) as under:

Plan Fact : ID(pk), Item_key, Plan_Sales , Date_key

I can join this fact (B) with product dimension which has item_key (pk) and this product dimension has division and deparment key.

My question is that is there anyway to see plan sales measure with the hierarchy I created?

Please let me know.

Thanks in advance.

Hi there,

AFAIK, you could achieve this with Analysis Services 2005.

Your data source view would consist of your department dimension, linked to both of your fact tables. When creating your cube in Analysis Services 2005, the cube creation wizard should pick up that you have two fact tables and a single dimension (you may need to point it in the right direction though!).

This should generate you a cube schema, with a single dimension (department) and two measure groups. One measure group would contain measures from fact table A, and the other measure group would contain measures from fact table B. One use of a measure group is that it can group measures from a single fact table.

Using the cube browser you should be able to drag on your division-department hierarchy, along with the relevant measures from each measure group.

Hope that helps.

Cheers,

Jonathon

|||Actually you can create what is called a referenced relationship in AS2005 where one dimension is joined to a fact table through another dimension. So you could relate the A1 dimension to the Plan fact table through the Item dimension.|||

Hi Darren,

Sorry for the late response and thanks for your reply.

I've tried to make a referenced relationship but it seems it does not give a right value.

if I select the reference dimension attribute : divdep ( which includes all attributes in divdep dimension) and intermediate dimension attribute : item_key ( pk in product dimension) which I join the fact table with, then the total is fine but in each division level and department level , the numbers are not correct.

If I select reference dimension attribute : division name and intermediate dimension attribute: division name , then I can see the right number only in division level. ( My question is do i need to select lowest level in reference dimension attribute? in this case , department name is the lowest in hierarchy. But it gives an error since this dimension does not have some attributes which appears in fact table.)

I need to view the data with division- department hierarchy and I've tried to do every single possible thing but I don't have any clue yet.

I appreciate if you can give me any comments.

Thanks.

|||

I'm not sure I'm completely understanding how you have things setup.

If you have a product dimension which includes an attribute for the department, you should be able to join the department dimension at it's key to the department attribute in the product dimension.

There is an example of a reference relationship in the Adventure Works cube, have a look at the Geography dimension and you should be able to find an example.

HIERARCHY

i have a problem.. which is the procedure to establish the hierarchies in my dimension.. the dimension wizard sends a message that the relationships were checked but did not create any hierarchies?

is there any propertie i should checke before running the wizard?

The cube and dimension wizards will attempt to create hierarchies based on relationships between tables and relationships found when sampling data. However, it may not discover many relationships, especially when the amount of data in the table is small. You can manually create hierarchies in the dimension editor by dragging attributes onto the "Hierarchies and Levels" pane.

HIERARCHY

i have a problem.. which is the procedure to establish the hierarchies in my dimension.. the dimension wizard sends a message that the relationships were checked but did not create any hierarchies?

is there any propertie i should checke before running the wizard?

The cube and dimension wizards will attempt to create hierarchies based on relationships between tables and relationships found when sampling data. However, it may not discover many relationships, especially when the amount of data in the table is small. You can manually create hierarchies in the dimension editor by dragging attributes onto the "Hierarchies and Levels" pane.

Hiding fields in Fact-Dimension Table

I have a fact table i set as both dimension and fact. so this one is linked to different dimension tables and at the same time, some of its values are in measure group.

the thing is, in the list of its attributes, i can see the keys to which the different dimensions are linked. i cannot remove these key because they serve as links. but i dont want the users to see this key. it's not helpful to them anyway. how can i do it?

Example:

Fct_ServiceJobItem (Set as both dimension and fact)

JobNo

LineNum

Partkey

Amount

Dim_Parts (Dimension)

PartKey

PartNo

.

.

I dont want to see the Partkey in the Dimension Fct_ServiceJobItem. But this is linked to Dim_Parts and removing it can cause error.

cherriesh

In the designer , highlight the Fact, or attribute you want to hide and press F4.

set Visible for facts, or attributehierarchyvisible for dimensions to false

Hiding "ALL" level in a dimension

Hi,

When I browse the data of any dimension then I have the "ALL" level and then the members of the dimension.

How can I remove / hide the "ALL" level in Analysis Services 2005.

Thanks in Advance

You can set the IsAggregatable property of the attributes to false. But this has other implications and might not be exactly what you want as the All member is the default member. The easiest way to explain this is with an example.

If you were to display Sales Amount by customer, without specifying a member of the product dimension then the "All Products" member would be used. If you were to change the Product dimension to remove the all member then you would either have to specify a default member or specify a particular member as default or SSAS will pick a member (usually the first one in the dimension) to use as the default. Thus when you look at Sales Amount by Customer it would now only be for one particular product.

Personally I only remove the all member in very specific circumstances. When doing budgeting cubes, where you have a dimension with budget/actual/forecast members, then it does not make sense to aggregate these together so I usually remove the all member. And for some cubes I take it out of the time dimension as the concept of aggregating measures across all time members does not always make sense.