Connecting to an Oracle Database from Power Pivot

July 23, 2015

Guest Post by Andrew Todd

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! Smile

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!

DBA to the Rescue

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

Read the rest of this entry »

Consolidated Worksheets with Power Query

July 9, 2015

by Matt Allington

The more I use Power Query, the more I want to use Power Query – and of course the more I learn.  I have seen quite a few references on the Web to “consolidating multiple Excel Workbooks into a single table”, but I came across a problem working with a client recently where I had to undo someone else’s ‘helpful’ partitioning of data into separate worksheets in the one workbook.  You may have come across the problem yourself.  Someone has been collecting data over time, and they start a new sheet every month.


Well intentioned but actually quite unhelpful.

The procedure I use here to bring this all back together in a single table is the same basic concept as consolidating multiple workbooks, however I have learnt a thing or two since the first time I copied someone else’s pattern on how to do this.  Specifically I have learnt the correct and easiest way to create and work with Power Query functions.

The basic process/pattern is:

  1. Create a query to import a single sheet of your data.  Do all the transformations you need here.
  2. Convert the basic query from step 1 above into a function (the easy way) while keeping some backwards capability.
  3. Create a second query that uses the function from step 2 and consolidates all the objects (Sheets in this example), but the same principles apply for any other object.

There are other approaches you can use to do this (as is often the case with Excel) but this procedure demonstrates how easy it is to write Functions if you understand the basic concepts.

First Create a new blank workbook edit a single sheet

Go to Power Query Menu\From File\From Excel.  You will see in the navigator the list of sheets.  Just select any one of these sheets in the list.   For this process to work, the column structure of the sheets needs to be the same.   Read the rest of this entry »

Time to revisit the Power Pivot Import Wizard

June 11, 2015

by Matt Allington

I have learnt a lot over the last year working full time as a Power Pivot professional.  And some of the things that I dismissed as “not useful” very early on have turned out to be very useful indeed.  The second option in the Table Import Wizard is a case in point.

option 2

Very early on I dismissed this as being “not useful” for Excel users of Power Pivot (although I could clearly see the benefit for SQL professionals that could already write SQL code).  But I have to say I was wrong about this and I think there is a lot of value in this second option for Excel users.

I was re-reading the excellent book by The Italians over the weekend.  I find that reading quality books for a second and third time is a worthwhile and rewarding pursuit.  Often things that didn’t mean a lot the first time you read will have a new meaning and relevance the second or third time around.  Anyway when I got to the part about importing data from a database, there in front of me was reference to the “Design Button” which appears on the “less preferred option 2″ import screen.


I have been using this less preferred option 2 screen for some time now (read about other tricks I use here) but I have never noticed this button before.

So how do I use this button?

This button is very useful when you want to import data from a database but you don’t have access to an IT buddy to help you out by writing the SQL code you need. Now it is not the greatest user interface tool that you will ever encounter, but it is very do-able for the average Excel user. 

Let’s consider the following scenario using Adventure Works reporting DB for SQL Server.  In this example, I don’t want to bring in the full low level transactional data from the source database but instead I want to bring in a summary table for a defined period of time.

eg  Give me a table that summarises sales by day and by territory, and also how many invoices there were for the year (say 2003).  So I really want a table something like this to put into Power Pivot.


Here are the wizard driven steps to complete this task.

Go through the normal “Import from Database” steps as you normally would for Power Pivot.  Select your server name and database name in the wizard.


Select the previously unloved option 2

option 2

Give your query a name (this will be your table name inside Power Pivot) and then click the Design button.


Expand the table navigation pane on the left and select the columns of data needed.  In this case we want order date, Territory Key, Order Number and Extended Amount.


In the selected fields section (top right), click the “Group and Aggregate” button and then select the “group by” actions needed for each field.  We want to group by order date and territory key, then do a distinct count on order number and sum up the extended amount.  These are not difficult concepts for Excel users to grasp even if you don’t know database design or the SQL language.  “Group By” simply means “give me a sub total at this level”.


