Perspectives with ADOMD.NET and AMO

I am trying to filter our application's cube metadata list (measures, kpis, dimensions, etc.) by perspectives.

Unless I have totally missed something it appears that ADOMD.NET has 0 support for perspectives. You can not iterate measures, dimensions, etc. by perspective using ADOMD.NET. Can anyone verify this or correct me if I'm missing something?

While AMO can be used to iterate measures, dimensions, etc. by perspective, none of its PerspectiveDimension, CubeDimension, PerspectiveMeasure, etc. objects contain UniqueName fields. AMO does not seem to provide any UniqueName fields. Can anyone also verify this or correct me if I'm wrong?

I am currently having to utilize both ADOMD.NET and AMO in order to build the metadata list and support perspective filtering, and I keep thinking there has to be a better way! This is also requiring a LOT of For Looping in order to match up an object in ADOMD.NET to the corresponding object in AMO.

Thanks,
Terry

Perspectives are returned as regular cubes in ADOMD.NET. For browsing data, they are cubes, while from the metadata point of view they are separate entities, so they appear separately in AMO, but not ADOMD.NET.

UniqueName is indeed not exposed in AMO because it's not part of the objects definitions (aka metadata). For example, a DimensionAttribute in AMO doesn't have the UniqueName property, but when browsing the members of that attribute, each will have an UniqueName.

Adrian Dumitrascu

|||Hi Adrian,

Thank you for your help! I'm still a bit confused though. I can not find any property setting within ADOMD.NET to set the perspective that I want to restrict by. Your previous post led me to believe it might be setting on the AdomdConnection object and that thereafter everything would be restricted to that perspective; however, I have not been able to find that in the documentation. Would you please clarify for me how to restrict everything returned in ADOMD.NET to a given perspective?

Thank you,
Terry|||

If you want to define what measures, dimensions and/or attributes to include in a perspective, you need to use AMO. The Cube class in AMO has a collection of Perspectives.

After that, the perspective will appear as any other cube (in excel, or when browsing with ADOMD.NET or other browser), alongside the other cubes and perspectives.

If you want certain users to only see certain measures, dimensions or attributes, but not the rest of the cube, dimensions or attributes, then using a perspective is not the way to go. Perspectives are not a security feature, they are only used to customize the view of a cube. Instead you should define a Role for each category of users and then CubePermissions. That ensures that certain users only see the data they are allowed.

|||Thanks Adrian! I wasn't expecting the perspective to show up as their own cubes, so I didn't look in the ADOMD.NET cube collection.

For others that need to know this, you can iterate all the perspectives of a given cube by looking at adomdConnection.Cubes(i).Properties("BASE_CUBE_NAME") = "My Real Cube" and then use each of those Cube objects to find the perspective-specific measures, dimensions, etc.

Thanks again,
Terry

Permissions used to Connect to SSIS on remote machine

We are trying to take advantage of the new Security Context for SSIS but users are unable to connect remotely to the SSIS Service unless they have been added to the Administrators Users Group on the server. I have tried adding them to Guests, Power Users, Remote Desktop Users, Users and SQLServer2005DTSUser$machine but the user is unable to connect.

The message received when the user is unable to connect is:

Cannot connect to 192.x.x.x
Additional Information
-> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
-> Connect to SSIS Service on machine "192.x.x.x" failed:
Access is denied

What is the list of permissions and privileges that a user that does not belong to the server's Administrator group and have sysadmin server role, must have to allow them to create and run a package through SSIS.

i have the same issue. i am able to connect to the local integration services. but when i try to access another servers integration services Access is denied. what permissions are needed?

aaks

|||Try these instructions: http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx|||

You have to perform these steps in addition to the one's specified above.

http://mohansmindstorms.spaces.live.com/Blog/cns!69AE1BEA50F1D0E7!213.entry

Permissions used to Connect to SSIS on remote machine

We are trying to take advantage of the new Security Context for SSIS but users are unable to connect remotely to the SSIS Service unless they have been added to the Administrators Users Group on the server. I have tried adding them to Guests, Power Users, Remote Desktop Users, Users and SQLServer2005DTSUser$machine but the user is unable to connect.

The message received when the user is unable to connect is:

Cannot connect to 192.x.x.x
Additional Information
-> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
-> Connect to SSIS Service on machine "192.x.x.x" failed:
Access is denied

What is the list of permissions and privileges that a user that does not belong to the server's Administrator group and have sysadmin server role, must have to allow them to create and run a package through SSIS.

i have the same issue. i am able to connect to the local integration services. but when i try to access another servers integration services Access is denied. what permissions are needed?

aaks

|||Try these instructions: http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx|||

You have to perform these steps in addition to the one's specified above.

http://mohansmindstorms.spaces.live.com/Blog/cns!69AE1BEA50F1D0E7!213.entry

Permissions

The Report Manager will not show on a client machine unless I logon with the local machine administrator account of the report server. The logon credentials that I am using, on the client, is in the administrators group of the local machine of the server.

