Interface for Analysis Services 2005

Dear friends,

I have a problem…

1. How can I show the data from Analysis Services 2005? In ASP.Net 2.0 or Sharepoint 2007?
2. I have users and groups in tables from one database, how can control it in the interface in ASP.NET 2.0 or Sharepoint? (I’m cant use the asp.net 2.0 security controls) It’s easy to allow or deny users or groups to view some items as we can do in asp.net 2.0 controls?

Thanks!!

Pedro,

I do not know about ASP.NET, but you can build reports using Excel 2007 and use Sharepoint as report repository.

High level design:

- In Sharepoint you create connection library(s) that point to appropriate cubes/perspectives in SSAS 2005

- In Sharepoint you create report library(s)

- You create reports using connections from Sharepoint connection library and save them in Sharepoint report library

- You can use SharePoint Excel services for users to see reports with some limited interactivity. Users can use these reports just using browser, no need for Excel. And reports/charts look very similar to what you would see in Excel.

In Sharepoint you would use Integrated security to allow/deny access to reports. But of course you would have to setup proper role security in SSAS 2005 first.

Vidas Matelis

|||

Thanks Vidas for your help.

Note, that I'm using excel 2003, and I will allow users to use the report builder... and I'm very confused aboout the previligies for each user...

I have the user and groups in a OLTP database...

regards

|||

In SSAS 2005 you have security roles where you assing permissions to windows users group. So there you will define if specific windows user/group can access that cube.

Another level of security will be for reports. In sharepoint you can put reports to different report libraries or different report folders and assign if user/group can access that library or folder.

Keep in mind that if user have access to cube, he/she can just start Excel and connect to SSAS to query that cube. Report permissions should go hand in hand with cube/dimension permissions.

If you have excel 2003, I do not believe you will be able to use Excel Services in SharePoint 2007.

As you already know, Excel 2003 does not work very well with SSAS 2005. It is more in presentation, than calculation. You see all attributes as dimensions, and measures are not groupped by measure group and/or folder. So for bigger databases this is big problem.

Vidas Matelis

|||

Thanks for your post...

But how can I control the access to the cubes or reports, if I have the users and groups inside tables and not in the active directory?

Thanks

|||

Pedro,

Analysis Services can use just integrated security. So you cannot change SSAS security based on users saved in SQL Server table.

Vidas Matelis

|||

> Analysis Services can use just integrated security. So you cannot change SSAS security based on users saved in SQL Server table.

Unless you control middle tier, which seems to be a scenario here. It would involve some ASP.NET coding, but it is possible to use users from SQL table. For more information, please read about Roles property and for more dynamic scenarios - CustomData property and MDX function.

|||

Pedro, sorry I misguided you. I based my answer on BOL information, but Mosha pointed that there are ways to do it.

Mosha,

Any published papers on this? Any examples on how this could be done?

I googled it, but cannot find much more detail information.

Thank you,

Vidas Matelis

|||

yes... mosha, do you have some example? or links about the subject?

Thanks both!

|||

MOsha,

Supose I use integrated security, where I can have the groups and roles? The groups are in Active Directory as the users?

Regards!

|||In the middle tier (usually ASP.NET app), after you authenticated the user, you can look up in the SQL table or in AD or somewhere else what are the roles he should belong to. Then you create ADOMD.NET connection for that user passing "Roles=Role1,Role2,Role3" connection string parameter. If static role assignment doesn't work for you, you can pass user id through CustomData property, i.e. "CustomData=appuser1", and then inside security definitions you can use CustomData() MDX function which will resolve to the value passed in the property.|||

Mosha,

Could you please confirm if my understanding for Role property is right:

- For this to work middle tier should have full (or at least some) access to SSAS database.

- User does not need to have access to SSAS database, as it is queried by middle tier.

- Including role parameter in connection string will further limit middle tier access to SSAS based on roles specified.

- Will there be any penalty for performance using this? I know that years back when connection string to relational DB did not matched exactly, then you could not reuse cache betten connection.

Using CustomData() function, is my understanding right:

- I would disable access to direct measures to users.

- I would create calculated measures and use CustomData() function to limit what values are available.

- With this approach I cannot hide dimensions, hierarchies, cubes, just calculated measure values.

Did I understand right?

Thank you,

Vidas Matelis

|||

For Roles property your understanding is correct.

> Will there be any penalty for performance using this? I know that years back when connection string to relational DB did not matched exactly, then you could not reuse cache betten connection.

You won't be able to reuse connections with different Roles set on them. The FE caches cannot be reused as well (SE caches can be reused sometimes). For more information check out the SQL Server 2005 Analysis Services book, page 511 - it has good explanation of this subject.

> Using CustomData() function, is my understanding right:

- I would disable access to direct measures to users.

- I would create calculated measures and use CustomData() function to limit what values are available.

- With this approach I cannot hide dimensions, hierarchies, cubes, just calculated measure values.

You misunderstood how CustomData can be used for security. Just think about it as a replacement for the UserName() function in dynamic security when your authentication is not Windows Integrated but a custom one.

|||

Mosha,

Thank you! This was very helpful.

Vidas Matelis