Tuesday, December 11, 2012

'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

You may get the error " 'Microsoft.ACE.OLEDB.12.0' provider is not registered" during importing the Excel file from SQL Serve Data Tools (in a business intelligence 2012 project) into an Analysis Service 2012 Tabular instance (However, you may also get this error while working with MS Access or MS Excel data source projects in other environment other than business intelligence projects).

Searching for this error over the net returns the guide to install Microsoft Access Database Engine 2010 Redistributable.
While it is true that you need the above modules in order to fix the error however there is no match to mention that if you do install the 32 bit version of "Microsoft.ACE.OLEDB.12.0" while working on the server which have already installed the Analysis Services in 64 bit mode but the MS Office 2010 in 32 bit at the same time there is no chance to escape from that mentioned error. Meanwhile, if you are going to install the 64 bit version of "Microsoft.ACE.OLEDB.12.0" you will be stuck because you have already installed MS Office 2010 32 bit(or other MS Office products in 32 bit mode).
(Note: As a rule, you won't be allowed to install the other MS Office 64 bit version products as long as you have (even one) the 32 bit of MS Office product, and vice versa).
Solution :
The only solution which I've tried in order to resolve the problem successfully is :
1. Remove/Uninstall all of the MS Office in 32 bit versions, including Project Server, SharePoint Designer, Visio, ... . (do not worry about the current configuration or the data inside Outlook or your records within OneNote, they will be back like a charm while installing the same version of MS Office regardless of 32 or 64 bit).
2. Do reboot your computer even if did not ask for it.
3. Installing the 64 bit version of MS Office 2010 (note that by default when you run the "setup.exe" inside the MS Office DVD it will start with the 32 bit version of setup, instead open "x64" folder and run setup.exe in order to install the 64 bit of MS Office).
4. Now check your outlook or open excel, then activate your product.
5. Install the 64 bit version of  "Microsoft.ACE.OLEDB.12.0"  (AccessDatabaseEngine_x64.exe).
5. Done. Now open the SQL Server Data Tools and continue to do import Excel file into the Tabular instance of the Analysis Service project.


Sunday, December 09, 2012

How to deal with Time Dimension in SQL Server 2008,2012 Business Intelligence

Here I'm not going to learn how to create a Time Dimension in business intelligence 2008 and 2012 as you may know it well or it could be found on the net, however there are some consideration in order to create the Time Dimension.
The key difference is that in one approach it will never show you the  Time Dimension during that wizard and you have to create it later by manual.

1. In first approach, we have already installed (or restored) our data mart database using SQL Server Management Studio, so we would create a cube at top of data mart. In this case we do the following steps briefly:
a. Using SSDT (in SQL Server 2012) or BIDS (in SQL Server 2008) to create a new Analysis Services project.
b. Create a new Data Source.
c. Create a new Data Source View.
d. Create a new Cube:
in this step while creating a new Cube, you asked to define the measures and dimensions except Time Dimension. Then we have to create our Time Dimension manually.

2. In second approach, we do not have the data mart yet, instead we are going to create our data mart using business intelligence tools. In this case we do the following steps briefly: 
a. Using SSDT (in SQL 2012) or BIDS (in SQL 2008) to create a new Analysis Services project.
b. Create a new cube:
in this step while creating a new Cube, you asked to define the Time Dimension along with the other measures and dimensions as well as at the end of the wizard you have to check the "Generate Schema" to create the data mart followed by the measures and dimensions which you have defined during that wizard as the schema. In this case we will asked to define a Data Source and Data Source View during the data mart creation (after click "Finish", it will open a new wizard page) and only if we did check the "Generate Schema".

As you may be noticed, in the second approach we followed the opposite direction in compare to the first approach, as well as we create the data mart on demand but asked for Time Dimension, in contrast at the second approach we have to create the Time Dimension manually but already have the data mart.