Summary



Role-Based Default Filter Context in Power BISummaryThe purpose of this paper is to describe a methodology for providing role-based default filter context in Power BI, Power Pivot, and SSAS Tabular. Implementing this methodology can eliminate manual user filter selections as well as distinct role or hierarchy specific reports. By embedding conditional filtering logic based on the user’s identity and reporting role into the data model, template reports and dashboards targeted at specific reporting roles can be leveraged by users with no additional user personalization or report customization required. IntroductionA common, important pattern in larger scale BI deployments is to enable a given user to easily view the content that’s most relevant for the user based on the user’s role level in an organizational hierarchy and some other dimension such as store or market. For example, a store manager is primarily interested in metrics filtered on the store she manages, store level granularity, and to a lesser extent the parent grouping her store belongs to (such as region, market, or zone) or metrics for the entire organization. However, these other stores and hierarchy levels can still provide valuable analytical context and thus the goal of the solution is to default the user to a specific filter context upon accessing the BI content but also provide the self-service flexibility to access other filter contexts if desired by the user.Example ScenarioIn this example, the AdventureWorksDW2016CTP3 database is used as the source for an SSAS Tabular 2016 (compatibility level 1200) In-Memory model. The business requirement is A) for members of the sales organization to automatically see ‘their data’ when logging into Power BI dashboards (or accessing SSAS Tabular database via other tools such as Excel) and B) have the option to make filter selections on the sales territory dimension to change the filter context away from the default role-based filter.The AdventureWorksDW SalesTerritory dimension has a three-level hierarchy with Group as a parent to Country and Country as a parent to Region per the image below.There are three reporting roles related to each hierarchy level – Group VPs, Country AVPs, and Regional Managers. Users in each of the roles should be defaulted to their specific dimension member or value. For example, the Country AVP for United States should see all metrics filtered by United States by default. However, the United States AVP should also have full flexibility in filtering the Sales Territory dimension including viewing data for the entire organization (no filters applied).Standard AlternativesIn Power BI, reports and dashboards can be deployed as a content pack enabling users to customize these templates in their own workspace (see User Power BI Content Packs). This is a powerful collaboration feature appropriate for many scenarios but this personalization can be onerous for users if many individual reports and report pages have to be modified manually. Additionally, the personalized content pack becomes disconnected from the published version of the content pack and thus won’t receive updates/modifications to the published content pack.Another alternative is to create multiple reports using filters defined on the report/visualization layer. The downsides to this approach is the additional reports and dashboards in the environment to maintain and monitor as well as the need to modify the Report Level Filter(s) and thus require users to navigate away from the report canvas.Note: A new feature allowing a global filter on Power BI dashboards (see Global Filter) is in ‘Planned’ state per the Power BI Ideas page and this could provide an alternative solution as well.Design OverviewThe three essential components of the design are as follows:Duplicate Dimension Attributes In the AdventureWorks sample database, for this example, additional cpiesThe ‘UD’ alias is applied to the dimension columns and these duplicate columns are used as slicer objects in the Power BI reports. DAX metrics determine if a selection has been made on any of the three attributes and this drives whether a user role based filter should be applied or if the user’s filter selections should be respected.User TableA table with the User’s name (domain and username), report role, and dimension keyThe USERNAME() DAX function is used to retrieve the role and dimension values of the report user at query time and apply the appropriate filterThe source, quality, ETL and governance of this table is critical to ensure changes among users and additions/deletions of users is reflected in reporting.Logic is built into the solution for users not found in this table to access the standard metrics with no custom filters appliedRole-Based Content PacksReport templates are built with the specific grain/level of the role and distributed as content packsFor example, a Group content pack in this example would contain visuals at the highest level focused on North America, Europe, and Pacific groups. The Group VP accessing this content pack could drill down to Country and Region if desired.Users accessing the content packs aren’t required to personalize DAXNote: The four status metrics are optional. They’re intended to advise the user of the filter context applied.MetricExpressionUserUSERNAME()UserSalesKeyLOOKUPVALUE(Users[SalesTerritoryKey],Users[UserName],[User])User RoleIF(ISBLANK(LOOKUPVALUE(Users[SalesRole],[UserName],[User])),"Corp",LOOKUPVALUE(Users[SalesRole],[UserName],[User]))UserSalesGroupIF([User Role] = "Corp", "Corp",LOOKUPVALUE(SalesTerritory[SalesTerritoryGroup],SalesTerritory[SalesTerritoryKey],[UserSalesKey]))UserSalesRegionIF([User Role] = "Corp","Corp",LOOKUPVALUE(SalesTerritory[SalesTerritoryRegion],SalesTerritory[SalesTerritoryKey],[UserSalesKey]))UserSalesCountryIF([User Role] = "Corp","Corp",LOOKUPVALUE(SalesTerritory[SalesTerritoryCountry],SalesTerritory[SalesTerritoryKey],[UserSalesKey]))IsTerritoryFilteredIF(ISFILTERED(SalesTerritory[UD Sales Territory Country]) || ISFILTERED(SalesTerritory[UD Sales Territory Group]) || ISFILTERED(SalesTerritory[UD Sales Territory Region]), "User Defined", "Role Based")D SalesIF([IsTerritoryFiltered]="User Defined" || [User Role] = "Corp",[Internet Sales $],SWITCH([User Role],"Country",CALCULATE([Internet Sales $],FILTER(SalesTerritory,SalesTerritory[SalesTerritoryCountry]=[UserSalesCountry])),"Region",CALCULATE([Internet Sales $],FILTER(SalesTerritory,SalesTerritory[SalesTerritoryRegion]=[UserSalesRegion])),"Group",CALCULATE([Internet Sales $],FILTER(SalesTerritory,SalesTerritory[SalesTerritoryGroup]=[UserSalesGroup]))))Sales Filter StatusIF([IsTerritoryFiltered]="User Defined","Sales Territory Filter: User Defined","Sales Territory Filter: Role Based")Sales Group Stat" My Group: " & [UserSalesGroup]Sales Region Stat" My Region: " & [UserSalesRegion]Sales Country Stat" My Country " & [UserSalesCountry]Sample Metric ValuesConditional LogicConceptually the DAX metrics answer the following questions:Is the user in the user table?If so, what’s her role and sales territory key?If not, the standard Internet Sales metric can be applied along with any filter selections from the userHas any filter been applied on the Sales Territory dimension (Group, Country, Region)?If not, a role based filter should be applied specific to the given userIf so, the report should allow this filter selection to pass throughUltimately a new, ‘smart’ sales metric (D Sales) dynamically responds to the identity and role of the active report userFor a user to view the entire organization (after being defaulted to a filter context), the user could select all the values of the Group attributeAdditional Design Considerations: This approach entails new metrics with the role-based logic for each metric to be used in the reportingA report that mixes standard metrics with role-based dynamically filtered metrics could lead to confusionDisplay folders and/or naming conventions should distinguish dynamic metrics from standard metricsThe new duplicate attributes must only be used as slicers. The standard attributes for Group/Country/Region should be used within the reporting visuals themselves. Variables could be used for SSAS 2016 instances to improve manageability of the codeTo avoid performance degradation declaring multiple variables should be avoided (see DAX Variable Design)SSAS 2016 Tabular performance benefits from ‘Lazy Evaluation’ of IF/Switch conditions. Previously all branches of the IF conditions were evaluated leading to poorer performance for expressions like those use in this example. (see Super DAX) As LOOKUPVALUE is a ‘virtual relationship’ that uses the single threaded formula engine it’s important to limit or avoid the use of these functions against large tables and to test for performance. A revised approach is likely required for an SSAS Direct Query model. Details of this design are outside the scope of this paper.Excel Client Role Based Example:The logged in user is in the Country report role (Group, Country, Region) and is associated with the United States. Therefore, despite no filters being applied by the slicer, the D Sales metric is the equivalent of Country = United States.Excel Client User Defined Example:By filtering on one of the three columns, D Sales adjusts and aligns with the standard metric. Per the requirements the user has full filtering control with the D Sales metric following the initial default filter in the previous screen. Power BI Client Role Based ExampleThe same $9.4M for United States is visible to the United States, Country role user and can be broken down by Region. The UD columns are exposed as slicers but no values are selected.Per the additional design consideration sections, it’s essential that the ‘UD’ or duplicate columns not *The Filter Context is just a custom title for the IsTerritoryFiltered metric. Likewise ‘Sales’ is used as a data label for the D Sales metric. Power BI User Defined ExampleThe United States, Country-role user selects Canada and the United States on the slicer and the filter context changes from the role based default to the User Defined context of Canada and United States. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download