11 Replies Latest reply on Mar 5, 2018 1:49 PM by Reza Rahmati

    Analysis Services Impersonation

    Vladimir Patsionov

      Hello everyone! I hope most of you know that there is a SQL impersonation in Tableau when using SQL Server Live Connection. I wonder if there's the same thing for Analysis Services(SSAS) connection? We are using OLAP Cubes as data sources in Tableau and all connections are done with Tableau server account. But now we need to use some kind of impersonation mode with OLAP Cubes to provide each user account is being used when connecting to cubes. Is there a way to implement this? Thanks in advance.

        • 1. Re: Analysis Services Impersonation
          Russell Christopher

          Hi Vladimir!

           

          Unfortunately, we don't support impersonation via an SSAS connection. I suspect you want to lean on SSAS's ability to secure dimension members by user or role?

           

          Here's how others work around this problem:

           

           

          The drawback here is that you'll essentially need to re-secure the SSAS dimension members using Tableau's user interface vs. taking advantage of what you already have done in the cube itself. More work.

           

          Some really advanced customers have also added a new dimension to the cube to do nothing else but help drive security. The dimension defines "which user can see what", and drives how our user filtering functions. It's sort of similar to one of the examples in the article I pointed you to -- except you do it in a cube vs. an RDBMS.

           

          Hope this helps?

          1 of 1 people found this helpful
          • 2. Re: Analysis Services Impersonation
            Vladimir Patsionov

            Russell, thank you very much for your answer. But could you tell me more about your advanced customers and their approach to solve this problem. Because, as it seems to me, even if I have dimension in a cube with all security rules for users I still have to send current user name ( USERNAME())  to cube. But when we make calculated fields in Tableau with a cube as a data source we can only use measures fields but not dimension fields. If I make calculated member for the cube I cannot use Tableau functions such as USERNAME() .

            • 3. Re: Analysis Services Impersonation
              Russell Christopher

              Hi Vladamir - you read the username attribute values from the dimension, but the comparison of "user logged into Tableau" against that username attribute happens inside a Tableau calculation.

               

              Consider:

               

              // Does the user logged into Tableau ( UserName() ) equal the value coming out of the [User Name] dimension in my SSAS lookup?

               

              Username() = [User Name]

              • 4. Re: Analysis Services Impersonation
                Russell Christopher

                Here's a full break down from an old sample I have:

                 

                • I built a simple cube against Microsoft’s sample Contoso Retail DW – It contains 18K Customers, hundreds of products, and 3.4M rows in the Fact Table
                • I “hacked in” two new tables – one which maps CustomerKeys to Users, and a many-to-many junction table which maps Customers (“Users”) to stores. This is my “Security Table” in which we express which stores a user has access to:


                MSAS_Cube.png

                • I then built a report which shows Sales by Product Category  / SubCategory
                • I made a copy of the report, added [User Dimension].[User Name] into Level of Detail, and then inserted a conditional filter on the field:


                User_Filter.png

                • I then removed [User Dimension].[User Name] from Level of Detail, although leaving it there didn’t seem to impact perf one way or the other.

                 

                ...I also recorded a video of this in action. Attached.

                1 of 1 people found this helpful
                • 5. Re: Analysis Services Impersonation
                  Vladimir Patsionov

                  Russel, thank you very much. But if you don't mind I would like to ask a few more questions. I don't have a lot of experience in making factless tables and that's what I encountered. I have made addittional tables according to your pictures , but in our DWH we have all key fields in GUID type. And moreover for users we don't have any  key columns yet, so I used UserLogin as a key. And of course when I tried to process my cube , error appeared saying that fields in measure table are not number types. So is it necessary to use only integer type field in a factless fact table? Or maybe there's a way to use other types?

                   

                  Then I decided to add a fictional measure which won't mean anything - just to let cube to be processed. Everything worked OK but when I opened Tableau New UserDimension was grey and there was an announcement that few measures fields are not compatible with UserDimension. What does this mean?

                  • 6. Re: Analysis Services Impersonation
                    Russell Christopher

                    Vladimir Patsionov wrote:

                     

                    So is it necessary to use only integer type field in a factless fact table? Or maybe there's a way to use other types?

                     

                    I frankly don't know - never tried anything else beside a number in a table like this. Perhaps the SSAS gurus on the Microsoft SSAS board can comment on this for you?

                     

                     

                    Then I decided to add a fictional measure which won't mean anything - just to let cube to be processed. Everything worked OK but when I opened Tableau New UserDimension was grey and there was an announcement that few measures fields are not compatible with UserDimension. What does this mean?

                     

                    What was the exact error message?

                    1 of 1 people found this helpful
                    • 8. Re: Analysis Services Impersonation
                      Russell Christopher

                      Thanks. From books online:

                       

                      http://onlinehelp.tableausoftware.com/v6.1/pro/online/en-us/i1219832.html

                       

                      Tableau helps you figure out the dimensions and measure that can be used together in meaningful ways by highlighting unrelated dimensions and measures in gray.

                       

                       

                      So, I think we're being told that the new table that you've added isn't related correctly to the other dimensions and measure groups already in your cube and being used in the current visualization. What happens when you try and create a similar report using the same mix of measures & dimensions in the SSAS cube browser? I suspect you'll a similar error when you add your "new fields".

                      1 of 1 people found this helpful
                      • 9. Re: Analysis Services Impersonation
                        Vladimir Patsionov

                        Russel, could you add a print screen of Dimension Usage Tab in OLAP project that you mentioned above?

                        • 10. Re: Analysis Services Impersonation
                          Vladimir Patsionov

                          Well, Russell, finally i got it all working! THANK YOU VERY VERY MUCH! Your answer was very helpful!

                          • 11. Re: Analysis Services Impersonation
                            Reza Rahmati

                            Hey would you please add your solution here for other people who are facing same issue