It’s been awhile that we’re ready for a wise enhancements in Microsoft self-service BI. The excellent news is that lastly there might be some cool new options added to the subsequent 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 healthier than nothing, not too unhealthy although!

Energy View and Energy Map:

As you recognize, Energy Pivot was built-in as a built-it characteristic to Excel 2013. Now I’m actually blissful 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) Information-> Allow Information Evaluation Add-ins: Energy Pivot, Energy View, and Energy Map

OR you may nonetheless flip them on from “COM Add-ins”:

File->Choices->Add-Ins-> Handle “Com Add-ins”-> Go

Now you may 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. Truly it’s there by default and also you don’t even want to show it on.

Yow will discover it below the “Information” tab on “Get & Rework”:

As you may see I already loaded FactInternetSales question from AdventureWorksDW2012.

When you’ve gotten a characteristic as a default characteristic in Excel you anticipate to have the ability to use VBA codes and Energy Question shouldn’t be exception. To see the way it works observe 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 may see the “Developer” tab on the ribbon
• Click on “Report Macro”
• Enter a reputation for the macro then click on OK

• Go to Information 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 may instantly click on on “Load”)

• Comply with the steps under to edit the question
  1. Take away the 5 first key columns by deciding on them, proper click on and “Take away Columns”
  2. Filter “EndDate” column to indicate the data with legitimate finish date
  3. Take away the final 7 columns as nicely
  4. Rename the three first columns to make them extra person 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, it is best to be capable to see the VBA codes generated over Energy Question

You may be taught rather a lot from the recorded codes. You probably have a take a look at the codes you’ll discover plenty of actually fascinating codes. You may see all of the steps we took after we edited the question like renaming columns and eradicating columns and so on.. You can too see the SQL question which is used to retrieve the info out of the SQL Server database.

However, usually talking of Energy Question as compared with the most recent model of Energy Question obtainable for Excel 2013 there isn’t any new options added to it.

One other cool characteristic that’s been added to Excel 2016 is the power to create “Energy View” report on prime of OLAP cubes. Within the earlier model you get the error message under:

“Sorry, Energy View in Excel can’t connect with this knowledge supply. It might be a multidimensional knowledge supply, which Energy View doesn’t help but. Attempt connecting to a unique knowledge supply.”

However, within the new model you may create flashy stories on Energy View very simply. Simply connect with an OLAP knowledge server and import knowledge right into a Energy View report.

Within the pattern under we create a report in Energy View on prime of Journey Works OLAP dice:

Web Gross sales by Full Calendar Date:

To begin with I actually favored the modifications on colors and relationship connectors on diagram view.

Now you may rename tables and columns simpler by deciding on the merchandise and urgent F2 which isn’t allow in Energy Pivot 2013. In Energy Pivot 2013 it is best to double click on on the article to rename it. In case you’re a keyboard man or lady like me you’ll be blissful to have F2 enabled for renaming objects.

You can too rename a desk or a column that you just added right into a Pivot Desk from the Mannequin with out loosing them from the Pivot Desk.  As you may see on the screenshot under I renamed DimDate desk to Date and I can see the modifications straight on the Pivot Desk.

Once you’re talking of Energy Pivot, there isn’t any approach to withstand speaking about DAX. There are misplaced of latest DAX capabilities added to Energy Pivot 2016. All of those capabilities are prepared to make use of in Energy BI Desktop as nicely.

Yow will discover new DAX capabilities right here.

That was a quick abstract of what are new BI associated functionalities in Excel 2016.

Hope you loved it.