Technical Tidbits – Susan’s Blog

‘ACE.OLE DB. 12.0 Provider Not Registered’: Solution Found!

January 28, 2015 SusieD22 No Comments Software, Technical Tidbits

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

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.

Now run your package and you should start seeing little green checkmarks. Happiness is 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!

Creating a Parameter Table

September 18, 2014 SusieD22 No Comments Becoming Technical, Tabular BI, Technical Tidbits

Note: ‘Technical Tidbits’ is an addition to this blog comprised of mini-blogs on different technical topics. Not all of these topics will be on the introduction level; in fact, most will deal with new things I’m learning as I continue on my own technical journey. These little blogs will expose you to advanced topics. You will want to read them and tuck them away for later review. For example, you may not have a clue about parameter tables now; but when you begin your study of the DAX language, you will remember the ‘tidbit’ blog and have it to review. Think of ‘Technical Tidbits’ as little markers down the road; they’re showing you what lies ahead.

DAX Master Wanna-Be

I’m on a quest to be a DAX Master. As Yoda of Star Wars fame wielded the ‘force’ as a Jedi warrior, I’d like to wield DAX functions to make Tabular Services and Excel Power BI slice through complex evaluation contexts! OK, I want to be taller and cuter than Yoda as well; I’m a stickler for detail.

So what is DAX and what is so great about DAX? Data Analysis Expressions is a formula language, used primarily with Tabular Services and Excel Power Pivot data models. Using DAX, you can create calculated columns and calculated fields on your data models; but our focus in ‘Technical Tidbit’ today is on creating a Parameter Table.

OK, what do DAX and Parameter Tables have to do with each other? What is a Parameter Table and what is the value of creating one? Well, you might not get as excited about Parameter Tables as I did, but I thought they were amazing when I first learned about them. As I read Ferrari and Russo’s book Microsoft Excel 2013: Building Data Models with PowerPivot, Chapter 7 dealt with ‘Understanding Evaluation Contexts’. Then, in order to bring Evaluation Context into the practical realm, they set up a real-world scenario utilizing the theory of evaluation context and introduced Parameter Tables.

I won’t go into the concept of ‘evaluation context’, but remember that it is VERY important to understanding DAX; using it wisely with data models and filtering is a must. With that said, let’s get to what a parameter table is and when they might be useful. A parameter table is when you create a little Excel table BUT it is unrelated to the rest of the data model. Well that doesn’t do anybody any good; but wait! This unrelated parameter table is used internally by DAX functions to modify the behavior of the other tables.

Say, for instance, you have a database with millions, billions or trillions of rows of Sales Amounts; trying to build a report on the sales column could drive you nuts. Now say your manager’s boss wants to see a report on all these sales. This amount of data would take up lots of space in a report and would not lend itself to concise reporting or easy analysis. We need a plan; a parameter table!

How to Create a Parameter Table

Your parameter table (let’s call it ‘ParameterSlicer) will allow the end user to decide the scale they want to view the sales amounts; for example: ‘Real Value’, ‘Thousands’, ‘Millions’, ‘Billions’, etc. The use of the parameter table does not filter the data; but allows you to change the scale used in viewing the numbers. Now that’s slick!

First, I used the ContosoRetailDW to create my data model, especially because there are millions of rows with lots of sales amount that will produce large sums. Then I created a little Excel table, named ‘ParameterSlicer’; giving it three columns ‘ShowValueAs’, ‘DivideBy’, and ‘Order’ along with four rows: ‘RealValue’, ‘Thousands’, ‘Millions’, ‘Billions’.

After you create the new table, you used the Table Import Wizard to load the Excel table to your data model. Remember, the new table is not related to anything inside your model; but once the table is in the model, you can use it as the source for a report slicer. This little Excel table will be the source for the slicer in that report your boss wants to see; but nothing will happen without the DAX Magic!

DAX

Without DAX, your little Excel table will sit in the data model and just look cute; lonely and unrelated, but cute.