Finally you apply a couple of filters to limit the results to the calendar year 2003.


After you click OK, the Design Wizard returns the appropriately written SQL code that will be used to fetch the data you need from the database.


And this SQL code is not hard to read or understand.  Once again, well within reach for the average Excel user that is learning Power Pivot.  In fact you can even change the SQL code here (to change the returned column names as an example).

Change this part

,COUNT(DISTINCT fctSales.SalesOrderNumber) AS [Count Distinct_SalesOrderNumber]
,SUM(fctSales.ExtendedAmount) AS Sum_ExtendedAmount

to this

,COUNT(DISTINCT fctSales.SalesOrderNumber) AS [Total Orders]
,SUM(fctSales.ExtendedAmount) AS [Total Sales]


Then click Finish and finalise the import of your summary table.

From here you can go ahead and bring in more tables of data, or start working on your data model as required.

Personally I think any Excel user that is regularly importing data from a database should invest a small amount of time learning how to read and write the SQL language.  12 months ago I classed my SQL skill as “knows what it looks like, can read it, but not good at writing it”. 12 months later having used SQL quite a bit for Power Pivot data imports, I class myself as “competent”.  So if I can do it, you can do it too. And UI tools like the one above can help you get started, and you will be writing your own SQL code without help before you know it.  Then there will be no stopping you.

Matt Allington is full time Self Service BI professional based in Sydney Australia

Power Update Adds Email Notifications, Macro Support, and 100% Free Version

March 23, 2015

Post by Rob Collie

Power Update Now Emails PDFs or Full Power Pivot / Power BI Workbooks to Email Recipients

Autorefreshes Your Power Pivot Workbook as Frequently as Desired, Auto-Publishes to Any Location, and Now Sends Email Notifications of Success/Failures – With Attachments!

Our Gift to the Community:  New, Improved, and Free

The team has added several new features in the latest version of Power Update.  One of them (email notifications that optionally attach the updated workbook or PDF-ified version of the workook), is pictured above.

But the biggest new “feature” is that there’s now a 100% free version.  Go ahead and download it from the link below, and start using it today.

It will work forever – no trial expiration – and will never require payment.


(Alternate Location Here in case DropBox is blocked)

It will be installed and working in less than five minutes.  Have fun, and if you have any troubles, report those on the Power Update Forum.

“Why Free?  What’s the Catch?”

Simply put, we want everyone to have it.  Everyone.  It’s a game changer.  It will lead to more Power Pivot / Power BI adoption and overall goodness, which is very much something we want.

The only limitation in the free version is that it will only schedule one workbook. Every last feature is available – email, PDF attachments, publish to SharePoint and even SSAS Tabular.

So if you’ve only got one important workbook, you can use the free version forever.  A lot of people will run that way, and we’re ok with that.  If you someday end up with more than one workbook that needs refresh, you can opt to purchase the full version, which can schedule as many workbooks as you want.

“Wait, Can’t I Cheat That With Multiple Computers?”

Read the rest of this entry »

Tales from the Trenches: My personal experience with Power Update (by Tim Rodman)

February 10, 2015

Guest Post by Tim Rodman, currently blogging about reporting in Acumatica ERP @

***Update #1:  a Free Version of Power Update is now available.  More info here.

***Update #2:  There is now a forum for Power Update questions, located here.

Intro from Rob: I’m what you might call a “gift horse optimist” – strongly positive outlook, but when the hoped-for thing finally arrives, I find myself closely inspecting it, testing it, before I trust it enough to advocate it to others.  I went through this same process with Power Pivot itself – I “saw” its gamechanging power in 2010, but it was a full eighteen months before I finally dropped all disclaimers and just started calling it far better – period – than anything we’ve had before.”

Similarly, I’ve long known that Power Update would be a MAJOR win for us in the Power Pivot and Power BI communities.  But I am willing to advocate it now only because I’ve watched others – like Scott, and Tim below – use it successfully, in production environments, in recent months.  (Also see my post last week “introducing” Power Update in case you missed it).

