Derived attributes in a dimension

SQL 2005 SSAS - is it possible to create a derived attribute in a Dimension or does it have to be done in the underlying table.

Specifically, I have a geographic location Dimension where the top level is "Area" (USGulf, USWestCoast, Caribbean etc). Now there is one part of the company that likes to group these together into Zones. I can see how to do this in SQL in the underlying table or in reports in SSRS, but ideally would like to do this in the cube.

Thanks

You could add a Named Calculation for Zone to the dimension table in the Data Source View. This would be a SQL expression - the underlying table structure wouldn't change.

Deployment worked when it shouldn't have done

I've just had a situation where, when trying to process a cube, it failed because one of my Script Commands referenced a non-existent dimension.

Now I have no problem with that - its easily fixed. My problem is that the cube is obviously faulty so why was I allowed to deploy it? The deployment should surely have failed shouldn't it?

Is this a deployment bug?

Here is the script command:

Scope(Leaves([Currency]), EXCEPT( [Currency Conversion].[Currency Conversion].MEMBERS, {[Currency Conversion].[Currency Conversion].&[<None>]} ))

-Jamie

P.S. Does anyone from the SSAS team in Redmond frequent this forum?

The server has a very flexible, forgiving, and complex system for attempting to resolve name ambiguities. So if you simply provide the text [Currencty Conversion] it may be a dimension name or a hierarchy name or a level name or even a member name. (Yes, if the server can uniquely resolve this to a member name it will although a dimension prefix is a good idea at a minimum.) As a result, until the server has read the data during processing it does not fully bind the names and thus does not know for sure that this is a dimension name that cannot be found.

Yes, people such as myself from the SSAS team in Redmond do frequent this forum.

|||

Good to know - on both counts. Thank you Matt.

-Jamie

sql

Deploying cube changes to multiple sites

Hi,

How does one deploy cube changes (say adding a new field into a dimension) into multiple customer databases without overwriting their Kpis?

We have a SSAS application which will snap into our existing application and allow customers to define Environmental, Health & Safety Kpis. Each customer will have different Kpis, so how do we deploy incremental updates, without overwriting those Kpis?

I know we can download a customers SSAS database into a new VS project, and copy the Kpis into an XMLA deployment script, but that is a lot of work for each customer.

What I am hoping to find is something like a SQL Script that makes the schema changes, but does not touch the data (Kpis etc.). Of course, my SQL knowledge has only got me into trouble in SSAS, so who knows.

Thanks,

--Phil

Probably the best approach would be to write a script which uses DISCOVER_XML_METADATA to retrieve the cube's metadata, and then changes the XML my injecting your KPIs and then sending this XMLA script inside <Alter> command.

Deploy Cubes

Hi

I want to deploy a complete analysis db (with the cubes and dimension) to another server. As I have learned it is possible to use backup and restore. But with this method also the data is transferred which is quite a headache when the cubes are already processed and contain a lot of data. Is it possible to just export the definition. Btw: It's analysis services 2000.

Thanks in advance

Thomas

Nobody?

Your answer would be greatly appreciated.

Thomas

|||

I think copy-paste in Analysis Manager would do the trick.

B.

|||

Hi Brian

Thanks for your reply. I will try it.

But one question to that. Copy paste method is not very friendly in a bigger scenario, where one developer creates the cubes and some companies on other location will use it. Also backup is not possible this way.

What are the methods to achieve this?

Regards

Thomas

|||

The options with AS 2000 are pretty weak. You can either backup & restore or copy-paste & reprocess. One variation is to backup an empty database, restore it, then reprocess.

In 2005, you can migrate changes through scripts. Some change will require reprocessing, but many do not.

B.

|||

Ok, thanks a lot.

Regards

Thomas

DependsOnDimension: How It Works?

Hi,

I've read about it that..

Specifies the identifier for any other dimension on which the current dimension depends. If set, this property indicates that the contents of the current dimension are dependent on the contents of the other dimension.

I tried it one of my Dimension, but when I browse it, then it seams that there is no effects Please any one explain how it can be seen effective.

Basically I have Dealer Dimension and Dealers belongs to Customers. So I set DependsOnDimension to Customer in Dealer Dimension. But I do not know how to see it effectively.