But that’s not good enough; we must use DAX code to make everything work. Your DAX formula must ‘see’ what the end user has selected in ‘ShowValueAs’ and modify the content of the report to display the values selected. I modified the DAX calculated field as shown in Ferrari and Russo’s book, and will try to explain it as simply as I can.

SumOfSalesAmount (this is just the name of your calculated field that was placed on the ‘Sales’ table) =

IF (

HASONEVALUE = ( ParameterSlicer[ShowValueAs]), (this is the table name|table column of parameter table)

SUM (Sales[SalesAmount]) / VALUE (ParameterSlicer [DivideBy]),

SUM(Sale[SalesAmount])

)

DAX Explained

The IF function works with another function HASONEVALUE. The IF function test a condition; if the end user does not select anything in the slicer, the HASONEVALUE will return FALSE and nothing happens. When the end user selects a single value on the Slicer table, then the HASONEVALUE will return TRUE and the sales numbers will be seen by the scale selected by the end user. It’s like saying “if the user has selected a single value in the slicer, then show the ‘sum of sales amount’ divided by the corresponding denominator; otherwise, show the total of the ‘sales amount”.

It’s important to remember that the ‘ParameterSlicer’ Excel table DOES NOT filter the data, it will simply allow the end user to change the scale of the numbers. Now with the Excel table loaded into the data model and this DAX calculated measured placed on your ‘Sales’ table, you can bring everything together.

This is a simple example but the most common use for a parameter table; however, they can be used for much more. With a parameter table you can modify the way a number is computed, change parameters for an algorithm, or change values returned from a calculated field. So don’t dismiss this little guy.

Final Thoughts

The table names and columns have been changed to protect the innocent, just kidding; you know you’ll need to plug in your actual database table names and columns, but you get the idea of using parameter tables. Be sure to get Alberto Ferrari and Marco Russo’s excellent book: Microsoft 2013: Building Data Models with PowerPivot. The book is a great way to begin your journey into DAX. Until next time!

Technical Tidbits: Knowledge Documents

July 16, 2014 SusieD22 No Comments Technical Tidbits

‘Technical Tidbits’ is a new addition to this blog comprised of mini-blogs on different technical topics. Not all of these topics will be on the introduction level; in fact, most will deal with new things I’m learning as I continue on my own technical journey. These little blogs will expose you to advanced topics. You will want to read them and tuck them away for later review. For example, you may not have a clue about parameter tables now; but when you begin your study of the DAX language, you will remember the ‘tidbit’ blog and have it to review. Think of ‘Technical Tidbits’ as little markers down the road; they’re showing you what lies ahead.

Our first ‘tidbit’ is a simple concept I wished I had employed at the beginning of my technical journey; it would have been of great help over the years. Don’t expect flashes of light and angel choirs in the background, it’s a simple little thing; it’s called a Knowledge Document.

Knowledge Document

A Knowledge document is a simple word document or spreadsheet where you record learning links and bits of code you find in your studies. In fact, I have several Knowledge Documents: one for keeping a list of training links on different topics, one for cool SQL Server Reporting Services (SSRS) expressions, another for T-SQL and Functions (Time Date, Cast/Convert, etc) for SQL Server 2012, and another one for DAX and PivotTables/Tabular with Excel 2013. There is just so much out there to know and learn; a Knowledge Document helps you to keep up with everything. There’s nothing worst that knowing there’s something you really need for your technical project, but you can’t find the link or the expression, or the function, or ……..

Creating a Knowledge Document is easy; just create a new word document or use a spreadsheet. You can insert a table or label topic areas; make the document easy for you to use, it’s for your use alone. The hard part is taking the time to record the nifty things you learn along the way. I didn’t find out about the concept of Knowledge Documents until two years into my learning; so I wasted lots of time looking for stuff over and over, because I didn’t save them in a file the first time!

Create a folder and keep all your Knowledge Documents in ONE location. It will make for an easy time when you need to locate something fast! See, I told you not to expect fireworks; this is simple common sense. I just wish I had thought about it sooner. Sigh!

Wanting to make your technical journey easier,
Susan