Female Brick Layer

Sometimes it’s hard to envision the finished product of your labors when you’re in the middle of the construction mess.  And it’s hard not to become frustrated when you can’t instantly learn all the great technical stuff out there.  Well, you’re heard that Rome wasn’t built in a day, and neither will your technical studies.  Building a solid technical foundation takes time and a ‘no quit’ attitude.  If there was another way to do it, I would have found it by now!  So let’s continue our journey as we explore Excel, an essential part of a solid foundation.

Excel 2013 Focus with Lynda.Com

I hope you have completed some of the courses we discussed in Lynda.Com In-depth: Part 1; if you have not read the post, click here.  For those of you who have completed the courses suggested in Part 1, you’re now ready for focused courses on Excel 2013.  But why Excel, when we’ve been studying SQL Server?  Because Excel is the preferred ‘go to’ for business analysis by the vast majority of companies.  Managers and owners still like the ‘slice and dice’ of Excel, so you must know Excel as well.  It will also set a solid foundation when you move into Power Pivot!

Building a Solid Foundation

Lynda.Com has a plethora of Excel courses, so we need to limit the scope.  Today’s post focuses on five courses; they teach essential and advanced concepts, so let’s take them in the best order for learning.  The great place to build a strong foundation is at the bottom, so Excel 2013 Essential Training is where you should begin.  Even if you have a good understanding of Excel, this course will serve as a refresher while introducing you to additional features found in the 2013 edition.

Excel 2013 Essential Training is 6 ½ hours long; most of the ‘Essential’ course topics taught on Lynda.Com are lengthy but worth the time.  After completing the Excel 2013 Essentials you will be able to enter and organize data and build workbooks.  You will be introduced to basic functions, formatting, creating simple formulas, sorting data and more; in addition to an introduction to pivot tables.  Now keep in mind, a pivot table is not the same as a Power Pivot table; the business intelligence aspect of Excel 2013 will be discussed in the third part of Lynda.Com.

Next Level Up

Once you’re waded your way through the Essentials course, you can move on to Excel 2013: Tips and Tricks course.  This course is 4 ½ hours and shows you how to increase your productivity with ‘power user’ tips; teaching you how to create formulas quickly, split data into columns, apply formatting, build charts, and create split screens quickly.  There is also a chapter on ‘Top Ten Shortcuts’ and ‘Ten Tiny Tips’, if you don’t have time for the full course.  But before you move on, watch the section on Database Techniques with ‘Flash Fill’ feature.  Now you can move on to Excel 2013: Power Shortcuts. 

Excel 2013 Power Shortcuts is a mere 3 hour long course showing you over 65 tips, tricks, and shortcuts.  Some of the material mirrors the previous course, so compare and contrast to see which one you’d like to watch.  The Power Shortcuts topics cover creating hyperlinks, setting print areas, removing duplicates, analyzing a formula for errors, and summarizing data with charts and tables.

Higher Level

The last two courses deal with more complex subjects: Data Validation and Time/Data; both are considered Intermediate level courses.   Excel 2013: Data Validation in Depth, only one hour in length, shows how to control data input into workbooks by controlling dates, length of text, types of data allowed in a workbook, using an error alert tab, and more.  The focus of this course is to ensure your data is accurate and valid.

Our last course for review is Excel 2013: Working with Data and Time.  This one hour course explains how Excel stores and uses dates and times and how Excel stores dates and time.  You’ll learn useful date/time functions to customize your dates, format dates over a 24 hour period, and calculate differences across dates and times.  A solid understanding of dates and times will greatly enhance your skills when you work inside a Power Pivot model as well.

A Few Final Thoughts

Next time, we’ll conclude the Lynda.Com In-depth with a look at the Power BI courses for Excel, with emphasis on Power Pivot, tables, and charts.  This is where the fun really begins!  You will not want to miss it.

I’m reminded of a saying my sailing captain would often say:  “When you get to the dock, you’ve already arrived at your destination.”  Captain Kevin wanted to convey that the trip was not about getting to a location, but enjoying the journey along the way.  So thank you for taking this technical journey with me as we continue becoming ‘technical’ together.  Until next time!

