SSAS : How to automate cube processing and user role attribution?

I’ve been having to give a role to new users, problem I have to add them to 2 different cubes in 6 different environments, which is 12 times adding the user and processing the rights table, which amounts to around an hour on my company’s rather weak laptop, for EVERY new user.

Is there any way to juste write some code with a list of users you wanna add to a list of cubes, and you’d just tell it to process the table after each addition ? It’d be a real life saver right now.

Answer

In SSIS, you can use the Analysis Services Execute DDL Task. This can take a TMSL script as input, which would look like below.

1) sequence – this command allows you to perform multiple operations

2) createOrReplace – this will refresh the role with the new list of members. Note that every existing member needs to be included in the role or they will be wiped out

3) refresh – processes the table

In ssis, you might create a connection to each environment and loop through a set of script files, so that you would not need to modify the package to add new members.

However, I would also suggest switching to an AD group instead of adding explicit users to the role. Then you would only need to refresh table.

{
    "sequence": {
        "operations": [{
                "createOrReplace": {
                    "object": {
                        "database": "<Your Database>",
                        "role": "<Your Role Name>"
                    },
                    "role": {
                        "name": "Reader",
                        "modelPermission": "read",
                        "members": [{
                                "memberName": "<Your Domain>\<User 1>",
                                "memberName": "<Your Domain>\<User 2>",
                                <All the users in the role...>
                            }
                        ]
                    }
                }
            }, {
                "refresh": {
                    "type": "full",
                    "objects": [{
                            "database": "<Your Database>",
                            "table": "<Your Table>"
                        }
                    ]
                }
            }
        ]
    }
}