Analysis 2005 HideMemberIf

Hi,

I have implemented dimensions with members with name.
I want to hide these members and i have set the HideMemberIf property to "NoName".
However i still have the empty members that appear.

Level1
|_SubLevel 1
| |_Child 1
| |_Child 2
|_(empty)
| |_Child 3
|_SubLevel 2

does anyone have an idea?
thanksYou should set the memberswithdata to NonLeafDataHidden. It has nothing to do with the HideMemberIf setting.

Analysis 2005 HideMemberIf

Hi,

I have implemented dimensions with members with name.
I want to hide these members and i have set the HideMemberIf property to "NoName".
However i still have the empty members that appear.

Level1
|_SubLevel 1
| |_Child 1
| |_Child 2
|_(empty)
| |_Child 3
|_SubLevel 2

does anyone have an idea?
thanksYou should set the memberswithdata to NonLeafDataHidden. It has nothing to do with the HideMemberIf setting.

Analysis 2005 Dimension Max members

Dear Anyone,

Does the dimensions for Analysis Services 2005 have any member limitations? Say a parent can only have so many members?

Thanks,

Joseph

The numbers of members in an level in an attribute hierarchy in Analysis Services 2005 has the limit of a 32-bit signed integer (2**32 = approx. 2 billion). The AS 2000 maximum of no more than 64,000 members per parent no longer applies. Also note that since the concept of a "dimension" has changed between the two versions of Analysis Services, you can now define more than one hierarchy per dimension in AS 2005, which makes it less meaningful to discuss the maximum number of members per parent. As I indicate, it is now the number of members in an attribute hierarchy that is interesting (an attribute hierarchy by default consists of two levels - an "All" level and a level including all of the members of that specific attribute) .|||

Is there a possibility that when processing a dimension (not parent and child) a memory error an happen even though the dimension is quite large?

The reason I asked is that we encountered a memory error problem when processing a large dimension. Understandably our dimension is large but it was also my impression that sql2005 removed the 64k limit.

AMO: Enumerate Calculated Members?

Hi,

Apologies if I'm missing something obvious here. I'm trying to enumerate the calculated members in a cube using AMO (as part of programmatically building perspectives), however I can't see an easy way to do this.

The collection of CalculationProperty objects on the cube's MdxScript object(s) would seem perfect (and indeed works for Adventure Works), but for my own hand-built cubes this collection is empty; CalculationProperty settings don't seem to be exposed anywhere in the BIDS UI so I assume they can only be set programmatically, which is of no particular use to me in the general case when operating on potentially manually-produced cubes.

Aside from using CalculationProperty the only way I can see to do this within AMO would be to parse the Command text associated with the MdxScript, but this is a heinous thing to have to do (by which I mean I'm surprised if this is required, rather than it being technically difficult). There are various ways I could get the information I need outside AMO, for example through ADOMDClient/ADOMDServer or firing off some MDX behind the scenes, but again I'd rather not unless necessary - surely it must be possible purely within AMO? How is BIDS doing it on the perspectives tab?

Many thanks for any suggestions,
Jon

Hi Jon,

I think I can answer half your question.

The calculationProperty are exposed in BIDS throught the calculation tab. There is a little properties button - 12th button from the left, just to the left of the script view button. This allows you to add/set all the CalculationProperty settings for all the calculated measures in your script.

I don't know how BIDS figures out what the calculated measures are. I can't see anything that might help in AMO.

|||Hi Darren,

Thanks for the helpful reply - it's funny, I've been switching back and forward using the Form View and Script View buttons in BIDS since the pre-beta days, and yet never noticed that Calculation Properties button right next to them :)

This does indeed partially solve my problem since at least I now have a non-hacky way of setting calculation properties for my own manually-built cubes (and hence can make use of CalculationProperty objects in AMO whenever I can mandate that calculation properties must be set in BIDS). However it still seems rather unsatisfactory that I have to require this surplus metadata to be added to the cube when all I want within AMO is to be able to get at calculated member names ... is this really the best I can do (if so then I still may resort to parsing the script command text to extract the names, rather than rely on metadata that cannot be guaranteed to be present)?

Thanks,
Jon|||Unfortunately, you do in fact need to parse through the MdxScript to enumerate calculcations from AMO. I agree that it's unfortunate. Fortunately, it's not too difficult - the script is nicely delimited.|||

You can parse the script with a Regular Expression too. The following seems to work reasonably well (although I am no RegEx expert so it could probably be improved upon)

(\bCREATE\s*MEMBER\s*.*\[?measures\]?[\n\r]*\.[\n\r]*\[?(?<CalcMeasure>[^\]]*)\]?\s*as\b|(?:\bCREATE\s*(\[(?<CalcMeasure>.*)\]|(?<CalcMeasure>[^\s]*))\s*\=))

