Replace: If you’re right here to learn to browse your SSAS Multidimensional mannequin in Energy BI please discuss with this text. The present article exhibits you methods to refresh information on Energy BI service on prime of a SSAS Multidimensional occasion primarily based on information import situation.

It’s been some time that a number of us are ready for seeing enhancements on Energy BI and SSAS Multidimensional. The excellent news is that Microsoft launched a new model of Energy BI Private Gateway final week on 3 Sep 2015. One of many new options added to this launch is that we will now refresh an on-prem SSAS Multidimensional mannequin (information import situation) after we revealed it to Energy BI web site. However, what information import situation means? Which means we can not create mashups with information we have already got in an present SSAS Multidimensional database/dice via the SQL Server Evaluation Providers connector which is accessible on Energy BI web site. So we have to connect with a SSAS multidimensional occasion via Energy BI Desktop and cargo the dice’s information into the Energy BI mannequin. Certainly we are going to create a relational mannequin on prime a multidimensional mannequin from SSAS.

Then we will create experiences and publish them to Energy BI web site and eventually we’ll be capable to schedule information refresh on the Energy BI web site.

We will additionally connect with a SSAS Multidimensional occasion via Energy Pivot AND/OR Energy Question from Excel then load the Excel file into Energy BI web site.

Be aware: We will do the identical via Energy Question, however, we gained’t in a position to setup an information refresh schedule on Energy BI web site if we didn’t load

It’s simply superior isn’t it?

On this put up I present you methods to implement all the info import situations utilizing Energy BI Desktop, Energy Pivot and Energy Question from Excel.

To start with it’s essential to obtain the gateway from right here. Then it’s essential to uninstall the present model of Energy BI Private Gateway out of your machine and set up the brand new model. The entire gateway set up and technique of refreshing an on-prem SSAS database is just about the identical as what I defined in this put up so I go away the set up half to you.  Nonetheless, I clarify the info refresh half once more.

Get Knowledge

• Open Energy BI Desktop
• Click on Get Knowledge
• Choose “SQL Server Evaluation Providers Database” from the listing and click on “Join”

• Enter the SQL Server Evaluation Providers occasion identify
• The database identify is elective, however, I put “AdventureWorksDW2012”
• Click on “Choose objects and get information from Multidimensional or Tabular mannequin”
• As you may see it’s also possible to put your MDX or DAX customized queries, however, we go away it clean in our pattern
• Click on OK

• Broaden the cubes and underlying measure teams  then tick the measures you want. On this pattern I chosen “Web Gross sales Quantity”

• Scroll down and tick the specified dimensions. I chosen “Date” dimension
• As you may see you may develop a dimension and choose the specified members or hierarchies
• Click on Load

• After your mannequin loaded you may see a desk with date dimension members and “Web Gross sales Quantity” measure on it. As you would possibly observed there is no such thing as a hierarchies within the desk as hierarchies aren’t supported in Energy BI.

Creating/Publishing a Easy Report

On the report view tick “Web Gross sales Quantity” and “Date.Calendar Yr” to create a easy column chart.

Now click on “Publish” from the ribbon to publish the report back to Energy BI Web site.

Schedule Knowledge Refresh Setup

• Open your web browser and browse Energy BI web site
• Register to your account
• Discover the newly revealed dataset from Datasets pane
• Click on on the ellipsis button then click on “Schedule Refresh”

• Broaden “Gateway Standing” to verify the non-public gateway is on-line

• Broaden “Knowledge Supply Credentials” then click on “Edit Credentials” and ensure Authentication Methodology is about to “Home windows” then click on “Signal In”

• Broaden “Schedule Refresh” and change the “Sustain-to-date- choice to Sure. Then set desired refresh frequency, time zone and time. Tick “Ship refresh failure notification e-mail to me” if you wish to get knowledgeable when a scheduled information refresh fails. Click on Apply.

All carried out!

• Open Microsoft Excel then go to Energy Pivot
• On the ribbon click on on “From Knowledge” from “Get Exterior Knowledge” part
• Click on “From Evaluation Providers or Energy Pivot

• Enter the SSAS server identify then choose the database from dropdown listing then click on Subsequent

• Sort a pleasant identify for the question and click on “Design” button

• Choose a desired measure group
• Broaden measures, then drag and drop a desired measure to the grid space

• Scroll down and develop a desired dimension
• Drag and drop a member or hierarchy to the grid

• Click on OK then click on End

• After information import is accomplished click on Shut

• Now that we imported the info we must always save the Excel file.

• Login to your Energy BI web site
• Click on on Get Knowledge
• Click on on Get Information

• Click on Native File and import the Excel file

• Now it’s best to see the Excel dataset within the Datasets listing

• Click on on the ellipsis then click on Schedule Refresh

• Now you may setup schedule refresh as desired

• Open Excel then from Energy Question get exterior information From Database
• Click on “From Evaluation Providers Database”

• Enter the SSAS server identify then click on OK

• From the Navigator develop database, develop a desired dice, develop measure group then choose a desired measure

• Scroll down and choose a desired dimension, dimension member or hierarchy
• IMPORTANT: Click on on the Load button possibility and choose “Load To”

• Tick “Add this information to the Knowledge Mannequin”. This is essential to load information into the mannequin in any other case you WON’T be capable to setup a Schedule Refresh on Energy BI web site for an Excel dataset which doesn’t have any information within the information mannequin.

• After your information loaded save the Excel file

• Now it’s essential to import the Excel file into your Energy BI web site and setup a schedule refresh as I defined beforehand

All carried out!