Archive for SQL

Use Simple SQL to Manage your data in Power Pivot

When I started learning Power Pivot, I learnt there are 2 ways to import data. There is the “standard way” that uses the table import wizard – good for us Excel folk. There is a second way that uses the Query Editor to write some SQL code and extract the data you need direct from the database. The problem is that you need to know SQL to be able to do this. So right from the start I decided this second approach wasn’t a very useful tool for the typical Excel user.
import choices

Recently though I have come across some real life challenges that I needed to solve. I have a very large “master” workbook that has all my measures and all my data (for several years) loaded – it is almost 400 MB and contains more than 40 million rows of data. I keep a master copy so I don’t have to re-write all these measures every time I have a new request for a report. But on the flip side, if someone wants a report that say contains just 10 products, I don’t want to send them the entire 400 MB workbook. Read More

DAX Studio 2.0 released today

Great news for Power Pivot for Excel users – DAX Studio has had an update over night and version 2.0 is now available. For those of you that don’t know DAX studio, it is an ADDIN that you install for Excel 2010 or 2013. It connects to the Power Pivot Data Model and allows you to write DAX code, query the data model and also query the DMV (which means you can get a list of all the Measures and Measure formulae in your data model). DAX Studio 2.0 is worth downloading for that reason alone.

How to Install

You do not need to uninstall your old version.  Just download the new version from here, close Excel and run the install.  Install DAX studio into the same location as your original version – I just used the defaults and all worked fine.  Once you start up Excel, you will find the same Icon on the Addin tab will work as before, but launch the new version.

If this is the first version you have installed, you first of all need to enable the Addin.  Do the following in Excel

  • Goto FileOptionsAddins
  • Go to the bottom and find “Manage Excel Addins”
  • Change the drop down box from Excel Addins and select COM Addins instead
  • Click Go.

Once you have done this, there will be an Icon on the Addin Ribbon in Excel.


What’s New and Cool

User Interface

The most obvious thing is a new user interface.  It is very nice and clean.  It now supports Hierarchies and KPIs in your data model, which was not the case before.

Read More

Problems Importing Access and Excel 2010 into SQL Server 2012?

Edit 10 Dec 2015: This blog post was written to solve a problem with SQL Server import drivers, however the process of addressing the issue should also work (in reverse) for people that are running 32 bit Office and want to install 64 bit Power BI Desktop.  If this is you, read on and you will see the process to fix the problem.

Since becoming a PowerPivot Professional, I have broadened my skill base to include the use of SQL Server.  I very quickly found that MS Access has a 2GB file size limit and hence I installed SQL Server on my PC.  It was a bit scary to start with, but once you get it installed and get started it is actually a fabulous way to go.  I would never go back now.  Anyway, I have had A LOT of issues with file compatibility when importing Access and Excel 2010 files into SQL Server.  It took me months to find a solution, mainly because I actually didn’t understand what the problem was – until now.

First an explanation of the problem – Office 64bit

Any serious PowerPivot user will already know that you really need Office 64 bit version.  PowerPivot will work with 500k – 1m rows of data in the 32 bit version (more or less, depending on your data) but if you want to get serious (ie many millions of rows of data), then you need to move to 64 bit.  The reality is that Excel with PowerPivot is THE ONLY Office program that needs 64 bit.  Now the problem is that there are lots of plugins and software that are compiled to work only with the 32 bit version.  That is why IT departments hate 64 Bit Office so much.

So the problem is as follows:

Read More