I am only able to author and view reports when physically logged on to the machine with the report server. The same credentials that were used on client machine are used when logging on to the server machine. I have granted as much permission to the user account as possible unless I am missing something in the report server.

It seems like a related permission issue happens when trying to run Report Builder. I get a better error message when running Report Builder(See Below). To be sure I have given explicit server administrator to the client logon. Furthermore I have given content manager rights to the client logon for a model and a report.

The client machine has framework 2.0 installed.

Here is the error produced when attempting to run the Report Builder.

PLATFORM VERSION INFO
Windows : 5.1.2600.131072 (Win32NT)
Common Language Runtime : 2.0.50727.42
System.Deployment.dll : 2.0.50727.42 (RTM.050727-4200)
mscorwks.dll : 2.0.50727.42 (RTM.050727-4200)
dfdll.dll : 2.0.50727.42 (RTM.050727-4200)
dfshim.dll : 2.0.50727.42 (RTM.050727-4200)

SOURCES
Deployment url : http://{Server Name:port number}/ReportServer/ReportBuilder/ReportBuilder.application

ERROR SUMMARY
Below is a summary of the errors, details of these errors are listed later in the log.
* Activation of http://{Server Name:port number}/ReportServer/ReportBuilder/ReportBuilder.application resulted in exception. Following failure messages were detected:
+ Downloading http://{Server Name:port number}/ReportServer/ReportBuilder/ReportBuilder.application did not succeed.
+ The remote server returned an error: (401) Unauthorized.
+ The target principal name is incorrect

COMPONENT STORE TRANSACTION FAILURE SUMMARY
No transaction error was detected.

WARNINGS
There were no warnings during this operation.

OPERATION PROGRESS STATUS
* [1/23/2006 1:55:22 PM] : Activation of http://{Server Name:port number}/ReportServer/ReportBuilder/ReportBuilder.application has started.

ERROR DETAILS
Following errors were detected during this operation.
* [1/23/2006 1:55:22 PM] System.Deployment.Application.DeploymentDownloadException (Unknown subtype)
- Downloading http://{Server Name:port number}/ReportServer/ReportBuilder/ReportBuilder.application did not succeed.
- Source: System.Deployment
- Stack trace:
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
at System.Deployment.Application.SystemNetDownloader.DownloadAllFiles()
at System.Deployment.Application.FileDownloader.Download(SubscriptionState subState)
at System.Deployment.Application.DownloadManager.DownloadManifestAsRawFile(Uri& sourceUri, String targetPath, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation)
at System.Deployment.Application.DownloadManager.DownloadDeploymentManifestDirectBypass(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, SubscriptionState& subState, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation)
at System.Deployment.Application.DownloadManager.DownloadDeploymentManifestBypass(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, SubscriptionState& subState, IDownloadNotification notification, DownloadOptions options)
at System.Deployment.Application.ApplicationActivator.PerformDeploymentActivation(Uri activationUri, Boolean isShortcut)
at System.Deployment.Application.ApplicationActivator.ActivateDeploymentWorker(Object state)
Inner Exception
System.Net.WebException
- The remote server returned an error: (401) Unauthorized.
- Source: System
- Stack trace:
at System.Net.HttpWebRequest.GetResponse()
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
Inner Exception
System.ComponentModel.Win32Exception
- The target principal name is incorrect
- Source: System
- Stack trace:
at System.Net.NTAuthentication.GetOutgoingBlob(Byte[] incomingBlob, Boolean throwOnError, SecurityStatus& statusCode)
at System.Net.NTAuthentication.GetOutgoingBlob(String incomingBlob)
at System.Net.NegotiateClient.DoAuthenticate(String challenge, WebRequest webRequest, ICredentials credentials, Boolean preAuthenticate)
at System.Net.NegotiateClient.Authenticate(String challenge, WebRequest webRequest, ICredentials credentials)
at System.Net.AuthenticationManager.Authenticate(String challenge, WebRequest request, ICredentials credentials)
at System.Net.AuthenticationState.AttemptAuthenticate(HttpWebRequest httpWebRequest, ICredentials authInfo)
at System.Net.HttpWebRequest.CheckResubmitForAuth()
at System.Net.HttpWebRequest.CheckResubmit(Exception& e)

COMPONENT STORE TRANSACTION DETAILS
No transaction information is available.

reading the other posts about permissions leads me to believe that the permissions problem has to do with the number of hops from the client to the server processes. I have not fixed the problem but I am sure this is what it is.

I am still not sure about one thing. Since windows auth. is used, is it possible that the web service and the front end are picking up on the credentials when running it from the server machine? Alternatively, when running it from the client, kerberos auth. must be used to pass the credentials from the front end to the web service.

|||I am getting the same error trying to donwnload the report builder to any client. Works fine with a server\local account but not with a network account. I added myself to the local Server\SQLServer2005SQLBrowserUser & Server\SQLServer2005SQLReportServerUser groups with no avail.

Permissions

The Report Manager will not show on a client machine unless I logon with the local machine administrator account of the report server. The logon credentials that I am using, on the client, is in the administrators group of the local machine of the server.