I setup a sample project with some NUnit tests to check it was working properly here http://projectdistributor.net/Files/73/227/381_Source.zip

This expression works with both the "old" and "new" syntax for calculated measures.

I have an explanation of what the regex is doing that I plan to post on my blog, but my provider is having issues at the moment.

Cheers

Darren

|||Hi guys,

Kevin, thanks for confirming what I feared might be the case. Darren, thanks for the regex - I'd decided do go with a regex parse myself but hadn't actually got round to doing it yet, so it looks like you've saved me the effort.

At least the rest of AMO is pretty sensible from what I've seen ... don't get me started on the SSIS API though

Cheers,
Jon|||No worries - it was an interesting puzzle for my lunch break |||Some people go to the pub during their lunch break ... others write regexes

I'm glad you did though!

Jon|||

turns out there is an API for parsing the calc script. It's not "officially supported" but works great. See mention of Microsoft.AnalysisServices.Design.Scripts(Microsoft.AnalysisServices.Cube c) at...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=324224&SiteID=1

You might also vote for that connect issue mentioned at the end of the thread.

AMO: Enumerate Calculated Members?

Hi,

Apologies if I'm missing something obvious here. I'm trying to enumerate the calculated members in a cube using AMO (as part of programmatically building perspectives), however I can't see an easy way to do this.

The collection of CalculationProperty objects on the cube's MdxScript object(s) would seem perfect (and indeed works for Adventure Works), but for my own hand-built cubes this collection is empty; CalculationProperty settings don't seem to be exposed anywhere in the BIDS UI so I assume they can only be set programmatically, which is of no particular use to me in the general case when operating on potentially manually-produced cubes.

Aside from using CalculationProperty the only way I can see to do this within AMO would be to parse the Command text associated with the MdxScript, but this is a heinous thing to have to do (by which I mean I'm surprised if this is required, rather than it being technically difficult). There are various ways I could get the information I need outside AMO, for example through ADOMDClient/ADOMDServer or firing off some MDX behind the scenes, but again I'd rather not unless necessary - surely it must be possible purely within AMO? How is BIDS doing it on the perspectives tab?

Many thanks for any suggestions,
Jon

Hi Jon,

I think I can answer half your question.

The calculationProperty are exposed in BIDS throught the calculation tab. There is a little properties button - 12th button from the left, just to the left of the script view button. This allows you to add/set all the CalculationProperty settings for all the calculated measures in your script.

I don't know how BIDS figures out what the calculated measures are. I can't see anything that might help in AMO.

|||Hi Darren,

Thanks for the helpful reply - it's funny, I've been switching back and forward using the Form View and Script View buttons in BIDS since the pre-beta days, and yet never noticed that Calculation Properties button right next to them :)

This does indeed partially solve my problem since at least I now have a non-hacky way of setting calculation properties for my own manually-built cubes (and hence can make use of CalculationProperty objects in AMO whenever I can mandate that calculation properties must be set in BIDS). However it still seems rather unsatisfactory that I have to require this surplus metadata to be added to the cube when all I want within AMO is to be able to get at calculated member names ... is this really the best I can do (if so then I still may resort to parsing the script command text to extract the names, rather than rely on metadata that cannot be guaranteed to be present)?

Thanks,
Jon|||Unfortunately, you do in fact need to parse through the MdxScript to enumerate calculcations from AMO. I agree that it's unfortunate. Fortunately, it's not too difficult - the script is nicely delimited.|||

You can parse the script with a Regular Expression too. The following seems to work reasonably well (although I am no RegEx expert so it could probably be improved upon)

(\bCREATE\s*MEMBER\s*.*\[?measures\]?[\n\r]*\.[\n\r]*\[?(?<CalcMeasure>[^\]]*)\]?\s*as\b|(?:\bCREATE\s*(\[(?<CalcMeasure>.*)\]|(?<CalcMeasure>[^\s]*))\s*\=))

I setup a sample project with some NUnit tests to check it was working properly here http://projectdistributor.net/Files/73/227/381_Source.zip

This expression works with both the "old" and "new" syntax for calculated measures.

I have an explanation of what the regex is doing that I plan to post on my blog, but my provider is having issues at the moment.

Cheers

Darren

|||Hi guys,

Kevin, thanks for confirming what I feared might be the case. Darren, thanks for the regex - I'd decided do go with a regex parse myself but hadn't actually got round to doing it yet, so it looks like you've saved me the effort.

At least the rest of AMO is pretty sensible from what I've seen ... don't get me started on the SSIS API though

Cheers,
Jon|||No worries - it was an interesting puzzle for my lunch break |||Some people go to the pub during their lunch break ... others write regexes

