Using a Domain to Cleanse a SQL Table in DQS

Last time I showed how I can manually create a domain that could be used to clean SQL or Excel data.  Did anybody try using it?  I modified the AdventureWork Department table by making a few changes to the group names.  I then performed an interactive data cleansing using my new department group domain.  My results follow:

ManualDomain21

Looking first at the tabs, I can see that I had 4 new values (the number in parenthesis after the tab name is the count) that my group name domain did not know anything about.  I also had 6 values that were correct as found.  The corrected tab tells me that five records were corrected.  This is the tab that I opened in the previous figure so I could see what information was corrected.

The first column shows the original value found in the source table.  The second column tells me the number of records that had this value.  In this case, each value occurred only once.  However, if a value occurs multiple times, rather than displaying multiple lines with that value, DQS shows only unique values in this grid but tells us how many times that value occurs in the source table.

The third column, Correct to, shows the new value assigned to the current value.  You can go back to last week’s blog to see that these were the values defined as the leading values in each of the individual synonym groups.  In fact, the fifth column tells us that the reason for the correction was to set the value to the leading value.  Because these are direct matching replacements, DQS’s confidence in these substitutions is 100%.

Finally the last two columns contain radio buttons.  As with any set of radio buttons, I can only one button in a set.  In this case, a set consists of an Approve and a Reject button.  By default, DQS preselects the Approve buttons.  However, if I wanted to reject any of the replacements, I could simply click the Reject button for that row and the update would not occur.  Note that by clicking the Reject button, I do not have to separately deselect the Approve button.  The deselection is automatic as soon as I click the Reject button.

That data cleansing process fixed many of the records that had values different from the leading values.  However, I still have a few new values which I can view by selecting the New tab.

ManualDomain22

These values appear to be acronyms for the group names.  To provide corrections for these values, I can click in the row for any of the values.  For example, if I click in the QA row, a second grid appears below the first one as shown in the following figure that displays all of the rows that have this value along with other column values.  In this case, because the abbreviation occurs only once, the second grid only displays a single record.  However, in other scenarios, there could be many possible rows that have this value.

Why display a top grid with unique values for the group name and a second grid with a separate row for each instance in the table where that value occurs?  If I wanted to always change QA to another value such as Quality Assurance, I could simply click in the third column of the row with the value of QA and enter Quality Assurance and click the Approve button.  When I click the Approve button in the upper grid, DQS makes the changes to all records that have a value matching the value in column 1.  It then moves the record from the New tab to the Corrected tab.

Alternately, I could update individual occurrences of the source value by clicking on the Approve button associated with specific records in the lower grid.  While this may not be common, it can occur when an acronym appears to be the same for two different expanded names such as the Central Intelligence Agency and the Culinary Institute of America. In this case, I could update individual records in the lower grid by select the Approve button for only selected rows after enter a corrected value in the upper grid.  After approving some of the records, I could change the corrected value in the upper grid and approve the remaining records in the lower grid.

ManualDomain23

After correcting all of the abbreviations and clicking Next, DQS shows a final screen as shown below.

ManualDomain24

The left panel shows the original columns from the input source along with the new GroupName_Output column.  You can see that the data now has consistently named groups that can better represent what each groups is doing in a pivot table or pivot chart.  In the right panel, I can save the data back to SQL Server.  I could replace the data in the existing table by selecting the same table name as the data source as shown in the next figure.

ManualDomain25

DQS prompts me whether I really want to replace the existing data, but at least I can update the existing table with the new data.  Some of you may prefer to create a new table so that you can go back and forth between the original table and the new table to verify the corrections.  Note also that replacing an existing table will also remove any custom property information.  Alternately, you see the Output Format option has two radio buttons, Data Only and Data and Cleansing Info.  If you export with the Data and Cleansing Info option selected, the resulting table or Excel spreadsheet will include columns that show you both the before and after values as well as the correct reasons.

ManualDomain26

After you gain confidence working the DQS, creating that extra table may not seem as important, but while you are getting accustom to working with DQS, I would recommend placing the corrected data in a new table.  Again remember, that if you replace a table, you may need to reset properties and indexes on the ‘new’ table.

ManualDomain27

After writing the data back to the database, DQS display one final confirmation dialog:

ManualDomain28

