SQL For Excel Power Pivot Users

SQL for Excel Power Pivot Users

Today I am going to spend some time trying to convince (and demonstrate to) Excel Power Pivot users that it is easy and valuable to learn some simple SQL code.  SQL is one of the easiest languages I have ever taught myself and you can do it too.  The reasons SQL is easy to learn include: It has a very simple syntax that uses English language expressions. You can start with simple short queries and get more complex only when…

Read the Rest
Sort Your Data On Load For Improved Compression

Sort Your Data on Load for Improved Compression

My last 2 blog posts have been about data compression – that theme continues today.  In December I blogged about the basics of compression and impacts that columns have on file size and did some testing with randomised data.  In January I went a bit deeper and talked about restructuring your data to get better compression.  In the comments section of this last post in January there was a seemingly innocuous comment from Mati Selg at the bottom of the…

Read the Rest
Restructure Your Data Table For Improved Compression

Restructure Your Data Table for Improved Compression

I was relaxing during my vacation thinking some more about column compression in Power Pivot.  One of the main things to know about compression is that a high level of cardinality is your main enemy (ie a large number of unique values will mean poor compression).  I started to think about how I could reduce the cardinality of one or more columns in a large data table I use for a client.  This blog covers the process I went through…

Read the Rest
Quickbooks Analysis Via QQube!

Quickbooks Analysis via QQube!

Power BI Desktop View of Our Clients by State (Map) and Breakdown by How We Help Them (TreeMap) (Yes, this is Quickbooks Data in Power BI, and it was Painless. See Below for Examples Using Power Pivot Too) Too Legit to Omit (the analytics, that is) For a couple years now, we’ve been quite a bit bigger than the two-person operation of our early days.  Like I mentioned in a previous post, there are now somewhere between 15 and 20…

Read the Rest
Connecting To An Oracle Database From Power Pivot

Connecting to an Oracle Database from Power Pivot

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’?

oracle

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

Consolidated Worksheets with Power Query

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.

image

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
Time To Revisit The Power Pivot Import Wizard

Time to revisit the Power Pivot Import Wizard

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

Read the Rest

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

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