Susan Schneider lives in Jacksonville, Florida with her wonderful husband Steve. She enjoys sailing and is a ‘wanna be fisherman’, and loves all things BI.  See more information under the ‘About Me’ section.  Remember to sign up for new blog notifications:  Go to Subscribe2 on the sidebar and sign up!

I can’t even begin to tell you how many times this error has popped up on me; it’s like the ‘Dread Pirate Roberts’ from the movie ‘Princess Bride’, you never want to meet him!  But I have great news, I have a semi-quick fix for the issue.  I will give you the ‘down and dirty’ way to fix the problem, because you’re most likely in the middle of a project and need the solution NOW.  But, if you’re interested to find out WHY the error occurs, keep reading.  There will also be a link at the bottom to a great article by Guy Glantser called “How to Load Data from Excel in SSIS -32 bit vs 64 bit’” and how he fixed the dread error message.  His article provided me the solution!

If you encounter this error you’re most likely working in a 64-bit environment (SQL Server, Excel, etc) and using a 32-bit tool like SQL Server Data Tools (SSDT).  There is no 64-bit version of SSDT; it doesn’t exist so just get used to it:)   By now, you should know that 32-bit and 64-bit do not play well together.  That’s probably why Microsoft makes 32-bit application installations the default setting, and why you must go out of your way to load the 64-bit version.  Don’t even get me started about setting up Office 365 in 64-bit mode; that’s a rant for another day!

Quick Steps

You’ll need to uninstall the Microsoft Access Database Engine 2010 Redistributable 64-bit version from your machine and download and install the 32-bit version.  Click here to get the download.  After you have the 32-bit version installed, you’ll need to make a few changes in your SSDT environment.

Under the Project tab of your current project, click the Project properties at the bottom; in my case, the project I’m working on is called Wise Owl SSIS Project. Click on the Project Properties (see screenshot) and change ‘Run64BitRuntime’ to FALSE’.

Run64BitRuntime

Last thing to do is go to the Properties box for your Package (in my case it’s called ‘ConditionalSplitLes7’ package), and change the ‘DelayValidation’ to TRUE.

DelayValidation

Now run your package and you should start seeing little green checkmarks.  Happiness is Little Green Boxes!

Little Green Boxes

 How and Why

If you’re like me, I’m not completely satisfied until I understand the reason behind the error and why the fix works.  So hang with me a little longer for the details; I hope you’ll leave happier and wiser.

You already know that the problem stems from your 64-bit environment being forced to deal with a 32-bit application, specifically SSDT.  Your Excel connection manager wants to use the ACE.OLE DB provider; but that pesky error keeps popping up, the ‘ACE OLE DB provider is not registered’.  But remember, your ACE.OLE DB provider is 64-bit AND you’re trying to interface with a 32-bit SSDT.  So this is why you had to uninstall the 64-bit and install the 32-bit version of ACE.OLE DB provider.  Now you have 32-bit Provider with 32-bit application; that’s why you made the next change: ‘Run64BitRuntime’ to FALSE.  When this property is set to FALSE, it allows the package to run in 32-bit; everyone is playing well.

But you still want to run the package in 64-bit, but there are validation issues.  Let’s look at what’s happening beneath the covers.  According the Guy Glantser’s article, the SSIS package goes through a validation process BEFORE the package execution ever occurs.  This preliminary validation is to ensure everything is configured properly before execution.

Since you have the 32-bit provider installed, you can pass the first validation hurdle, but the package will fail upon actual execution when you try to run the package in 64-bit mode.  The workaround was to make the last change by switching the ‘Delay Validation’ to TRUE.  By selecting TRUE, your task will be validated at run time; and as the package is controlled by the ‘Run64BitRuntime’, your package is executed in 64-bit!  See all my little green boxes!

Hopefully we’ve seen the end of the ‘ACE.OLE DB.12.0 Provider not registered’ error.  So long Dread Pirate Roberts!

Here is the link to Guy’s article

Susan Schneider lives in Jacksonville, Florida with her wonderful husband Steve. She enjoys sailing and is a ‘wanna be fisherman’, and loves all things BI.  See more information under the ‘About Me’ section.  Remember to sign up for new blog notifications:  Go to Subscribe2 on the sidebar and sign up!