Next time, we will take a look at how to replace common abbreviations even when they are embedded in a larger string value rather than creating synonyms for each value in which they occur.

Until then, C’ya.

Remembering

It has been almost two weeks.  In some ways it is hard to believe that you are gone.  We were married for 12,577 days.  Remember?  I keep hoping that it is all a dream and I will wake up to see you again and be with you.  But I know it is not a dream.  At night I still fall asleep within seconds, like always, but more from exhaustion than anything else.  Then I wait up around 3 AM and cannot get back to sleep again because I’m remembering.

I remember when we first met back in 1977.  Do you remember?  I was driving for my old house at Lehigh University bringing in a carload of girls from Kutztown College. (It was only a college then.  Now it is Kutztown State University.) Lehigh had gone co-ed a few years before, but the number of women was far outnumbered by the number of men.  So we would bring girls in from neighboring schools for parties.  When I picked you up along with a couple of your friends, I could not stop looking at you and talking with you.  Somehow I knew from the start that you were special.

I remember our first real date two weeks later.  We went to a hockey game in Hershey, PA.  Remember?  Over the subsequent months we spent a lot of time together and it soon became apparent that we would spend our lives together.

I remember the day I asked your father for your hand in marriage.  I remember going out to dinner that night and asking you to marry me.

I remember our wedding day like it was yesterday.  I remember your dad walking you down the aisle.  Remember I told you that I had a dream shortly after we met where I saw you walking down the aisle in that church which I had never seen before the first time we went there months later.  I always believed it was destiny.

I remember our honeymoon in Hawaii.  Do you remember driving down the main street of Honolulu the first night and I could not figure out how to turn the high beams off on the rental car?

I remember moving into our first house in Macungie.  I remember our first Christmas.  I remember when we moved to Reading and we built our first house.  Remember Paulo, our builder who lived across the street?  How about our first Christmas in Reading.  That Christmas eve it started to snow and snow and snow.  It was awesome.  A few weeks later it snowed again.  So much snow that we were all out shoveling the snow off the street.  Then we found out that the township would not plow our street because it was not ‘finished’.  Remember when Paulo organized the residents of the entire street to go to the township meeting to protest their refusal to plow the street and the resulting refusing of the garbage truck to go through the street to pick up garbage.  Remember how Paulo asked the township commissioners where they lived because he had a big truck and he could deliver all of our garbage to their houses so it could be picked up.  They plowed our street the next day.

Then came the day we found out you were pregnant.  It was such a happy day.  However, it was soon followed by your mother’s illness and hospitalization.  She spent the next 8 months in intensive care on a breathing machine.  You missed having her help during this time.  But you delivered us a healthy baby girl.  We could not take Natasha into the hospital to show her to your mom, but we did take pictures in.  Shortly after seeing the pictures of her granddaughter, your mother died.  Happiness mixed with sadness.

Remember our friends, Cathy and Jeff.  Remember all those nights and weekends we worked with them preparing food for the church carnivals.  They were and still are two of our best friends.

Remember when Natasha first started going to school.  You use to take her to the school bus stop in the morning and pick her up at the stop in the afternoon.  Then one day she did not get off the bus.  You called me at work, panicked.  I raced home and we searched everywhere, even going back to the school to see if she did not get on the bus.  While there, the school got a call from the bus driver reporting that Natasha was still on the bus.  Seems that she hid beneath the seat because she wanted to see where the bus went after it dropped all the other kids off.

Then we moved to Orlando, FL.  Remember driving two cars down using walkie-talkies to keep in touch.  Cell phones were still rare and they were huge.  They were more of a status symbol then.  Remember how people would proudly place them on tables at restaurants as if to say look how important I am?  Now we all have them.

Remember all the good times as Natasha was growing up?  The school events? The birthday parties?  Girl Scouts?  The vacations?  You even got me to go on a cruise… twice.  We were so proud when Natasha graduated high school, when she was accepted to college as a pre-Pharmacy student.  Remember her first graduation with her undergraduate degree in 2010.  You had just been diagnosed with cancer the previous October and your greatest fear was that you would not live to see it because the doctor only gave you 3-6 months to live back then, but you did live to see her graduate.  You fought a hard battle just like your mom.  You fought hard enough to be able to celebrate our daughter graduating from Pharmacy school with her PhD last June, almost three years after your first diagnosis.

