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

high Availability cubes: During Cube metadata updates + ProClarity

Hi,

This is a kind of solution design issue.

Scenario: We have a web page that displays information from a Oracle data warehouse. We collect the required information in the page and have used AMO to create cubes and other related objects from this information. The source for the cubes in SSAS 2005 would point to Oracle. This is modelled as a web service and used within the web page. Once the Cube gets created in SSAS, we model ProClarity graphs on them.

The problem: The users can invoke the AMO web service from the web page, to add a new MeasureGroup to an existing cube. Once this is done the Cube becomes non-browsable. because we are adding a new MeasureGroup and thus modifying the cube metadata.

In detail, If the new MeasureGroup that is added, uses dimensions that are already cube-dimensions then- only the newly added MeasureGroup will be UnProcessed and the exsiting MeasureGroups will be processed. (cube is UnProcessed state).

otherwise, if the new MeasureGroup that is added, adds a new cube-dimension to the cube then- all the MeasureGroups will be in the UnProcessed state including the newly added MeasureGroup.

Now, until the user invokes a 'process' request, for the cube that is modified and the processing is complete, that paticular cube is not accesible. All the Proclarity graphs that are linked to that cube, show error messages!

How can we keep the Proclarity graphs / users pointed to a old copy of the cube, at least until the newly modified cube is processed and online?

If not, are there any other approaches to get high availability for the cubes or for the ProClarity Graphs?

Can we create a offline cube that can be used temporarily? if so how to dynamically switch between the offline cube and the online cube?

Regards

If you do all the AMO operations including creating and processing the cube within transaction, then the previous version of the cube will be available until transaction commits. After transaction commited, the new version of the cube will be immediatelly avaiable.|||

Hi Mosha,

Thanks for the tip.

Since this operation is used in a J2EE web site using a AMO web service.

If I include the processing task with it then it would take a huge time for large cubes.

Currently we are using the 'Ascmd' to do the processing. This is scheduled using a scheduler.

We planned to somehow call Ascmd Asynchronously from the web page and intimate users using an email after the processing completes.

can we include the processing also in the previous transaction while using Ascmd?

Apart from this, any other mechanism that we can use like Raid/mirroring, Failover clustering. etc?

Or temporary local cubes?

:-) Just a wild shot: Probably the next version of SSAS needs to include something similary "shadow-copy-cube".

Thanks and Regards

|||

Just a wild shot: Probably the next version of SSAS needs to include something similary "shadow-copy-cube".

No need to wait for the next version. SSAS2005 already supports Sync functionality, where you can build and process cube on one server and then sync it to another server.

|||

You might consider using AS 2005's "synchronize database" capability. This would require (I think) two servers. The first would be the "modify and process" server (call it ServerA) while the second would be the "presentation" server (call it ServerB).

In your application, you would have the users always browsing the cube(s) on ServerB. As they make changes to the design of the cube (adding measure groups, cube dimensions, etc.), you'd direct those changes to the cube(s) on ServerA. You could then asynchronously use ascmd to reprocess part or all of a modified cube on ServerA (while they continue to use the cube on ServerB). Once the processing is complete, you could execute an XMLA script on ServerB, requesting that the database on ServerB be sychronized from ServerA. The nice thing about this is that the synchronization occurs within a transaction, so the cube on ServerB is still usable while the sychronization occurs and the updated version of the cube is then available to users immediately once it is complete.

Take a look at the following link in BOL for the XMLA element for the synchronize command:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/xmla9/html/9401323c-feff-409a-a9da-94aee47e0563.htm

HTH,

Dave Fackler

|||

Hi,

Wow!

Thanks a lot for that.

I am going to implement the "Sync Database", This will solve my problem!

although I would take some time to complete this , I will let you know when I start.

Regards

high Availability cubes: During Cube metadata updates + ProClarity

Hi,

This is a kind of solution design issue.

Scenario: We have a web page that displays information from a Oracle data warehouse. We collect the required information in the page and have used AMO to create cubes and other related objects from this information. The source for the cubes in SSAS 2005 would point to Oracle. This is modelled as a web service and used within the web page. Once the Cube gets created in SSAS, we model ProClarity graphs on them.

The problem: The users can invoke the AMO web service from the web page, to add a new MeasureGroup to an existing cube. Once this is done the Cube becomes non-browsable. because we are adding a new MeasureGroup and thus modifying the cube metadata.

In detail, If the new MeasureGroup that is added, uses dimensions that are already cube-dimensions then- only the newly added MeasureGroup will be UnProcessed and the exsiting MeasureGroups will be processed. (cube is UnProcessed state).

otherwise, if the new MeasureGroup that is added, adds a new cube-dimension to the cube then- all the MeasureGroups will be in the UnProcessed state including the newly added MeasureGroup.

Now, until the user invokes a 'process' request, for the cube that is modified and the processing is complete, that paticular cube is not accesible. All the Proclarity graphs that are linked to that cube, show error messages!

How can we keep the Proclarity graphs / users pointed to a old copy of the cube, at least until the newly modified cube is processed and online?

