How to Connect SSAS Cube in Power BI

Connecting your SQL Server Analysis Services (SSAS) cube to Power BI is a great way to combine a powerful, business-tested data model with a leading-edge visualization tool. Instead of rebuilding your logic from scratch, you can leverage the years of work already invested in your SSAS cube. This tutorial walks you through exactly how to establish that connection, explaining the key concepts and best practices along the way.

First, What Is an SSAS Cube?

Think of an SSAS cube as a highly organized and optimized data structure designed for fast analytics. SQL Server Analysis Services, a component of Microsoft SQL Server, takes your raw transactional data from various sources and pre-calculates, aggregates, and stores it in a multidimensional format called a "cube."

Why is this useful? Instead of querying millions of individual sales records to calculate "Total Sales for North America last quarter," your query targets a pre-aggregated value. This makes your reports incredibly fast. Cubes contain:

• Measures: These are the numeric values you want to analyze, like Revenue, Quantity Sold, or Cost.
• Dimensions: These are the categories you use to slice and dice your data, such as Time, Geography, Product, or Customer.
• Hierarchies: Dimensions often have hierarchies, like Year > Quarter > Month > Day, making it easy to drill down into your data.

Connecting this well-structured, high-performance cube to Power BI gives you the best of both worlds: a robust, reliable data engine from SSAS and flexible, beautiful visualizations from Power BI.

Live Connection vs. Import: Choosing Your Connection Mode

Before you jump in, it's important to understand the two ways Power BI can connect to SSAS. How you connect determines how Power BI interacts with your data, affecting performance, features, and how up-to-date your reports are.

Live Connection (The Recommended Method for SSAS)

A live connection is the most common and often best way to connect Power BI to an SSAS cube. In this mode, Power BI doesn't import or store any data itself. Instead, it acts as a visualization layer, sending queries directly to the SSAS cube in real-time for every interaction you make in your report.

• Pro: Data is Always Current. Because queries go directly to the source, your Power BI reports always reflect the latest data in the cube. No scheduling refreshes are necessary.
• Pro: Leverages the SSAS Engine. All the complex calculations and DAX logic defined in your cube are preserved. This leverages the performance and security rules already built into your SSAS model.
• Pro: Handles Huge Datasets. Since no data is imported into Power BI's memory, you can analyze massive enterprise-level cubes that would otherwise be too large to import.
• Con: Limited Data Modeling. You cannot use Power Query to transform data, and you can only write report-level measures in Power BI. The data model itself lives in SSAS and must be modified there.

Import Mode

In Import mode, Power BI takes a full copy of the data from the SSAS cube and stores it within the Power BI file (.pbix). The data is compressed and stored using Power BI's super-efficient in-memory engine.

• Pro: Full Power Query & DAX Features. You have access to the full suite of Power Query for data transformation and you can create complex new tables, columns, and measures in Power BI's PBIX model.
• Pro: Better Report Performance for Simple Visuals. Once the data is imported, basic slicer and filter interactions can sometimes feel faster because they are hitting the in-memory PBIX model instead of sending a query across a network.
• Con: Data is Not Live. The dataset is a snapshot in time. To see updated data, you must schedule and perform a data refresh within the Power BI service.
• Con: Data Size Limitations. Importing data is subject to the size limits of your Power BI license (e.g., 1 GB per dataset for Pro), making it impractical for very large cubes.
• Con: Replicates Logic. You are essentially ignoring the work done in the cube and rebuilding the model within Power BI, which can lead to duplicated effort and inconsistencies.

The Verdict: For SSAS, almost always use a Live Connection. It’s purpose-built for this scenario, respecting the security and performance of your existing enterprise model.

Step-by-Step Guide: How to Connect to an SSAS Cube

Connecting is a straightforward process. All you need is Power BI Desktop installed and a network connection to your SSAS server.

Step 1: Open Power BI and Select 'Get Data'

Launch Power BI Desktop. On the Home ribbon, click on the Get Data dropdown. From the common data sources, find and click SQL Server Analysis Services database.

Step 2: Enter Your Server Details

A dialog box will appear asking for your server information.

• Server: Enter the name of your SSAS server. If it's a specific instance, you might need to enter it in the format ServerName\InstanceName. Your database administrator or IT department will be able to provide this.
• Database (Optional): You can leave this blank if you want to browse all the databases on the server, or you can specify a database name here to jump directly to it.

Step 3: Select Your Connection Mode

This is where you choose your path from the previous section. Select Connect live. This is the pre-selected default for Analysis services connections for good reason. Click OK.

Note: You might be prompted for credentials if your Windows account doesn't automatically have access. You may have to enter a username and password under the 'Database' or 'Windows' authentication options.

Step 4: Navigate and Select Your Cube

Next, the Navigator window will open. This is where you see all the data models available to you on that SSAS server.

• On the left-hand side, browse through the available databases and models. An item with a cube icon (a small 3D cube) represents your SSAS model.
• Expand the server, then the database, then the model until you find the specific cube or perspective you want to analyze.
• Click on the cube's name. The preview area on the right will confirm that you've selected a valid model.
• Click OK.

Step 5: Start Building!

That's it! You are now live connected to your SSAS cube. Notice an indication in the bottom-right corner of Power BI showing "Connected live to...".

Look at your Fields pane on the right. You will see a list of all the measures and dimensions from your cube, neatly organized. The calculator icon indicates measures, while dimensions are listed with their corresponding fields and hierarchies. You can now drag and drop these fields onto the report canvas to start building visuals just like you would with any other data source.

Working With a Live Connection: Tips & Limitations

Building reports with a live connection is powerful, but it’s a little different from working with imported data. Here’s what to keep in mind:

• No Data View: The "Data" icon on the left-hand pane will be greyed out. Because Power BI isn't storing any data, there's no underlying table to view. You can only see the data through the report visuals.
• No Power Query Editor: You cannot open the Power Query Editor to transform or clean data. All transformations must be done in the source SSAS cube by a data modeler.
• Embrace Hierarchies: Your cube likely has built-in hierarchies (like time or geography). Use these in your visuals! Power BI will automatically enable drill-down functionality, allowing you to go from Year to Quarter to Month with a single click.
• Use Report-Level Measures Sparingly: While you can’t create new calculated columns or tables, you can create "report-level" measures using DAX directly inside your Power BI report. However, it's a best practice to define most of your business logic within the centralized SSAS cube so all reports connected to it can benefit.

Common Troubleshooting Steps

Sometimes connections don't work on the first try. Here are a few common hiccups:

1. Cannot Connect to Server:
2. Authentication/Permission Errors:
3. Refreshing in the Power BI Service: The On-Premises Data Gateway:

Final Thoughts

By connecting your robust SSAS cube to Power BI using a live connection, you can immediately start building dynamic, modern reports without recreating your data model. This approach leverages your organization's existing investment in data infrastructure while empowering analysts and decision-makers with best-in-class visualization and dashboarding tools.

Connecting to enterprise systems like SSAS is powerful, but we know that many marketing and sales teams have data scattered across a dozen different cloud platforms. Manually exporting CSVs from Shopify, Google Analytics, and Facebook Ads is a reporting nightmare. That's why we built Graphed to make connecting to all your data sources effortless. You can integrate your apps with a few clicks and then use plain English to build real-time dashboards in seconds, not hours.