Using PowerPivot with Problem Databases (Like M2M) Part 1

In my presentation at Consona Connect 2010, I showed users the wonders of Excel connections through ODBC, and what you could do with PowerPivot. However, I repeatedly mentioned to that group that I was glossing over lots of things in order to fit the presentation into an hour. This is part one of the series further detailing the process.

The problems… are not with PowerPivot.

First, if you haven’t already done so, please read the article which details these problems and how they motivated me to form Project M-Data Analytics in the first place.

The most difficult problem to deal with is the lack of primary and foreign keys. A primary key is a column or combination of them which represent a unique record in a table. Database tables are not required to have a primary key (obviously), but nearly all should, and a table cannot have more than one primary key.

For example, the Sales Order Master table (SOMAST), if M2M had been properly designed, would have one primary key, the sales order number (fsono). This helps to insure data integrity by preventing duplicate sales order master records from being created as well as protecting against null (or unknown) sales order number values as well.

Conversely, a foreign key is a column, or a combination of them, which refer to another table’s primary key column(s). The long and short of it is, records cannot exist in the child table that do not have corresponding records in the parent. In the Sales example, the Sales Order Item table (SOITEM) should have a primary key consisting of the sales order number and item number. The foreign key would be sales order number which corresponds to the identically named field in the Sales Order Master table. Obviously from my orphaned records post, M2M lacks this mechanism.

Suffice it to say that when you watch demonstrations involving the importation of data using Excel, PowerPivot, etc. and everything looks easy, the presenter is typically using a properly designed database with primary and foreign keys. A natural by-product of having these keys is automatic linking of the tables.

If you open a sample data warehouse database (similar to M-Data Analytics), select a sales table, and then click the following:

you’ll be rewarded with this:

… and there’s peace in the valley.

Incidentally, you can view an excellent video produced by Brian Knight on PowerPivot as part of the 24 Hours of Pass. You may need to register as a member of PASS, but it’s completely free and you should be a member by now anyway. 🙂 At about the 10 minute mark you’ll notice Brian say, “Now I don’t have to pick a data warehouse, I’m just choosing that in my case. So, I’m just doing that to make things much simpler on myself…” Now in this video, he actually selects the tables manually, but also indicates that PowerPivot would pull the relationships out for him automatically as I’ve shown above. Please don’t misunderstand me, I have a huge amount of respect for Brian Knight and I wouldn’t choose a database like M2M to demonstrate with either unless I had to.

Any idea what happens when you do this with Made2Manage? It’s about as effective as Paris Hilton on Celebrity Jeopardy. That is to say that Excel has no answer, because M2M lacks primary and foreign keys.

Further, because M2M uses those cryptic, and often misleading, field names you must replace those with readable English alias names each time. Otherwise, your executives are reading reports with fsono and finumber all over them.

It’s important to note that these are some of the reasons I founded M-Data Analytics and you will not have these problems when you use it.

However, using the normal M2M Database, these problems can be partially negated using relatively simple techniques such as database Views, which I’ll discuss in the next article.

2 comments to Using PowerPivot with Problem Databases (Like M2M) Part 1

  • Joshua

    Thanks David,

    I’ll definitely review this when I have more time later on today.

    I was really exiting about what I saw regarding PowerPivot in Vegas.

    Thanks again.


  • Joshua


    Correct me if I misunderstood. Are you saying that the automatic linking of tables in Excel/PowerPivot is useless because M2M’s doesn’t use primary and foreign keys?

    If the answer is yes. Is that on a table by table basis, or can Excel/PowerPivot sometimes create the relationship…automatically.

    Can’t wait for Part 2.

Leave a Reply




You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>