I'm glad you did though!

Jon|||

turns out there is an API for parsing the calc script. It's not "officially supported" but works great. See mention of Microsoft.AnalysisServices.Design.Scripts(Microsoft.AnalysisServices.Cube c) at...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=324224&SiteID=1

You might also vote for that connect issue mentioned at the end of the thread.

AMO: Check if role member is existing in Role

Hi,

In AMO, how do I check if role member is existing in Role? I have this command (VB.net):

If UserRole1.Members.Contains(mydatareader(0)) = true then

.

.

end if

But it keeps having an error:

Unable to cast object of type 'System.String' to type 'Microsoft.AnalysisServices.RoleMember'.

How do i check properly?

thanks .

cherriesh

The contains function expects a MemberRole reference, not a string. I can't see anyway of checking directly if a role contains a given member name so you would have to loop over the collection. Something like the following should work. Note: I have coded this off the top of my head so I hope that I have not made any typo's but you should get the general idea.

Code Snippet

If RoleContains(UserRole1, mydatareader(0)) then

.

.

end if

Function RoleContains( myRole as Role, memberName as string)

For each rm as RoleMember in myRole.Members

'// I have used string.compare to do a case-insensitive compare

If string.compare(rm.Name, memberName,true) = 0 then

return true

End If

Next

Return false

End Function

AMO Question - Distinct members in Cube

Hi,

How to filter members in a dimension.

To meet a requirement, I made a table as a Fact and a Dimension. In the said table, I have a column which is varchar and contains unique values always. They can be said as 'A' or 'B' or 'C'. For all records in the said table, any of these three values can be present. But they are not from any lookup table but come from a third party system.

Using the AMO object, I am listing all the values are members. But the problem I am facing is, I am getting all values as members. As per my requirement, I must list only distinct of them

I am setting the DimensionAttribute object's Usage property to Key, OrderBy to OrderBy.Name and Type to Regular. What other property need to be set to list only the unique values as members?

I request a quick response to this query. I am desperately looking for the reply to solve the problem.

Thanks

Sekhar PC

Sorry Sekhar.

But the description of your problem is not very clear. Can you try to describe what you are trying to acheve.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi,

Thank you for your reply.

In my cube, I have a fact table which has a column of varchar type. Assume that, the possible values are A,B,C,D.

While building the cube using the AMO and VB.Net, I am required to list them as members in the cube. Essentially, I am required to fetch the distinct values from that column and list as members.

How to that using AMO and VB.Net?

Thanks in advance

Sekhar

|||

You dont use AMO for retreiving data from Analysis Services. For that you use ADOMD.NET object model ( not the only option, but it might be easiest for you).

Here is some information on how to use ADOMD.NET

http://msdn2.microsoft.com/en-us/library/ms123483.aspx
http://msdn2.microsoft.com/en-us/library/ms123477.aspx

You can try and get a book on Analysis Services: Teo Lachev's "Applied Microsoft Analysis Services 2005.. " is good one you can use.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

AMO Question - Distinct members in Cube

Hi,

How to filter members in a dimension.

To meet a requirement, I made a table as a Fact and a Dimension. In the said table, I have a column which is varchar and contains unique values always. They can be said as 'A' or 'B' or 'C'. For all records in the said table, any of these three values can be present. But they are not from any lookup table but come from a third party system.

Using the AMO object, I am listing all the values are members. But the problem I am facing is, I am getting all values as members. As per my requirement, I must list only distinct of them

I am setting the DimensionAttribute object's Usage property to Key, OrderBy to OrderBy.Name and Type to Regular. What other property need to be set to list only the unique values as members?

I request a quick response to this query. I am desperately looking for the reply to solve the problem.

Thanks

Sekhar PC

Sorry Sekhar.

But the description of your problem is not very clear. Can you try to describe what you are trying to acheve.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi,

Thank you for your reply.

In my cube, I have a fact table which has a column of varchar type. Assume that, the possible values are A,B,C,D.

While building the cube using the AMO and VB.Net, I am required to list them as members in the cube. Essentially, I am required to fetch the distinct values from that column and list as members.

How to that using AMO and VB.Net?

Thanks in advance

Sekhar

|||

You dont use AMO for retreiving data from Analysis Services. For that you use ADOMD.NET object model ( not the only option, but it might be easiest for you).

Here is some information on how to use ADOMD.NET

http://msdn2.microsoft.com/en-us/library/ms123483.aspx
http://msdn2.microsoft.com/en-us/library/ms123477.aspx

You can try and get a book on Analysis Services: Teo Lachev's "Applied Microsoft Analysis Services 2005.. " is good one you can use.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.