Thanks in advance

Bhudev

Show us an image of your datasource view...

More details friend!

Regards!

|||

DependsOnDimension is really just a hint for use when designing aggregations using the Aggregation Design Wizard or the Usage Based Optimization Wizard. Normally the aggregation design algorithm estimates the size of aggregations assuming dimesions are independent, but this property lets it know that one dimension is strongly determined by another dimension. This should not affect your browsing experience in any way.

In Analysis Services 2000, DependsOnDimension was also used as part of the definition of virtual dimensions as a way of specifying the source for virtual dimension's levels.

DependsOnDimension: How It Works?

Hi,

I've read about it that..

Specifies the identifier for any other dimension on which the current dimension depends. If set, this property indicates that the contents of the current dimension are dependent on the contents of the other dimension.

I tried it one of my Dimension, but when I browse it, then it seams that there is no effects Please any one explain how it can be seen effective.

Basically I have Dealer Dimension and Dealers belongs to Customers. So I set DependsOnDimension to Customer in Dealer Dimension. But I do not know how to see it effectively.

Thanks in advance

Bhudev

Show us an image of your datasource view...

More details friend!

Regards!

|||

DependsOnDimension is really just a hint for use when designing aggregations using the Aggregation Design Wizard or the Usage Based Optimization Wizard. Normally the aggregation design algorithm estimates the size of aggregations assuming dimesions are independent, but this property lets it know that one dimension is strongly determined by another dimension. This should not affect your browsing experience in any way.

In Analysis Services 2000, DependsOnDimension was also used as part of the definition of virtual dimensions as a way of specifying the source for virtual dimension's levels.

DependsOnDimension: How It Works?

Hi,

I've read about it that..

Specifies the identifier for any other dimension on which the current dimension depends. If set, this property indicates that the contents of the current dimension are dependent on the contents of the other dimension.

I tried it one of my Dimension, but when I browse it, then it seams that there is no effects Please any one explain how it can be seen effective.

Basically I have Dealer Dimension and Dealers belongs to Customers. So I set DependsOnDimension to Customer in Dealer Dimension. But I do not know how to see it effectively.

Thanks in advance

Bhudev

Show us an image of your datasource view...

More details friend!

Regards!

|||

DependsOnDimension is really just a hint for use when designing aggregations using the Aggregation Design Wizard or the Usage Based Optimization Wizard. Normally the aggregation design algorithm estimates the size of aggregations assuming dimesions are independent, but this property lets it know that one dimension is strongly determined by another dimension. This should not affect your browsing experience in any way.

In Analysis Services 2000, DependsOnDimension was also used as part of the definition of virtual dimensions as a way of specifying the source for virtual dimension's levels.

denying read rights to a dimension

Hi,

When you give read rights to a cube then the dimension gets read rights by default. Is there a way by which you can grant read rights to certain dimensions of that cube and deny the read rights to certain dimensions of that cube?

thanx.

In AS 2005, just create a new Role, and select what the users belonging to that role can see in the Dimension Data tab. If you select the "Deselect All Members" option, then the users of the role won't be able to see any data of that dimension.

In AS 2000, you have to add the dimension to the restricted dimension list when editing the proper role.

Hope this help,

Santi

|||

Thanx. But then in that case I can still see the dimension. However if I dont want to show any of the attributes of a dimension, is it possible to hide the dimension completely..

Denying access to a whole dimension

In AS2K dimension security I could deny access to a whole dimension using the 'Fully Restricted' setting on a role. In AS2005 I can't see how to do the same thing - the only thing I can do is to deny access to every member on every attribute but even then I still see the dimension name in the metadata. BOL suggests it is possible but its descriptions of the UI must have been written before RTM and don't bear much resemblence to the current situation; I've also had a go at setting some of the properties on the permissions object in the XMLA definition of my cube but they don't work either. Is this feature still available? If not, can we have it back?

Chris

Hi Chris.