There was so much more you wanted to celebrate.  So much more that you will never get to see, at least not from this life.  You made me promise to watch over Natasha and I suspect you made Natasha promise to watch over me because we are all we have left.

We had your body cremated just like you wanted.  We found a really nice urn.  It does not look like an urn.  It is more like a Tiffany box.  We know how much you liked Tiffany lights and the Tiffany shades we had for the living room lights.  Actually Natasha spotted it and thought you would like it.  We also got what they call a Keepsake.  It is a small butterfly about 5 inches tall standing on a small rock that contains a few of your ashes.  The butterfly’s wings are also Tiffany glass.  If it is ok with you, we would like to think of those wings as the wings of the angel that you now are.

Later today, I will stop by the funeral home to bring you home.  I have a spot on the fireplace mantel all set aside for you with a picture of your parents on one side and a picture of Natasha and me on the other.  You will be with family again, like you always wanted.

We will always love you and remember you Susan.  Forever.

Manually Building a Domain for DQS

Manually Building a Domain for DQS

This week I return to my series on using DQS, Data Quality Services, which is a few functionality found in SQL Server 2012.  I previously provided an introduction to DQS on 01/26/2013 through 02/23/2013.  So if you did not read those blog entries, you may want to start there.  Anyway, for the rest of you, I want to emphasize that learning to use DQS is essential if you are serious about doing BI Analytics.  In fact, with modified sample data from AdventureWorks on which this discussion is based, I will show you how ‘bad’ data can make interpreting your pivot tables and pivot charts more difficult, and how data cleaned with DQS simplifies and clarifies your results.  So let’s get started.

Let’s say I create a modified version of the AdventureWorks HumanResources.Department table called simply Dept which has the following set of records:

ManualDomain01

Notice that the sixteen unique departments are actually grouped together with the GroupName column.  However, to make this example interesting, I changed some of the GroupName values so that while similar, they do not exactly match each other.  For example, the following set of GroupName values probably should all be set to Research and Development.

  • Research and Development
  • Research & Development
  • R&D

Similarly, similar related group names appear for Executive General and Administrative, Inventory Management, Quality Assurance, and Sales and Marketing.

Let’s create a domain for our department group names so I can define correct values and then use that domain to clean new datasets.

Open the Data Quality Services Client as I described several weeks ago so that the main dialog appears as shown below.

ManualDomain02

I could create a new knowledge base for our new data domain, or I could add the department domain to one of the existing knowledge bases that I previously created.  In this case, let me choose the previously created Customers Knowledge base.  I can do this either by clicking the Open Knowledge Base button from the above main dialog and then select Customers from the list of available knowledge bases followed by selecting Domain Management under the Select Activity and then click the Next button as shown in the next figure.

ManualDomain03

Or I could simply click on the Customers (Domain Management) link at the bottom of the first column on the main dialog.  Either way, I will next see the Domain Management screen shown below.

ManualDomain04

In this dialog, I can see the domain created a few weeks ago called CustomerNames.  This week, we want to create a new domain in the knowledge base.  To do that, click the first of the icons on the left above the Domain list.

ManualDomain05

The Create Domain dialog, shown above, prompts me for a name for the new domain along with an optional description.  One of the first properties is the domain type.  For the GroupName, I will keep the default data type: String.  I also need to choose Use Leading Values.  Earlier I said that several group names have multiple similar values.  Each of these values will be defined as synonyms.  I could choose to allow each of the synonyms in the final corrected output dataset.  However, if I want to use the group name to aggregate other data using a pivot table or pivot chart, have multiple values that mean the same thing will make interpreting the results difficult.  Instead, I want to choose a leading value for each set of synonyms to replace all of the other synonyms.  Therefore, I need to select this checkbox.

The Normalize String option allows me to ignore any punctuation in the values.  For example, the string ‘Inv. Mgmt.’ would be treated the same as the string ‘Inv Mgmt’.  While I may not have that problem in my test data set, it could occur in other data sets that I may want to clean so I will check this option.

The Format Option allows me to pass values through as is without any change.  I could also change the string to all upper case, all lower case, or I could simply capitalize the values.  If I choose English as the language, I can use the built in spell checker that is essentially the same as the one found in other Microsoft products such as Word to correct misspelled words for me.  Afterall, I would not necessarily want to include as synonyms every possible misspelling of a value.  However to use the spell checker, I also have to click the next check box, Enable Speller.