If not, are there any other approaches to get high availability for the cubes or for the ProClarity Graphs?

Can we create a offline cube that can be used temporarily? if so how to dynamically switch between the offline cube and the online cube?

Regards

If you do all the AMO operations including creating and processing the cube within transaction, then the previous version of the cube will be available until transaction commits. After transaction commited, the new version of the cube will be immediatelly avaiable.|||

Hi Mosha,

Thanks for the tip.

Since this operation is used in a J2EE web site using a AMO web service.

If I include the processing task with it then it would take a huge time for large cubes.

Currently we are using the 'Ascmd' to do the processing. This is scheduled using a scheduler.

We planned to somehow call Ascmd Asynchronously from the web page and intimate users using an email after the processing completes.

can we include the processing also in the previous transaction while using Ascmd?

Apart from this, any other mechanism that we can use like Raid/mirroring, Failover clustering. etc?

Or temporary local cubes?

:-) Just a wild shot: Probably the next version of SSAS needs to include something similary "shadow-copy-cube".

Thanks and Regards

|||

Just a wild shot: Probably the next version of SSAS needs to include something similary "shadow-copy-cube".

No need to wait for the next version. SSAS2005 already supports Sync functionality, where you can build and process cube on one server and then sync it to another server.

|||

You might consider using AS 2005's "synchronize database" capability. This would require (I think) two servers. The first would be the "modify and process" server (call it ServerA) while the second would be the "presentation" server (call it ServerB).

In your application, you would have the users always browsing the cube(s) on ServerB. As they make changes to the design of the cube (adding measure groups, cube dimensions, etc.), you'd direct those changes to the cube(s) on ServerA. You could then asynchronously use ascmd to reprocess part or all of a modified cube on ServerA (while they continue to use the cube on ServerB). Once the processing is complete, you could execute an XMLA script on ServerB, requesting that the database on ServerB be sychronized from ServerA. The nice thing about this is that the synchronization occurs within a transaction, so the cube on ServerB is still usable while the sychronization occurs and the updated version of the cube is then available to users immediately once it is complete.

Take a look at the following link in BOL for the XMLA element for the synchronize command:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/xmla9/html/9401323c-feff-409a-a9da-94aee47e0563.htm

HTH,

Dave Fackler

|||

Hi,

Wow!

Thanks a lot for that.

I am going to implement the "Sync Database", This will solve my problem!

although I would take some time to complete this , I will let you know when I start.

Regards

hididng calculated measures in the cube browser window

hi i am currenly using analysis serivces 2005 for making cubes.

my problems are 2 fold

1)the list of calcualted measures is around 35 so when i go to browser window in the cube they actually give a very bad look.Can i make any folder within which we can keep all the calculated measures and then drill down according to the requirement. OR if i can divide all the calculated measures according to the need-- say "primary sales" calculated measure folder contains all the calculated measures that fall under that category and simlarly "order daily" contains all the calculated measures that fall under its category.

2)the end user will be accesing the cube using BIP now in the cube Dimension we can discriminate among the hierarchy and attributes by there default symbols but when we actually see it in the BIP window we cannot distinctly identify which is a attrribute and which is a hierarchy,which might confuse the business user.

kindly reply asap

cheers
Sid
..u can mail me at sidhuvirgoster@.gmail.com(for answer and further clarification of question)

Unfortunately I don't have a lot of good news for you.

1) you can put measures into display folders, there is a button on the toolbar of the calculations tab called "Calculation Properties" which lets you set this. (the button looks like a properties window with a little yellow hand over it)

2) Unfortunately BIP uses OWC which was written before AS2005 existed and it is not aware of attributes or hierarchies, it treats them all as just dimensions. There is no way around this and MS has discontinued development of OWC so it will not change in the future. And in relation to your first point, OWC is not aware of display folders either so all your measures will still be displayed in a big long list.

All I can suggest is to have a look at some of the other 3rd party browsers, Mosha has one of the most comprehensive lists around at www.mosha.com/msolap

|||About 2.

Why is this so important for the end user to see where the attribute hierarchy is? As far as i understand the model, an attribute hierarchy is still a hierarchy, which consists of one level and represents just a plain list of members of certain entity (attribute). They are visible to the end user by default, but the designer can make them invisible, leaving just those hierarchies, which do not confuse the user.

For the cube developer - yes it is more important to see what comes from what.|||

I agree that end users should not really care about the difference between attribute hierarchies and other hierarchies. And typically if I have "Month" in a user defined hierarchy I will hide the attribute hierarchy so they don't see the same thing twice. One problem I have seen is situation where two different dimensions have attributes with the same name.

eg.

[Date].[Financial] and [Organisation].[Financial] would both show up as just Financial in OWC

or [Ship Date].[Month] and [Order Date].[Month] would both show up as Month

The only real work around if you need to use an older client (like OWC) is to make sure that the levels/attributes that the users see have unique names (eg [Ship Date].[Ship Month], [Order Date].[Order Month])