0

I have the following XMLA Script to add domain Users to a specific SSAS membership role. It works fine, but I need to work that in a loop. A table with all the Users will be filled from Active Directory. The question is how can I generate the tab <Member> </Member> for every User existent in my SQL Table or SSAS Dimension ? Or how can I parse every username from the table as a parameter in this XMLA-Tab?

enter image description here

Pirvu Georgian
  • 657
  • 1
  • 12
  • 37

1 Answers1

1

I created a T-SQL script here which determines the current users and adds one over a linked server to SSAS. It shouldn't be hard to join to your table and use that as the source of members. You may not need ASSP if you don't care what members the role has currently.

Here are steps on setting up the linked server: http://www.mssqltips.com/sqlservertip/2790/dynamic-xmla-using-tsql-for-sql-server-analysis-services/

If you don't want to setup a linked server and install ASSP to do the above you could write some C# code that uses the AMO library to maintain the members.

Community
  • 1
  • 1
GregGalloway
  • 11,355
  • 3
  • 16
  • 47
  • I created a linked server called SSAS and a Stored Procedure for xaml script to parse the Username. I tried to use your script wiht @members to append the next member to the old one, but when I execute `EXECUTE('CALL ASSP.DiscoverXmlMetadata("\MISystem\Roles\PowerUsers\Members\Member")') at [SSAS]` , where MISystem is the SSAS-DB and PowerUsers is the rolename, I receive the following message _Cannot process the object "STREAM". The OLE DB provider "STREAM" for linked server "(null)" indicates that either the object has no columns_ . I have already installed ASSP. – Pirvu Georgian Nov 23 '15 at 08:28
  • I checked where the problem could be and it seems to me as a permission issue. My user is already admin. The SSAS linked server was created to accept RPC and the MSOLAP Provider has enabled _Allow inprocess_ option – Pirvu Georgian Nov 23 '15 at 08:37
  • I finally gerated one xaml script for all users once and parsed the variable in your stored procedure. It works fine. By the way, Nice blog @GregGalloway – Pirvu Georgian Nov 23 '15 at 12:35