Finally, I can also disable syntax errors which I’ll save for a future blog discussion because it really doesn’t apply in my example.

Next, rather than use an existing table to discover possible values as we did with Customer Names a few weeks ago, I am going to build the domain manually by going to the Domain Values tab and selecting the icon to Add A New Domain Value.

ManualDomain06

I’ll start by adding my first value: Executive General and Administration

ManualDomain07

Next, I add the first of the synonyms as a new domain value.  In fact, I continue to add all of my synonyms as shown below.  I then select all of the rows and right click on the selected rows to display a popup menu of actions.  From the actions, I select Set as Synonyms defining all four values as related values.

ManualDomain08

By default, DQS lists the synonyms alphabetically and makes the first item in the list the leading value.  Remember that a leading value is the one that all the other synonyms translate into.

ManualDomain09

But as is often the case, the first item alphabetically is not the value I want as the leading value.  To change the leading value, I right click the row I do want, in this case: Executive General and Administration and select the action: Set As Leading.

ManualDomain10

DQS shuffles the list of synonyms to put the leading value at the top of the group.  I now have a set of synonyms that I can translate to a single leading value.

I can do the same thing for all the other group name values so that my final list of group names looks something like the following.

ManualDomain11

I’m not done yet defining this domain, but I will stop here for this week and I pick up the rest of the definition and show you how it works next time.

Until then, C’ya.

Wisdom, Not Brilliance, Will Make a Difference

Ok, it has been a tough week for me, but I need to get back into things.  I recently was catching up on listening to some of my older webcasts.  One in particular from back in early 2009 caught my ear and I thought I would share some of the insights I got with you.  The specific webcast was part of the TED Talks series and was presented by Barry Schwartz.  It was a very passionate presentation because Barry really believes in the  importance of his topic.  However, I know people who would call him angry and mad, unable to control his emotions, and danger to have around ‘real’ people.  Unfortunately, these people don’t understand the difference between passion and anger.  That is too bad, because passion is often what drives progress.  Well, let’s see what Barry says.

Barry began by talking about the typical job description.  Most jobs simply list the things that the individual should or sometimes should not do.  They rarely if ever go into interpreting the way the person in that job should interact with others.  Oh sure there are simple comments like the employee should work well with others, but what does ‘work well with others’ really mean?  Does it mean that the employee should simply follow the rules given to them by their boss and walk lock-step like an android with never any additional thought about why the rules are there or whether there should ever be a reason to disobey a rule.

Barry tells several stories such as the janitor who stopped mopping a floor at a hospital even though he was told to get it done now because he saw a patient trying to walk up and down the hall with a walker after an operation.  Another hospital employee refused to vacuum the carpet in the waiting room because there were some people in the waiting room who had been up all night with a sick relative and were trying to catch a little nap before going back into their family member’s room.  While these were hospital related examples, I remembered them because I related to how nurses would come into a darkened room in the middle of night and turn on the bright lights just to take a patient’s vitals.  Can’t they have a lower wattage night-light rather than waking up the patient every 2 hours? I thought sleep was suppose to be healing?  Can’t the vitals be obtained remotely?  Or how about the buzzers and alarms on the electronic equipment at night?  Do they really have to be that loud?  Isn’t it possible in this day and age to have them automatically signal the nurse’s station or send a text message directly to the nurse’s cell phone so they can get the message no matter where they are?

Where is the kindness, caring, and empathy in today’s world?  Where is the moral will to do what is right rather than simply what some procedure says to do or what will cost the least amount of money?  Having to make many phone calls to various people lately I can tell you that I am sick and tired of answering machines that pick for people that are either not at their desk, on another call, or simply do not want to answer that say, “Your call is important to us.  Please leave a message and we will get back to you as soon as possible.”  Four hours later you might get a call back.  Sometimes it is the next day.  In either case, I have to ask how important was my call to them really?

A wise person knows when to ignore the rules and when to improvise for the greater good of the customer, patient, or client.  They treat rules as guidelines, but not absolutes, not as limitations.  They depend on their experience to choose the better path.