Take it away, Tim…

I first found out about Power Update two months ago via a LinkedIn post by Christian Floyd.

It took me a while to realize that he wasn’t talking about a theoretical future idea, but an actual product, something that exists today. Click the picture below to see the entirety of my foolishness. It wasn’t until I talked to him directly that I realized what Power Update really was and I was immediately interested.


He got me a beta version of Power Update and I began testing it at the company I work for: a manufacturing company in Cleveland, OH called The Robbins Company.

Our Background

We started using Power Pivot at The Robbins Company back in 2013 and I wrote about our experience on this blog (click here).

Read the rest of this entry »

Introducing Power Update!

February 3, 2015

Post by Rob Collie

***Update:  check out Scott Senkeresty’s review of Power Update over on Tiny Lizard.

***Update #2:  a Free Version of Power Update is now available.  More info here.

***Update #3:  There is now a forum for Power Update questions, located here.

Power Update:  Refresh any Power Pivot / Power BI Workbook, from Any Data Souce, and Publish to Any Location (SharePoint or Otherwise)

A brand-new software utility designed from the ground up as
a “Companion” to  Power Pivot, Power Query, and the entire Power BI stack.

Definitely Click on the Image for Larger Version – Surprises Lurk Therein

Do Any of These Sound Familiar?

Common Problems with Power Pivot and Power BI Scheduled Refresh

Power Update Helps With ALL of These (And a Few More, Too)

“What IS It?”

OK, a few things:

Read the rest of this entry »

Creating dynamic lookup-tables with unique values using Power Query instead of a database

March 4, 2014


Guest post by Lars Schreiber

Hello PowerPivot Community,

I’m quite sure most of you know a scenario comparable to the one in the following figure:


Bringing two fact-tables with different date-columns together using a 'unique-months'-dimension

Two scenarios (in two tables) – in this case actuals and budget – have to be put together to do some math on them. The only problem you have is the different level of planning regarding the time dimension. While the actual figures are on daily basis, the budget was planned on monthly basis. As you could learn on this website many times before (e.g. here) you need another lookup-table with a unique list of months to bring both tables (actual & budget) together. And this is where Power Query can help you a lot.


Read the rest of this entry »

What Excel users could learn from Masterchef or why preparation is so important!

August 22, 2013

Intro from Rob:  In the car today I realized two things.  One, I have been a terrible host.  Multiple guest posts got stuck in the pipeline while I focused on PowerPivotPro School.  Much effort went into these articles and I apologize to all of you who have been waiting patiently to see them shared.  My original post idea for today will wait.

Today we start with Anne Walsh, who you will see employs a very different “lens” than the one typically used by yours truly.  Except for her movie and pop culture tie-ins – those are quite consistent!

The 2nd realization was triggered by Anne’s references to Ratatouille.  Remember the line “anyone can cook?”  And how that rankled the elitist chefs?  There’s a strong parallel there in my belief that “anyone can business intelligence.”  As long as they savvy Excel, that is :)  Onwards…

What Excel users could learn from Masterchef or why preparation is so important!

By Anne Walsh




Colette: [Linguini is making a mess at the kitchen] What is this? Keep – your station – clear! When the meal rush comes, what will happen? Messy stations slow things down. Food doesn’t go, orders pile up, disaster! I’ll make this easier to remember: keep your station clear, or I WILL KILL YOU! (Taken from Ratatouille)


Fruit stall | store in Tai O, a fishing town located at western side of Lantau Island in Hong Kong.

Taken by Denis Won


Before we begin, let me point out that while I respect Colette’s fierce commitment in Ratatouille, I am not espousing her approach to her co-workers!  But if you have ever had to start doing something that required a well organised list only to find that it was actually all over the place – you may have some empathy for her..