I am only able to author and view reports when physically logged on to the machine with the report server. The same credentials that were used on client machine are used when logging on to the server machine. I have granted as much permission to the user account as possible unless I am missing something in the report server.

It seems like a related permission issue happens when trying to run Report Builder. I get a better error message when running Report Builder(See Below). To be sure I have given explicit server administrator to the client logon. Furthermore I have given content manager rights to the client logon for a model and a report.

The client machine has framework 2.0 installed.

Here is the error produced when attempting to run the Report Builder.

PLATFORM VERSION INFO
Windows : 5.1.2600.131072 (Win32NT)
Common Language Runtime : 2.0.50727.42
System.Deployment.dll : 2.0.50727.42 (RTM.050727-4200)
mscorwks.dll : 2.0.50727.42 (RTM.050727-4200)
dfdll.dll : 2.0.50727.42 (RTM.050727-4200)
dfshim.dll : 2.0.50727.42 (RTM.050727-4200)

SOURCES
Deployment url : http://{Server Name:port number}/ReportServer/ReportBuilder/ReportBuilder.application

ERROR SUMMARY
Below is a summary of the errors, details of these errors are listed later in the log.
* Activation of http://{Server Name:port number}/ReportServer/ReportBuilder/ReportBuilder.application resulted in exception. Following failure messages were detected:
+ Downloading http://{Server Name:port number}/ReportServer/ReportBuilder/ReportBuilder.application did not succeed.
+ The remote server returned an error: (401) Unauthorized.
+ The target principal name is incorrect

COMPONENT STORE TRANSACTION FAILURE SUMMARY
No transaction error was detected.

WARNINGS
There were no warnings during this operation.

OPERATION PROGRESS STATUS
* [1/23/2006 1:55:22 PM] : Activation of http://{Server Name:port number}/ReportServer/ReportBuilder/ReportBuilder.application has started.

ERROR DETAILS
Following errors were detected during this operation.
* [1/23/2006 1:55:22 PM] System.Deployment.Application.DeploymentDownloadException (Unknown subtype)
- Downloading http://{Server Name:port number}/ReportServer/ReportBuilder/ReportBuilder.application did not succeed.
- Source: System.Deployment
- Stack trace:
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
at System.Deployment.Application.SystemNetDownloader.DownloadAllFiles()
at System.Deployment.Application.FileDownloader.Download(SubscriptionState subState)
at System.Deployment.Application.DownloadManager.DownloadManifestAsRawFile(Uri& sourceUri, String targetPath, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation)
at System.Deployment.Application.DownloadManager.DownloadDeploymentManifestDirectBypass(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, SubscriptionState& subState, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation)
at System.Deployment.Application.DownloadManager.DownloadDeploymentManifestBypass(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, SubscriptionState& subState, IDownloadNotification notification, DownloadOptions options)
at System.Deployment.Application.ApplicationActivator.PerformDeploymentActivation(Uri activationUri, Boolean isShortcut)
at System.Deployment.Application.ApplicationActivator.ActivateDeploymentWorker(Object state)
Inner Exception
System.Net.WebException
- The remote server returned an error: (401) Unauthorized.
- Source: System
- Stack trace:
at System.Net.HttpWebRequest.GetResponse()
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
Inner Exception
System.ComponentModel.Win32Exception
- The target principal name is incorrect
- Source: System
- Stack trace:
at System.Net.NTAuthentication.GetOutgoingBlob(Byte[] incomingBlob, Boolean throwOnError, SecurityStatus& statusCode)
at System.Net.NTAuthentication.GetOutgoingBlob(String incomingBlob)
at System.Net.NegotiateClient.DoAuthenticate(String challenge, WebRequest webRequest, ICredentials credentials, Boolean preAuthenticate)
at System.Net.NegotiateClient.Authenticate(String challenge, WebRequest webRequest, ICredentials credentials)
at System.Net.AuthenticationManager.Authenticate(String challenge, WebRequest request, ICredentials credentials)
at System.Net.AuthenticationState.AttemptAuthenticate(HttpWebRequest httpWebRequest, ICredentials authInfo)
at System.Net.HttpWebRequest.CheckResubmitForAuth()
at System.Net.HttpWebRequest.CheckResubmit(Exception& e)

COMPONENT STORE TRANSACTION DETAILS
No transaction information is available.

reading the other posts about permissions leads me to believe that the permissions problem has to do with the number of hops from the client to the server processes. I have not fixed the problem but I am sure this is what it is.

I am still not sure about one thing. Since windows auth. is used, is it possible that the web service and the front end are picking up on the credentials when running it from the server machine? Alternatively, when running it from the client, kerberos auth. must be used to pass the credentials from the front end to the web service.

|||I am getting the same error trying to donwnload the report builder to any client. Works fine with a server\local account but not with a network account. I added myself to the local Server\SQLServer2005SQLBrowserUser & Server\SQLServer2005SQLReportServerUser groups with no avail.