It’s been awhile that we’re ready for a smart enhancements in Microsoft self-service BI. The excellent news is that lastly there will probably be some cool new options added to the following model of Excel which is Excel 2016. By some, I imply, nicely, there not rather a lot new BI options, however, some. One thing is best than nothing, not too unhealthy although!
Energy View and Energy Map:
As you understand, Energy Pivot was built-in as a built-it function to Excel 2013. Now I’m actually joyful that the identical factor occurred to Energy View and Energy Map. So that you don’t want to put in them individually. Now you can flip these options on from:
File–> Choices–> Superior-> (scroll down the web page) Knowledge-> Allow Knowledge Evaluation Add-ins: Energy Pivot, Energy View, and Energy Map
OR you possibly can nonetheless flip them on from “COM Add-ins”:
File->Choices->Add-Ins-> Handle “Com Add-ins”-> Go
Now you possibly can see “Energy Map” and “Energy View” on the “Insert” tab in addition to the “Energy Pivot” which has a separate tab:
Energy Question:
To me, it’s an excellent information that “Energy Question” is built-in with Excel. Really it’s there by default and also you don’t even want to show it on.
You will discover it beneath the “Knowledge” tab on “Get & Rework”:
As you possibly can see I already loaded FactInternetSales question from AdventureWorksDW2012.
When you’ve gotten a function as a default function in Excel you count on to have the ability to use VBA codes and Energy Question just isn’t exception. To see the way it works comply with the steps under:
- To begin with we want allow Developer tab in Excel 2016
- Go to File-> Choices-> Customise Ribbon-> tick “Developer”-> OK
- Now you possibly can see the “Developer” tab on the ribbon
- Click on “Document Macro”
- Enter a reputation for the macro then click on OK
- Go to Knowledge tab-> Get & Rework-> New Question-> From SQL Server Database
- Enter server and database then click on OK
- Choose a desk then click on “Edit”. (Clearly you possibly can immediately click on on “Load”)
- Observe the steps under to edit the question
- Take away the 5 first key columns by choosing them, proper click on and “Take away Columns”
- Filter “EndDate” column to point out the data with legitimate finish date
- Take away the final 7 columns as nicely
- Rename the three first columns to make them extra consumer pleasant
- Click on “Shut & Load”
- Return to the “Developer” tab and click on “Cease Recording”
- Now click on on “Macros” from “Developer” tab
- Click on “Edit”
- Right here you go, you need to be capable to see the VBA codes generated over Energy Question
You possibly can be taught rather a lot from the recorded codes. In case you have a have a look at the codes you’ll discover plenty of actually fascinating codes. You possibly can see all of the steps we took once we edited the question like renaming columns and eradicating columns and many others.. It’s also possible to see the SQL question which is used to retrieve the information out of the SQL Server database.
However, typically talking of Energy Question as compared with the newest model of Energy Question obtainable for Excel 2013 there isn’t a new options added to it.
One other cool function that’s been added to Excel 2016 is the flexibility to create “Energy View” report on high of OLAP cubes. Within the earlier model you get the error message under:
“Sorry, Energy View in Excel can’t connect with this information supply. It could be a multidimensional information supply, which Energy View doesn’t help but. Attempt connecting to a distinct information supply.”
However, within the new model you possibly can create flashy studies on Energy View very simply. Simply connect with an OLAP information server and import information right into a Energy View report.
Within the pattern under we create a report in Energy View on high of Journey Works OLAP dice:
Web Gross sales by Full Calendar Date:
To begin with I actually favored the adjustments on colors and relationship connectors on diagram view.
Now you possibly can rename tables and columns simpler by choosing the merchandise and urgent F2 which isn’t allow in Energy Pivot 2013. In Energy Pivot 2013 you need to double click on on the article to rename it. In case you’re a keyboard man or woman like me you’ll be joyful to have F2 enabled for renaming objects.
It’s also possible to rename a desk or a column that you simply added right into a Pivot Desk from the Mannequin with out loosing them from the Pivot Desk. As you possibly can see on the screenshot under I renamed DimDate desk to Date and I can see the adjustments straight on the Pivot Desk.
While you’re talking of Energy Pivot, there isn’t a approach to withstand speaking about DAX. There are misplaced of recent DAX features added to Energy Pivot 2016. All of those features are prepared to make use of in Energy BI Desktop as nicely.
You will discover new DAX features right here.
That was a quick abstract of what are new BI associated functionalities in Excel 2016.
Hope you loved it.
Associated
Supply hyperlink