Barry then goes on to tell the story about a father and son at a baseball game.  I actually remember seeing this in the news.  The son was thirsty and wanted a lemonade.  The father went to the concession stand and bought a Mike’s Hard Lemonade which was a relatively new product at the time.  He really did not know this product had alcohol in it.  (I see you snickering.) Anyway, he brought it back for his son and an employee of the stadium saw the boy with the lemonade and called the police.  The father was arrested and they tried to move the boy to foster care.  It took two weeks for things to be straightened out and the father reunited with his son.  The point is that a simple conversation could have solved the issue without all of the expense of an ambulance, police, courts, foster homes, judges, and more.  Wisdom would have solved the problem in minutes while procedures cost everyone time and money for what was a simple mistake.

Brilliance is nothing without wisdom.  Even the most brilliant person can look stupid if they don’t apply their brilliance to know when and how to apply rules.  Take away a person’s ability to think and just blindly follow rules and you take away the growth of their moral skills.  Furthermore, too many rules can lead to stagnation.  Providing incentives on top of those rules takes away a person’s understanding of doing what is right unless they are paid for doing it.

Barry also maintains that we all need to build character starting with students in our schools.  We need to teach them how to respect themselves, how to respect their school mates, respect their teachers and staff, and respect learning.  Everything, he maintains, follows from that.

Is Barry right?  Perhaps.  At the very least, it should make us pause to consider.  All I can say is that it is all about what you do and how you do it.  Practical wisdom, not blind obedience to rules, will help you make a positive contribution.  That type of wisdom does not require brilliance, but it does require practice building your moral skill and moral will.  If your organization does not support you building those skills, then even the best employee forced to constantly swim upstream will give up and never really soar with the eagles.

Thanks Barry for a very insightful presentation.

C’ya next time.

An Ending – A New Beginning

Some of you know me personally and know how my wife has been battling cancer for the last three and half years.  Others might know about her from a blog entry a few months back.  I am sorry to tell you all that Susan died today at 5:15 PM after a very difficult, but valiant battle with cancer.

I met Susan back in 1977.  After dating for over a year, we married in 1978.  Susan was a wonderful and loyal wife and in 1988 she became an equally fantastic mother to our only daughter, Natasha.  Susan was always there to help her friends and I hope they will always remember the good times they shared with Susan.

Susan, I always told you that would love you now and forever.  Now may have ended today.  But forever is just beginning.

Susan M. Antonovich, 1956-2013

Tax Week-No Post

I’m neck-deep in taxes this week for my wife and my federal income tax, our daughter’s federal income tax and my deceased father-in-law’s federal and state income taxes.  Please come back next week when I hope to resurface and be able to resume my coverage of Data Quality Services.

Global Warming/Climate Change – Is It Real?

Every time the temperature gets too hot in the summer or too cold in the winter, or too windy, or too rainy, or too whatever, someone is either pointing at global warming as the reason or alternately as proof that global warming does not exist. Of course the Earth’s climate has always been variable over long periods of time, even periods as short as a few thousand years, but that in of itself does not prove or disprove anything.  While we may be coming out of a relatively cool period, perhaps even a mini ice age, man’s polluting of this planet has been evident for decades.  At the same time, I don’t believe any one country can make a difference if so many large developing nations view any curtailment of their perceived economic development in the name of heading off climate change as nothing more than a ploy to keep them poor and underdeveloped.

On another but similar note, I just saw something on the Internet that plots what they believe to be an increase in the number of meteors streaking through the atmosphere making large booms.  Really?  Something going through the atmosphere faster than the speed of sound is making a boom?  Anyway, they claim that this is proof that an even larger object could be heading our way.  Last year it was the Mayan calendar and the planet Niburu.  When those disasters did not occur, they had to come up with something else I suppose.  Late night conspiracies are quite entertaining from one point of view with one expert saying one thing.  Another expert then says something exactly the opposite.  Each have irrefutable facts to back up their opinion.  Who and what is one to believe?  All this does is muddy up real research and blur the lines between valid skepticism and outright refusal to accept facts.

