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.

designbutton

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.

summary

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.

image

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.

image

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.

image

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”.

image

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

image

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.

image

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

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

to this

SELECT
fctSales.OrderDate
,fctSales.SalesTerritoryKey
,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.

GET THE FREE VERSION HERE

(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 @ www.AcumaticaReports.com

***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.

image

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_ratatouille

 

 

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)

http://www.imdb.com/title/tt0382932/quotes?item=qt0465201

 

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:

image

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:  http://blogs.msdn.com/b/dataexplorer/archive/2013/03/12/a-new-build-of-data-explorer-and-an-auto-update-feature.aspx

Download it From Scratch

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

http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx

What the Heck is Data Explorer?

Read this quick post from two weeks ago


“Introducing” Data Explorer

March 7, 2013

 
image

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

 
image

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

 
image

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.


Cloud Data Approaching Critical Mass: Connection Cloud, SalesForce, PowerPivot, & Webinar on YouTube

December 13, 2012

 
image

Cloud Data Like SalesForce Available to PowerPivot as if it Were in a Local Database:
My Long Wish for a “Data Highway” Gets Closer Every Day
(Click for the Webinar Featuring Yours Truly on YouTube)

Flashback 2001:  The “Data Highway” Concept

Back at Microsoft in 2001 when I was working on what eventually became Excel 2003, I pitched a vision that I called “Data Highway.”  (OK, not an original name considering the Information Superhighway thing coined by Internet inventor Al Gore, but invention is smart and theft is genius, or something like that.)

The idea behind Data Highway was simple:  all relevant data made available to the most popular tools (cough cough Excel), in a convenient and refreshable format.  No manual gruntwork required to “fetch” data in other words – saving your brain for actual thinking.

imageThere were three elements to the pitch:

  1. A common internet protocol for exchanging data. 
  2. “Teaching” Excel, Access, and other tools to consume any data source exposed via that protocol.
  3. A marketplace for data where providers like Dun and Bradstreet could sell data to be piped straight into Excel.

Well the protocol flopped and our VP killed the marketplace idea before it got off the ground.  Having good ideas isn’t enough – you can’t be too early, and you also need to execute better than we did.

Fast Forward to Today

Here we are at the end of 2012, and we have all three elements available in different (but robust and real) forms:

Read the rest of this entry »


Webinar Wednesday on Cloud-Based Dashboards

December 4, 2012

image

Today’s “post” is actually going to be a series of small updates/announcements.

Connection Cloud is one of our new partners at Pivotstream.  They do something amazing:  they make your cloud data sources like SalesForce and Facebook look like normal databases.

Which means PowerPivot can connect to those sources and pull data just as easily as you pull data from a database like SQL or Access:

Read the rest of this entry »