In this side of the world, (Europe), we have a programme called Masterchef (UK and Ireland)  in which amateur cooks are asked to prepare dishes which are then evaluated by two master chefs.

However I always notice that a key part is glossed over. The chefs work in an environment with great well prepared ingredients and top notch equipment. You don’t see them running out because someone forgot the vanilla essence…

Alas in the Excel world, we start well before that. We usually don’t have any minions to get our saucepans, knives and source perfectly shaped tomatoes. Usually we have to assemble our lists (often with blemishes and long gone sell by dates) so this is where I want to start. We have to go to the market to get the ingredients, and prep them.

Read the rest of this entry »

New Version of Data Explorer Released

March 26, 2013

Quick post today, as I am travelling again.  PowerPivot’s version of Tyler Durden Smile

If you installed Data Explorer a couple weeks back, go look at your Data Explorer ribbon tab today:


This “Update” Button “Lit Up” Last Week – Click It Smile
(I already did, so mine is now disabled again)

That will launch you into a surprisingly painless update.  Pretty nifty.

New Features

They added support for a few new data source types:

  1. Azure HDInsight – the MS cloud version of Hadoop
  2. Teradata
  3. PostgreSQL

They also improved the query editing experience.

Full details here:

Download it From Scratch

If you haven’t downloaded data explorer yet, you can get it here:

What the Heck is Data Explorer?

Read this quick post from two weeks ago

“Introducing” Data Explorer

March 7, 2013


In My Opinion, There Aren’t Many Things More Exciting Than a New Ribbon Tab Full of Goodies
(And One That I Can Add to Excel 2010 or 2013 – I Hear That’s Important for Some Reason)

Maybe There’s a Future in this “Numbers” Thing…

It does seem like Microsoft has figured out that data is a big deal.  Every time I turn around, I am hearing of a new software development team joining Microsoft’s efforts in the Excel/BI/Overall Data Crunching space.

Often, such teams are merely whispers – shadowy rumors on the wind.  Friends disappear from their familiar roles and reappear in places they can’t talk about.

Other times, someone new to me walks up and just hands me a piece of nearly-finished software.

This is one of those latter cases.

Some Highlights

I don’t have time for a full tour today, and honestly I haven’t even explored all of the functionality yet.  So let’s hit some highlights shall we?

Read the rest of this entry »

Friday Bonus: Database Pros In Demand

January 4, 2013


In a PowerPivot Environment, A Single DB Pro
Can Empower MANY PowerPivot Pros

Short Version:  Job Openings in the UK for SQL Pros

I’ve recently been asked to post about some opportunities for SQL pros in the UK – opportunities in which qualified candidates would be working closely with Excel Pros in a PowerPivot environment.  A situation that closely resembles what it’s like to work as a SQL pro at Pivotstream, or as a SQL pro working at virtually any of my training clients (who end up implementing similar collaborative systems).

Scroll to the end of this post for the details, but first, I want to provide some context – both for interested candidates as well as for all readers.

Better With a Database:  a Recurring Theme

For a long time now, I’ve been saying that you can get a LOT more out of PowerPivot if you pair it with a database.  For reference, see the following posts:

  1. Self Service BI Doesn’t Mean Desktop Standalone BI (one of my most popular all-time posts, at least in certain circles)
  2. Better With a Database Part One
  3. Better With a Database Part Two

The human element, and why “anthony” is becoming a noun

Stated even more clearly, an Excel Pro can achieve much better results when they collaborate with a Database Pro.  (Yes, sometimes they can be the same person, but generally speaking, the skillsets are distinct).

Read the rest of this entry »

Sign Up With Connection Cloud, Get My Book for Free

December 14, 2012


Connection Cloud is Offering to Buy You a Copy of My Book, Click Image for More Info

The cloud data connection service I talked about and demo’d in yesterday’s post is offering to buy a copy of the book for anyone who sign up with Connection Cloud by January 15th.

A flattering gesture on their part, this is definitely a first for me Smile

Excel Pros – the world is waking up to our importance.