Many people find all of these predictions of disaster annoying, maybe even disturbing.  Some may even live in fear that the next global catastrophe is just around the corner.  Ultimately it makes recognizing the difference between the real pending disasters and the artificial ones difficult for many people.  It also makes it difficult to mobilize people to head off real pending disasters that they might be able to do something about from those that, if true, are not preventable.   The bottom line is that for me, I find most of them amusing.  I’ve always been a fan of science fiction.  Many of these predictions sound like the lead-in to a great science fiction story.  In fact, some of us at work sometimes take one or two of these stories or maybe even make up an entirely new one and spin our own ‘conspiracy’ theory.  I’m sure if we published some of these on the right Internet sites, more than a few people might just believe our theory was true.  Don’t believe it?  Ok let’s give it a try.

Astronomers have reported a comet, designated as Comet ISON, heading toward earth and due to arrive later this year.  They say that the comet will not get within 42 million kilometers of the earth.  Sounds like pretty far away.  After all, that is almost half the distance from the Earth to the sun.  What they did not tell you is that the orbit will be displaced twice as it rounds the sun by a close approach to first Mars and then Mercury which will deflect the orbit of the comet just enough to send it on a course right between the Earth and the Moon. The one hope is that it’s closest approach to the sun will be 1.8 million kilometers and considering that the sun’s diameter is only 1.4 million kilometers, it could simply vaporize completely.

While not considered a serious threat at first, astronomers admit that they cannot predict for sure whether these deflection will send the comet through is a window between the Earth and the Moon, a keyhole so to speak, that if the comet comes through at the right time could deflect the comet just enough for it to skim the upper atmosphere or even to crash into the Earth.

Because of the fear of mass panic by the public if they really thought that this comet could hit the earth and possibly cause an extinction event as great as the one that destroyed the dinosaurs, it was decided by all the world governments to suppress this information.  In the meantime, the governments of United States and the Soviet Union and even China have been secretly working with an alien race that has been visiting this planet ever since the late 1940’s to get all of the high government officials off the planet just before the comet’s approach.

In fact, it was only recently learned through confidential sources that the entire reason the comet is coming into the inner solar system is because a competing alien race to the one these governments have been working with deflected the comet from the outer reaches of our solar system’s Oort cloud over 10 years ago sending it on a course to destroy their enemy’s latest new friends in the galaxy, us.  Unfortunately, by the time our alien friends discovered this, it was too late for them to deflect the comet away.  Therefore, their only choice was to work with our government to evacuate as many humans as they can with their massive starships sitting on the far side of the moon.

Most people will not even know about the evacuation before it occurs because the aliens have a technology that allows them to transport people directly to their waiting space ships.  (Did you think that Star Trek came up with the transporter system on their own?)  By using implanted bio-electronic RFID chips that are custom coded to the individual and micro-miniaturized by the alien’s technology, they will be able to remotely identify and transport only the individuals on the list of those who will be saved.  Of course, to hide their plan they will make everyone get one of these RFID chips by using the claim that they will help monitor the individual’s health and make recommendations to improve their well being.  The bottom line is that when the time comes, people will disappear in the twinkle of an eye and no one will know what happened to them.  Fortunately, they will not have much time to think about before everyone realizes that the comet is really not going to miss the earth.

Making up stories is fun isn’t it?  Unfortunately as H.G. Wells found out with his famous War of the Worlds broadcast, you run the risk that some people will think the story is true and not a work of fiction.  That is why so many are confused as to whether global climate change is real or not.

C’ya next time.

By sharepointmike Posted in SciFi

What is Up With PowerPivot in Office 2013?

Just about two years ago I discovered this remarkable tool called PowerPivot and was blown away by two important facts.  First this add-in to Excel 2010 allowed me to manipulate millions of rows of data that came from a variety of different data sources to create analysis tables and charts in mere hours that would have required days of programming in other tools prior to that. Second the tool was a free download from Microsoft.

PowerPivot had some of its roots in the old Pivot table capabilities of Microsoft Excel that goes back over a decade and several versions.  For that reason, it was relatively easy to get started using it.  As I was learning how to use it, I started doing SQL Saturday presentations on Pivot tables from the simple PIVOT command in SQL Server to the Excel basic pivot tables of earlier versions to the latest PowerPivot features.  Over the first few months, I began to emphasize more of the PowerPivot features as I explored them and before long I was looking for different ways I could use PowerPivot in my everyday analysis.

