Contact us anytime to know more — Kuldeep K., Founder & CEO CISIN
Power BI Desktop provides access to SQL Server Analysis Services multidimensional models (SSAS MD). Many of us have patiently waited for Power BI and SSAS Multidimensional enhancements.
Microsoft launched an updated version of Personal Gateway with one key new feature: updating an on-premises SSAS Multidimensional model after publishing to the Power BI website through "data import scenarios." But what exactly are these scenarios?
As such, we cannot utilize advanced data analytics from an existing SSAS Multidimensional database/cube using sql server analysis services database on Windows to make mashups with it; so instead, we will utilize Power BI Desktop to connect to an SSAS multidimensional instance and import its cube's data directly into Power BI models as relational models built upon them will provide access to it.
Capabilities And Features Of SSAS MD
Multidimensional Model Tabular Metadata
This depicts the relationship between multidimensional objects and tabular metadata returned by Power BI Desktop.
The Premium capacity Power BI client applications looks up tabular metadata within its composite model to locate appropriate tabular metadata that it runs against SSAS when visualizing tables, matrixes, charts or slicers.
Measure Groups And KPIS
Multidimensional cube measure groups can be displayed as tables in the Fields pane with their associated Sigma () values shown alongside.
Calculated measures that do not belong to any measure group can be organized under a calculated table in tabular metadata for easy reference.
Power BI detects project files folders in tabular metadata and displays measurements or KPIs for multidimensional models.
It provides value, goal, status graphic, and trend graphic charts as KPIs are supported within multiple-dimensional databases.
Dimension Attribute Type
Dimension attributes may also be connected with certain dimension attribute types within multidimensional model projects.
For instance, Geography dimensions that link City, State-Province, CountryRegion and Postal Code characteristics to relevant geography types are readily accessible in tabular information and recognized by Power BI for map visualization purposes; their metadata links appear with an indicator in Power BI's Field pane to show linkages with elements within that geography dimension.
Power BI can generate images provided you provide it with fields containing URLs of images to use as URL references for visualizations, like SQL Server Data Tools' ImageURL types or Power BI Desktop's table metadata information for these fields.
After retrieving photos via their respective URLs and visualizing them within Microsoft Power BI.
Parent-Child Hierarchies
Parent-child hierarchies can be created and displayed using multidimensional model project and tabular metadata, with each level accessible through hidden columns in the table.
Unfortunately, tabular metadata cannot provide key attributes of any particular dimension containing parent-child relationships.
Dimension Calculated Members
Calculated members can be created in multidimensional models using computer computations. Here are more prevalent computed member types: Calculated members on attribute hierarchies that don't descend from All (calculated elements on user hierarchies, for instance) can be exposed as column values within multidimensional models and offer further options and constraints:
- Unknown Member is an optional dimension attribute.
- An attribute with computed members cannot be the key attribute in any dimension unless it is its only attribute.
- A point containing calculated members cannot serve as the parent-child attribute.
Power BI does not expose calculated members of user hierarchies directly; rather, you can connect to a cube that contains estimated members based on user hierarchies; however, any calculated members that don't meet certain constraints won't appear here.
Also Read: Implementing Role-Based Security in Power BI: A Comprehensive Guide
Security
Multidimensional models support dimension table and cell-level security context through roles. When connecting to a cube using Power BI, users are authenticated and their permissions checked; dimension security prevents dimension members from showing in Power BI while those granting cell security role authorization cannot connect using this method.
Manage SQL Server Analysis Services Data Sources
At Data Gateway on-premises, documentation is split between Power BI-specific information and general text that applies to all services it supports.
At this point, you are in Power BI-specific content; feel free to scroll to the end in the Power BI section about any topic covered herein or overall gateway experience.
Once installed, an on-premises data gateway installation enables users to add additional sources once connected.
In this blog post, we'll walk through how you can add Power BI SSAS Integration as a data source in an on-prem SSAS database gateway for refresh schedule or live connections.
Add A Data Source
A multidimensional or tabular Analysis Services data source:
- On the New Connection screen for your on-premises data gateway, select Analysis Services as your Connection type.
For more information about additional resources data, please see Add data sources.
- Provide data source details, including Server and relational Database information.
The gateway connects to the Analysis Services instance using the Username and Password provided as details by you.
- Your Windows account must possess the Server Administrator role on the Analysis Services instance you are connecting to; otherwise, users will experience connection errors until their data source password has been updated - for more details regarding credential storage, see encrypted credentials in the cloud.
- Adjust the Privacy Level for Your Data Source.
This option specifies how data for scheduled refresh can be mixed; live connection string remain unaffected.
See Set Privacy compatibility Levels (custom Query) for further information regarding privacy levels for data sources.
- User name mapping is now available; for details, please see Manual User Remapping.
- This premises data source can now be utilized for scheduled refreshes or live connections to an on-premises Analysis Services server.
Usernames For Analysis Services
Read to understand authentication with Analysis Services live connections in Power BI: When users interact with reports connected with Analysis Services, their effective user name (EuuNum) is passed to both a gateway server and to their on-premises Analysis Services server through an Effective UserName attribute connection attribute containing their email address used when signing into Power BI is then passed onto Analysis Services as the effective identity in that report.
Within an Active Directory (AD) domain, an email address must correspond with an established User Principal Name (UPN).
An AD account's UPN serves as its property; Analysis Services roles contain Windows accounts with UPNs that do match. Otherwise, sign-in may fail, and further information on Alzheimer's disease should be found here.
Map User Names For Analysis Services Data Sources
Your Power BI sign-in name can also be linked with a Unified Person Number in your local directory. For more details on UPN mapping within Power BI: User names can be mapped in power bi ssas data source.
Rules can be created that assigns one sign-in user name from Power BI to an Effective UserName that passes along to an Analysis Services connection. When your Azure Active Directory (Azure AD) User doesn't match up with one in your local Active Directory instance, this functionality provides a great workaround; for example, if your email address is meganb@contoso.onmicrosoft.com, then map this to meganb@contoso.com which passes onto gateway.
There are two approaches to map user names for Analysis Services:
- Manual User remapping in Power BI - training
- Active Directory Lookup Mapping uses on-premises AD property lookup to remap Azure AD UPNs back onto on-premises AD users.
Manual mapping using on-premises AD property lookup can be time-consuming and complex, particularly if pattern matching fails to produce adequate results.
Domain names or account names could differ between Azure analysis AD and on-premises AD systems requiring manual mapping as opposed to Azure AD's automated method; as a result, this second approach should not be pursued as recommended for manual mapping purposes.
Considerations And Limitations
There are certain limitations and dropdown list associated with the use of SSAS MD:
- Only Enterprise data models and Business Intelligence editions of SQL Server support live connections; for live access via the standard edition, SQL Server or later must be used.
- Power BI does not offer direct access to actions or named sets; however, you may still link cubes containing these features to create visualizations and reporting service.
- Power BI doesn't allow data extraction from every SSAS model schema in its metadata display.
If the 32-bit version of Microsoft Online advanced Analytics capabilities and its service was installed but not the 64-bit version, this might happen; installing 64-bit may help resolve it.
- Authoring reports linked live to an SSAS multidimensional model does not allow you to generate report-level metrics; only measures defined within the MD model will be accessible.
Conclusion
With SQL Server's introduction of Analysis Services Tabular models as Direct Query sources to Power BI service Desktop models, we now have more flexibility when building reports using Power BI service Desktop models.
In Data Mesh designs where different teams manage specific products such as data tables or product feeds, using these direct query sources makes incorporating that data easier without copying their actual source directly into reports.
SSAS Integration Best Practices for Power BI multidimensional models can be seamlessly implemented into reports published or uploaded directly to Power BI services using the Live Connection mode on both Power BI Desktop and Service.
Reports created with these models can also be printed or submitted in real time to the Power BI service for reading, publishing, and uploading.
