Replace: In case you are right here to discover ways to browse your SSAS Multidimensional mannequin in Energy BI please discuss with this text. The present article reveals you how you can refresh knowledge on Energy BI service on prime of a SSAS Multidimensional occasion primarily based on knowledge import situation.
It’s been some time that numerous 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 (knowledge import situation) after we revealed it to Energy BI web site. However, what knowledge import situation means? Which means we can not create mashups with knowledge we have already got in an current SSAS Multidimensional database/dice by way of the SQL Server Evaluation Providers connector which is offered on Energy BI web site. So we have to hook up with a SSAS multidimensional occasion by way of Energy BI Desktop and cargo the dice’s knowledge 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 studies and publish them to Energy BI web site and eventually we’ll be capable of schedule knowledge refresh on the Energy BI web site.
We will additionally hook up with a SSAS Multidimensional occasion by way of Energy Pivot AND/OR Energy Question from Excel then load the Excel file into Energy BI web site.
Observe: We will do the identical by way of Energy Question, however, we received’t capable of setup a knowledge refresh schedule on Energy BI web site if we didn’t load
It’s simply superior isn’t it?
On this publish I present you how you can implement all the information import eventualities utilizing Energy BI Desktop, Energy Pivot and Energy Question from Excel.
Initially you must obtain the gateway from right here. Then you must 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 publish so I go away the set up half to you. Nonetheless, I clarify the information refresh half once more.
Get Information
- Open Energy BI Desktop
- Click on Get Information
- Choose “SQL Server Evaluation Providers Database” from the listing and click on “Join”
- Enter the SQL Server Evaluation Providers occasion title
- The database title is optionally available, however, I put “AdventureWorksDW2012”
- Click on “Choose objects and get knowledge from Multidimensional or Tabular mannequin”
- As you’ll be able to 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
- Increase 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’ll be able to see you’ll be able to increase a dimension and choose the specified members or hierarchies
- Click on Load
- After your mannequin loaded you’ll be able to see a desk with date dimension members and “Web Gross sales Quantity” measure on it. As you would possibly seen there isn’t any 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 Information Refresh Setup
- Open your web browser and browse Energy BI web site
- Check in to your account
- Discover the newly revealed dataset from Datasets pane
- Click on on the ellipsis button then click on “Schedule Refresh”
- Increase “Gateway Standing” to verify the non-public gateway is on-line
- Increase “Information Supply Credentials” then click on “Edit Credentials” and ensure Authentication Methodology is ready to “Home windows” then click on “Signal In”
- Increase “Schedule Refresh” and swap 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 knowledge refresh fails. Click on Apply.
All accomplished!
- Open Microsoft Excel then go to Energy Pivot
- On the ribbon click on on “From Information” from “Get Exterior Information” part
- Click on “From Evaluation Providers or Energy Pivot
- Enter the SSAS server title then choose the database from dropdown listing then click on Subsequent
- Kind a pleasant title for the question and click on “Design” button
- Choose a desired measure group
- Increase measures, then drag and drop a desired measure to the grid space
- Scroll down and increase a desired dimension
- Drag and drop a member or hierarchy to the grid
- Click on OK then click on End
- After knowledge import is accomplished click on Shut
- Now that we imported the information we must always save the Excel file.
- Login to your Energy BI web site
- Click on on Get Information
- Click on on Get Information
- Click on Native File and import the Excel file
- Now you need to see the Excel dataset within the Datasets listing
- Click on on the ellipsis then click on Schedule Refresh
- Now you’ll be able to setup schedule refresh as desired
- Open Excel then from Energy Question get exterior knowledge From Database
- Click on “From Evaluation Providers Database”
- Enter the SSAS server title then click on OK
- From the Navigator increase database, increase a desired dice, increase 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 knowledge to the Information Mannequin”. This is essential to load knowledge into the mannequin in any other case you WON’T be capable of setup a Schedule Refresh on Energy BI web site for an Excel dataset which doesn’t have any knowledge within the knowledge mannequin.
- After your knowledge loaded save the Excel file
- Now you must import the Excel file into your Energy BI web site and setup a schedule refresh as I defined beforehand
All accomplished!