In the fall of 2011, I was at a speaker reception the night before the Orlando SQL Saturday and ran into someone named Rob Collie.  I did know him at the time.  We were both going to give PowerPivot presentations the next day and Rob wanted to make sure that we did not walk over each other’s presentation.  As we talked, I found out that he was a prior Microsoft employee who worked on the Excel team.  I was impressed enough with his discussion of the inner workings of the PowerPivot engine and his enthusiasm for the product that I came to believe that PowerPivot could be a BI game changer in Microsoft’s grand scheme of bringing BI to the average business.

Since then I’ve written quite a few blogs about using PowerPivot, both the 2010 version for Excel 2010 and the version for Office 2013.  In fact, over the last year, most of my public presentations have been about PowerPivot and the Tabular model of SSAS in SQL Server 2012 which looks and acts very much like PowerPivot itself.  I even presented a PowerPivot/Tabular model presentation on the DAX language at the 2012 PASS Summit in Redmond.  I’ve been telling everyone how great PowerPivot was (and still is), but then the bad news hit.

Microsoft was pulling PowerPivot from the standard office SKU for Office 2013.  After making such a big deal about the fact that the latest version of Excel for 2013 would include not only PowerPivot but also Power View right out of the box (with the possible exception of having to turn on the COM add-in), I was stunned to hear that these tools would not be available in the final standard edition rollout of Excel 2013.  In fact, I found out that it would only appear in the Professional Plus edition or in the Office 365 subscription version.  After telling all of these people how great this free tool was, I now felt betrayed by having the product yanked out of reach by most potential Office 2013 users who will only have the standard SKU.

One of the first things I did when I heard this was to go off to Rob Collie’s blog to see what he was saying about this situation.  He wasn’t pleased either, but he had an opinion as to why it happened.  I’ll let you read his blog at: http://www.powerpivotpro.com/2013/02/hey-who-moved-my-powerpivot-2013-cheese/.  Rob’s basic premise is that the Office team at Microsoft decided to put PowerPivot and PowerView only in the Professional Pro SKU to add value to that SKU which previously was only differentiated by the inclusion of Microsoft Access which has greatly dropped in popularity over the years, especially since SQL Server Express can be downloaded for free.

So today I’m in Tampa giving a presentation on the DAX language for PowerPivot and the SSAS Tabular model at a SQL Saturday.  What can I tell them?  On possibility is that they should stay on Microsoft Office 2010 for which they can still download the 2010 version of PowerPivot.  For some people, that may work.  If they work for a company that has a volume license with Microsoft, they could still get the 2013 Office SKU and will be able to start using PowerPivot and Power View right out of the box.  But what abou the rest of us?

Interestingly, there is a guest post on Rob’s site from Ken Puls who explains how to buy PowerPivot 2013 using a $30 Volume Licensing Workaround.  The link to this blog is:  http://www.powerpivotpro.com/2013/02/guest-post-from-ken-puls-how-to-buy-powerpivot-2013-including-the-30-volume-licensing-workaround/.  (Sorry for the long link, but I wanted to make sure you see the full link.)  This trick seems a little too good to be true, but it is based on the fact that to get a volume license, you only need to buy 5 qualifying licenses of any Microsoft products including something like the Microsoft DVD Playback Pack for Windows Vista Business which costs about $7.00.  Then you should be able to get a copy of Professional Plus through volume licensing.

Still, it seems like too much work and I suspect that most developers will not want to play that game.  On the other hand, if this begins to limit the use of PowerPivot and Power View by corporate America and especially medium to small business, this could also result in eventual marginalization of what could have been a great product.

In my comment on Rob Collie’s site, I remind the reader of the way Access was hyped during the beta of SharePoint 2010.  The beta versions promised a way to migrate your Access applications to SharePoint to solve many of the concern with Access applications like security, multiple and different copies, multi-user limitations, etc.  But when SharePoint 2010 was finally released, they moved the ability to migrate Access to the Enterprise edition which effectively eliminated this feature from most SharePoint users who only use standard or foundation versions.  As a result, three years later, this feature is practically dead whereas it could have been a game changer moving countless Access applications to a more secure SharePoint platform.

In any case, I will continue to support PowerPivot and hope that Microsoft and the Office team re-consider their decision to hide PowerPivot in the Pro Plus SKU.   What are your thoughts?

C’ya next time.