My recent experience is we had to go "outside" the native role based functionality to achieve the functionality you defined. Our work is probably a demonstration of my ignorance on how to get the same funtionality we had in AS2K expressed in AS2005. When I say go "outside" I mean we used table based security which married the user with the branch of the organization hierarchy they are allowed to see - and ONLY that branch. The user does not see any other members from the organizational hierarchy. I think you're familiar with the solution. If not, let me know and I'll email you the whitepaper.

If Chris is correct - where we can't get the same functionality as we had in AS2K, then I second his suggestion to "have it back."

PGoldy

|||

Hello Paul. Have you done this in the old way of dynamic security, by a security fact table or a stored procedure. If there is any new written on this subject I would like to know.

Regards

Thomas Ivarsson

|||

Hi Thomas. The security technique was teh "old" way of dynamic security. Nothing new here, except that it works in AS2005. Nothing new written here.

Best Regards,

PGoldy

|||

Hi,

Did you try to design a perspective for the role you want to restrict acces ?

Regards

|||

Thanks for the feedback, everyone. It does indeed look like a forgotten feature - I'll post it in Connect. Alex - yes, my workaround involves using dimension security to restrict all the members on my dimension then use a perspective to hide the dimension itself.

Chris

|||

Thanks Chris and everyone for rasing this issue.

Please go ahead and file the connect case for it.

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

|||Hi,
I was trying to point the same thing out here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=785302&SiteID=1

Can I get somehow access to Connect web site (which afaik was closed for new-comming users), I have some other issues as well, which would be nice to handle in next update. I understand that this forum is not intended to collect any errors and that nobody will enter it into Connect programme.

Thanks,
Radim

|||

Hi Radim,

I have no idea whether Connect is closed to new users - it seems a bit strange if it was. It is a bit of a nightmare to navigate through though.

Anyway, I've submitted this issue here:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=233410

Please vote on it!

Chris

|||

Absolutly!

You've had my vote !

Best, Jocke

|||

Hi PGoldy,

Can you email me the whitepaper on this? i have the same problem right now on security (how i can restrict the users with the amount of data they can see).

thanks,

cherriesh

|||

Hi Cherriesh. Better than a document, here's an on-line link to the information your looking for. The author is Richard Tkachuk who is part of the Analysis Services team at MS - very reliable source. Good luck.

PaulG

http://www.sqlserveranalysisservices.com/OLAPPapers/UsingUserNametoControlDataAccessandDefaultMemberinSSAS.htm

Denying access to a whole dimension

In AS2K dimension security I could deny access to a whole dimension using the 'Fully Restricted' setting on a role. In AS2005 I can't see how to do the same thing - the only thing I can do is to deny access to every member on every attribute but even then I still see the dimension name in the metadata. BOL suggests it is possible but its descriptions of the UI must have been written before RTM and don't bear much resemblence to the current situation; I've also had a go at setting some of the properties on the permissions object in the XMLA definition of my cube but they don't work either. Is this feature still available? If not, can we have it back?

Chris

Hi Chris.

My recent experience is we had to go "outside" the native role based functionality to achieve the functionality you defined. Our work is probably a demonstration of my ignorance on how to get the same funtionality we had in AS2K expressed in AS2005. When I say go "outside" I mean we used table based security which married the user with the branch of the organization hierarchy they are allowed to see - and ONLY that branch. The user does not see any other members from the organizational hierarchy. I think you're familiar with the solution. If not, let me know and I'll email you the whitepaper.

If Chris is correct - where we can't get the same functionality as we had in AS2K, then I second his suggestion to "have it back."

PGoldy

|||

Hello Paul. Have you done this in the old way of dynamic security, by a security fact table or a stored procedure. If there is any new written on this subject I would like to know.

Regards

Thomas Ivarsson

|||

Hi Thomas. The security technique was teh "old" way of dynamic security. Nothing new here, except that it works in AS2005. Nothing new written here.

Best Regards,

PGoldy

|||

Hi,

Did you try to design a perspective for the role you want to restrict acces ?

Regards

|||

Thanks for the feedback, everyone. It does indeed look like a forgotten feature - I'll post it in Connect. Alex - yes, my workaround involves using dimension security to restrict all the members on my dimension then use a perspective to hide the dimension itself.

Chris

|||

Thanks Chris and everyone for rasing this issue.

Please go ahead and file the connect case for it.

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

