Announcement Update: For those who missed out on the webinar Watch the Session Recording for my (Avi Singh) webinar. Click here to watch the session recording of all PASS BA webinars. Download the PowerPoint Slide-Deck, Power BI Desktop (.pbix) file,…

Cube Formulas: Ultimate flexibility for your reports but lots of drama to update…

A while back, I was tasked with finding a way to automate upkeep of a scorecard built primarily with thousands of cube formulas (Yes, thousands!). This particular scorecard was still under development, and maintaining and making changes to it had become a full-time job! All of the individual cube formulas needed to be updated several times a week, and this was expected to go on for months as executives made up their minds on the final product.

Fortunately, I found two tricks that allowed me to:

a.) Change slicer references in all cube formulas with a single click

b.) Modify entire tables across multiple sheets in seconds

These two tricks freed up time that I used to drive further improvements and start performing real analysis, rather than just maintaining a report.

Cube Formulas: Flexible and Powerful

Cube formulas allow you to add PowerPivot/SSAS Tabular calculations to any cell in virtually any orientation that you can think of. They’re a big part of what makes Excel simply the world’s best data tool, period.(Imagine if you could use them in Power BI Designer!)

The flexibility of cube formulas is powerful, but it does carry a price. Cube formulas are worksheet functions, so they bring their ‘worksheet function drama’ with them (lack of portability, unique formulas, individual updates, etc).

Here are two tricks to keep your formulas easy to maintain and update!

Cube Formula Trick 1: Consolidate Your Slicer References!

The first thing I noticed when opening my scorecard was the length of the cube formulas! I clicked on a cell containing a CUBEVALUE() formula and was greeted by multiple rows of slicer names in the function bar. The scorecard had a total of 10 slicers, and each needed to be referenced in the formulas. What’s more, not all of the slicer names in the scorecard were to be included in the calculations and yet more slicers were set to be added in the future!

Intro by Avi: We’ve been excited about all the new functionality that Power BI Desktopbrings to the table. Bi-Directional Relationships is one of those new features.

Before this (and still for the users of Excel+PowerPivot), you could force a relationship filter to flow “uphill” and implement many-to-many relationships. However that was done inside your DAX measures (click to read a detailed how-to). Now, with Power BI Desktop, that can be done automatically via bi-directional relationships. Andrew shows us how, using some Dynamics CRM data…

Dynamics CRM: Want to find out which sales calls resulted in a sale? You can with Bi-Directional Relationships in Power BI Designer

Note: Download the example .pbix at the bottom of this this post, which includes the above dashboard, example bi-directional relationships setup and showcasing other new features in Power BI Desktop

Lookup tables in PowerPivot are like reservoirs holding torrents of instructions poised to break free at the click of a slicer tile. When a user clicks on a slicer connected to a lookup table, they open a flood gate and instructions are unleashed to flow downhill to data tables.

In Power BI Desktop, filters can defy gravity! Not only can filters flow downhill from the reservoir into the data tables… they can also flow uphill from data tables to lookup tables! Thoseinstructions flowing uphill into the lookup tables can then spill over to data tables on the other side of the lookup table!

Driving Sales Activity Metrics from the Back Seat

With filters flowing uphill, the filters from the data table side of the relationship can be sent back to the lookup table. In a sense – those filters can actually flow right through the lookup table and down to data tables on the other side! The lookup table itself is filtered and the context ‘splashes’ over to the other data tables.

Datazen is a great visualization tool that is free to Microsoft SQL Server Enterprise customers. If you haven’t worked with Datazen yet, check out this post by Rob and Chris! Datazen allows you to design an awesome dashboard before you even work with any data! You simply draw a visualization, and then Datazen reverse engineers the exact layout of the aggregate table that you need in order to make that visualization work.

If you already have a PowerPivot model that you use for reporting, naturally you’ll want to use it to build visualizations in Datazen rather than reinvent the wheel entirely. One way to incorporate your dozens or even hundreds of measures and model logic (not to mention dozens of hours) into Datazen visualizations is through DAX queries.

Two Paths to Datazen DAX Query Nirvana…

Ok, you’ve built a fantastic BI dashboard in Datazen and your chakras are perfectly aligned. Now, there are two enlightening paths that you could take to build tables for Datazen visualizations with DAX queries:

A) Create a new ‘Data Connection’ in Datazen to your PowerPivot model hosted on SharePoint/SSAS Tabular, using the Analysis Services Data Provider. You can automate the refresh of data with SharePoint/Power Update and in Datazen itself.

B) Query your Power Pivot model using DAX Studio and save the workbook to a One Drive folder, then import the data into Datazen. This process can also be automated using Power Update.

Let’s take a closer look at both of these methods, starting with a SharePoint hosted Power Pivot model.

Intro by Avi: Andrew has been a part of PowerPivotPro family for a while now and has been doing wonders for the clients he has been working with. You have probably heard Rob talk about the “Data Gene”, well Andrew is the ideal specimen for that breed.Recently I had worked with a client where we spent quite some time just trying to figure out how to connect Power Pivot to their Oracle database. Andrew here lays it all out in simple step by step approach. Andrew’s got a lot of wisdom to share and you should all look forward to more posts from him. Take it away Andrew…

Oracle Databases

The amount of data around us is staggering. Statistics citing the amount of data being generated daily describe it in terms of exabytes (1 exabyte is 1 BILLION gigabytes!) In fact, the amount of data generated daily now exceeds the entire storage capacity of the entire world in 1986!

In a world with that much data, we’re lucky to have tools like Power Pivot and Power Query that offer easy connection to data sources that just sort of … clicks! However, if you’ve tried connecting to an Oracle database in Power Pivot you might have had some trouble. Fear not, here’s how you can make your Oracle database connections click just like all the rest!

Why aren’t my Oracle connections ‘clicking’?

For Excel pros, Oracle databases are a bit different, because there are a few key connection enabling components that are not automatically included with most users’ setups:

1.) Connection details – for Oracle databases, the connection details are included either in the connection string itself (EZConnect) or in a flat file called TNSnames.ora. Don’t worry, it isn’t as complicated as it sounds!

2.) Oracle data providers – You can think of these as the communication link between Power Pivot and the Oracle database.

Make a Call to your DBA for some details!

Before you attempt to connect to your Oracle database, you’ll need to contact your friendly Oracle DBA for the following information: