Before i deep dive into the technical aspects of this, here is my experience to share :
- I had an query on MicroStrategy Free form SQL Security Filter Assignment to understand if this will work dynamic based on user role – Hence i commented on KB Article – No Reply
- Also opened an case with MicroStrategy while i try to make myself available to test in house on top of other work activities.
- I wasn’t getting an response with support case immediately – hence, it strike my thoughts to use EXPERT.NOW service that MicroStrategy offers
It was very easy to schedule for an expert discussion via MicroStrategy Community & proceed with explaining the issue with an MicroStrategy Expert (Monika Peasecka)
It was a fruitful conversation with MicroStrategy Expert, i myself went ahead with tutorial project on my laptop and tested it. Also showed her the issue or limitations that i’m facing with to discuss internally within MicroStrategy to work on the fix for limitations.
Outcome of the discussion
- Update KB Article that MicroStrategy maintains for the other customers to take advantage of this feature(explained in detail in this article below) +
- Update the known issue that i ran into – which deals with COMBINATION of Attributes as SECURITY FILTER (DEFECT/ENHANCEMENT to fix this within PRODUCT)
Benefits
- Quick connect to an MicroStrategy Expert (Love It!!)
- You get expert advise immediately if they are aware of the solution for the issue you’re facing over the call
- Helps connecting with business/customers immediately
- Architect Pass – you get it for 30 Days to view tons of videos about use of MicroStrategy
My Feedback/Suggestion to Any BI Tools:
- Improve the Support communication with similar format
- Schedule with customers for quicker understanding of actual issues via Google Meet/WebEx/Join.Me/Goto Meet /Audio call
- Understand and then work on the cases – so lot of time can be saved in many of the support cases &
- Customers will be happy in getting quicker answers or support engineer be able to work on actual issue than with assumptions
Don’t you agree to it ? Please provide your comments below. Thanks!
Security Filter on MicroStrategy Free form SQL Report
What is Security Filter ?
A security filter is an object that you assign to users or groups, which limits the result set when users execute reports or browse elements. Security filters enable you to control what warehouse data users can see, at the MicroStrategy level. This function is like database views and row level security.
I define it as “ROW LEVEL SECURITY” functionality is called as SECURITY FILTER
Scenario Explained
What if the organization has multiple level of attributes – used as security filter (example below)?
- User A : Region = NORTH AMERICA
- User B : Region = ASIA
- User C : COUNTRY = USA
- User D : Country = India
- User E : State = California
- User F : State = Tamil Nadu
- User G : State = Tamil Nadu & Category = Books
- User H : Country = USA & Product = iPhone
How can we dynamically pass security filter within FFSQL in the above case ? Any idea ?
Knowledge Base Article
MicroStrategy explains how to embed security filter within Freeform SQL (FFSQL) Report in below KB article. Thanks for sharing that!
But the article wasn’t helpful for above scenario mentioned.
Solution Explained
Pre-requisite
- Ensure your attribute used for Security Filter resides within MicroStrategy FFSQL Report (Here Security Filter will be applied based on Region, Call Center & Category – hence they are present in below FFSQL Report)
- Define the security filter object that are used across the project or specific to your business needs
How to Insert Security Filter within FFSQL ?
Right click in WHERE Clause section to insert Security Filter
Alternative is use “Edit” menu to “Insert Security Filter”
Here my requirement is that, there are different roles of users with security filter on respective attributes (Region, Call Center & Category) – hence, used multiple attributes that are used on security filter in the FFSQL
Scenario 01 : Region User = SOUTH
Edit the user editor & go to “Security Filter” >> Pick the project
Apply the security filter as “REGION IN SOUTH” (this can be created beforehand)
Now, login as the user with above security filter applied & run the FFSQL report
Report displays only “SOUTH” region
Scenario 02 : Category = Books
Scenario 03 : Call Center IN (<<Some set of Call Centers>>)
Scenario 04 : Combination of Attributes – will it work (Like Region & Category combined)
Report SQL isn’t as expected & hence, this scenario wouldn’t work
If this is your business need, then we will need to reach out to MicroStrategy Product team for handling this behavior with right SQL as expected.
Note: Same approach can be used on “Stored Procedures” as well but you will need to pass these SQL statement for WHERE clause as STRING & handle within STORED PROC accordingly
Idea Vote : https://community.microstrategy.com/s/idea/0872R000000bns7QAA
Thanks for Reading! Hope it helps! Comment below if any other scenarios you would think is missed to be covered here.
Very helpful…