|||Hi,
I was trying to point the same thing out here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=785302&SiteID=1

Can I get somehow access to Connect web site (which afaik was closed for new-comming users), I have some other issues as well, which would be nice to handle in next update. I understand that this forum is not intended to collect any errors and that nobody will enter it into Connect programme.

Thanks,
Radim|||

Hi Radim,

I have no idea whether Connect is closed to new users - it seems a bit strange if it was. It is a bit of a nightmare to navigate through though.

Anyway, I've submitted this issue here:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=233410

Please vote on it!

Chris

|||

Absolutly!

You've had my vote !

Best, Jocke

|||

Hi PGoldy,

Can you email me the whitepaper on this? i have the same problem right now on security (how i can restrict the users with the amount of data they can see).

thanks,

cherriesh

|||

Hi Cherriesh. Better than a document, here's an on-line link to the information your looking for. The author is Richard Tkachuk who is part of the Analysis Services team at MS - very reliable source. Good luck.

PaulG

http://www.sqlserveranalysisservices.com/OLAPPapers/UsingUserNametoControlDataAccessandDefaultMemberinSSAS.htm

Denying access to a whole dimension

In AS2K dimension security I could deny access to a whole dimension using the 'Fully Restricted' setting on a role. In AS2005 I can't see how to do the same thing - the only thing I can do is to deny access to every member on every attribute but even then I still see the dimension name in the metadata. BOL suggests it is possible but its descriptions of the UI must have been written before RTM and don't bear much resemblence to the current situation; I've also had a go at setting some of the properties on the permissions object in the XMLA definition of my cube but they don't work either. Is this feature still available? If not, can we have it back?

Chris

Hi Chris.

My recent experience is we had to go "outside" the native role based functionality to achieve the functionality you defined. Our work is probably a demonstration of my ignorance on how to get the same funtionality we had in AS2K expressed in AS2005. When I say go "outside" I mean we used table based security which married the user with the branch of the organization hierarchy they are allowed to see - and ONLY that branch. The user does not see any other members from the organizational hierarchy. I think you're familiar with the solution. If not, let me know and I'll email you the whitepaper.

If Chris is correct - where we can't get the same functionality as we had in AS2K, then I second his suggestion to "have it back."

PGoldy

|||

Hello Paul. Have you done this in the old way of dynamic security, by a security fact table or a stored procedure. If there is any new written on this subject I would like to know.

Regards

Thomas Ivarsson

|||

Hi Thomas. The security technique was teh "old" way of dynamic security. Nothing new here, except that it works in AS2005. Nothing new written here.

Best Regards,

PGoldy

|||

Hi,

Did you try to design a perspective for the role you want to restrict acces ?

Regards

|||

Thanks for the feedback, everyone. It does indeed look like a forgotten feature - I'll post it in Connect. Alex - yes, my workaround involves using dimension security to restrict all the members on my dimension then use a perspective to hide the dimension itself.

Chris

|||

Thanks Chris and everyone for rasing this issue.

Please go ahead and file the connect case for it.

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

|||Hi,
I was trying to point the same thing out here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=785302&SiteID=1

Can I get somehow access to Connect web site (which afaik was closed for new-comming users), I have some other issues as well, which would be nice to handle in next update. I understand that this forum is not intended to collect any errors and that nobody will enter it into Connect programme.

Thanks,
Radim|||

Hi Radim,

I have no idea whether Connect is closed to new users - it seems a bit strange if it was. It is a bit of a nightmare to navigate through though.

Anyway, I've submitted this issue here:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=233410

Please vote on it!

Chris

|||

Absolutly!

You've had my vote !

Best, Jocke

|||

Hi PGoldy,

Can you email me the whitepaper on this? i have the same problem right now on security (how i can restrict the users with the amount of data they can see).

thanks,

cherriesh

|||

Hi Cherriesh. Better than a document, here's an on-line link to the information your looking for. The author is Richard Tkachuk who is part of the Analysis Services team at MS - very reliable source. Good luck.

PaulG

http://www.sqlserveranalysisservices.com/OLAPPapers/UsingUserNametoControlDataAccessandDefaultMemberinSSAS.htm