PowerPivot Addin Won’t Load ? You May Need Visual Studio 2010 Tools for Office Runtime

If you have attempted to install the Powerpivot Add-in for Excel 2010 and you notice the PowerPivot tab is not there after install, chances are the addin failed to load.     You may also notice an error along the lines of “runtime error occurred during the loading of the COM add-in”.

In Excel 2013 this is a breeze as its all installed ready to go – although if you do have issues with the 2013 edition, it is likely you could be running Office 2013 Standard edition and not the required Office 2013 Professional edition http://office.microsoft.com/en-us/excel-help/start-powerpivot-in-excel-2013-add-in-HA102837097.aspx.  Get this right, and you shouldn’t need any of the prerequisites required for Excel 2010.

For Excel 2010 you need to first ensure the correct version of the add-in is installed for your flavour of Excel 2010.  So 32 bit version of Excel needs 32 bit Powerpivot add-in and 64 bit Excel needs 64 bit Powerpivot add-in.  If you don’t know check by going to the file menu in Excel 2010, select Help and check the version under About  Microsoft Excel.

excelversion

There are two prerequisites as outlined here http://msdn.microsoft.com/en-us/library/ee210599.aspx.

You will need the Visual Studio 2010 tools for Office runtime.  A good read here explaining how the 32 bit versus 64 bit thing works for operating system v Office v Visual Studio 2010 tools …

http://msdn.microsoft.com/en-us/library/bb608603.aspx

and location to down the Visual studio 2010 tools for office runtime here …

http://www.microsoft.com/en-au/download/details.aspx?id=35594

There is a second prerequisite as outlined here http://msdn.microsoft.com/en-us/library/ee210599.aspx

You must have Microsoft NET Framework 4.0 (http://www.microsoft.com/download/en/details.aspx?id=17718) or, on Windows 8 Microsoft .NET Framework 4.5(http://www.microsoft.com/en-us/download/details.aspx?id=30653).

Once you have downloaded and installed the Visual studio 2010 tools for office runtime, go back into Excel, go to File > Options > select add-ins > select Com add-ins and select the PowerPivot for Excel 2010 add-in again.  This should work.

The Mosdal Support Kit

Mosdal is not the name of the Android in the current Star Trek film, rather it is a support kit useful for troubleshooting issues with Microsoft Online Services.

Try it here … a quick download and install on your local PC, then enter your Microsoft Online account, perform your intended operations, then wait a few minutes while the tool goes and collects data and presents you with some detailed test results.

http://www.microsoft.com/en-us/download/details.aspx?id=626.

A tool in this style, would be great for troubleshooting Kerberos issues….

TSQL – Select All Foreign Keys and their Associate Primary Keys

Thats what I was after !  Not so obvious in the INFORMATION.SCHEMA tables but this query will list all the foreign keys in the database along with their associated primary keys.


SELECT TC.CONSTRAINT_SCHEMA + '.'+ TC.TABLE_NAME AS PRIMARYKEYTABLE
,TC.CONSTRAINT_NAME AS PRIMARYKEY
,COALESCE(RC1.CONSTRAINT_NAME,'N/A') AS FOREIGNKEY
,CASE WHEN TC2.TABLE_NAME IS NULL THEN 'N/A'
 ELSE TC.CONSTRAINT_SCHEMA + '.' + TC2.TABLE_NAME END AS FOREIGNKEYTABLE
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
 LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC1 ON TC.CONSTRAINT_NAME =RC1.UNIQUE_CONSTRAINT_NAME
 LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC2 ON TC2.CONSTRAINT_NAME =RC1.CONSTRAINT_NAME
 WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY'
 ORDER BY TC.TABLE_NAME,TC.CONSTRAINT_NAME,RC1.CONSTRAINT_NAME

Thanks to …

http://wiki.lessthandot.com/index.php/Find_all_Primary_and_Foreign_Keys_In_A_Database

 

TSQL Backup and Restore to and from Multiple Files

Backing up and restoring to multiple files can make your admin activities faster and can also test the throughput of your storage if you want to go silly.
A sample backup command sample for backing up and restoring 2 files is outlined below :

BACKUP DATABASE <DatabaseName>
TO DISK = 'C:\folder\<DatabaseName>_yyyymmdd_file1.bak'
,DISK = 'C:\folder\<DatabaseName>_yyyymmdd_file2.bak'
with init, nounload, stats=10, compression

Now run the following query to kill all connections in the destination database and then perform the multi file restore with placeholders for your own object names  :

ALTER DATABASE <DatabaseName>
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE  <DatabaseName>
SET MULTI_USER;

RESTORE DATABASE  <DatabaseName>
FROM
DISK = 'c:\folder\<DatabaseName>_yyyymmdd_file1.bak',
DISK = ' c:\folder\<DatabaseName>_yyyymmdd_file2.bak'
WITH STATS=10, REPLACE,
MOVE 'LogicalDataFileName' TO 'X:\MSSQL\DATA\PhysicalDataFileName.mdf',
MOVE 'LogicalLogFileName' TO 'X:\MSSQL\DATA\PhysicalLogFileName.ldf';

PowerPivot – Removing Duplicate Rows in Excel

You have a list of data and you want to remove duplicates in Excel before importing into PowerPivot.   There are two approaches that will give you the same visual result, but only one that will suit PowerPivot.

1. Advanced Filter – Filter Unique Rows, this is only a visual filter so the full list including duplicates still be imported when added to the PowerPivot model.   Optionally select the Copy to another location and use that list to import into PowerPivot.

Advanced Filter

 

 

 

 

 

 

 

 

2. Remove Duplicates – This option is preferred and will be discussed below.  You will notice the Remove Duplicates feature in the Data menu of the ribbon.

RemoveDuplicates

 

 

 

 

 

But first, we select our list of duplicate values.

Unsorted

 

 

 

 

 

 

 

 

 

 

In the Data menu select Remove Duplicates, then select the column list that applies.

DistinctListBox

 

 

 

 

 

 

 

You will get a message to advise the number of     duplicate rows found and the number of unique rows that remain.

RemoveDuplicatesResults

 

 

 

The data list is now ready for PowerPivot.

DistinctListRemoveDuplicates

 

 

 

 

 

 

 

 

 

 

 

 

PowerPivot FAQ site – Golden !

Golden link = this PowerPivot FAQ site

http://powerpivotfaq.com/Lists/TGPPF/AllItems.aspx  !!

Includes posts by Rob Collie who authored this cool read

http://www.amazon.com/DAX-Formulas-PowerPivot-Excel-Mastering/dp/1615470158 (at a cool $10 Kindle price).

Note – this PowerPivot FAQ is a public Sharepoint site so you are browsing the site as an external user – speaking of which, see my post on Sharepoint 2013 licensing for external users …

https://popbi.wordpress.com/2013/01/09/sharepoint-2013-features-feat-editions/

 

 

PowerPivot – Public Holidays By State

There are plenty of examples around about how to calculate working days for a single Public Holiday calendar.    But what happens when the Public Holidays vary by state ?   This post walks through a PowerPivot example for calculating a single measure “Working Days” that works when the states each have their own distinct Public Holiday calendar.

For the purposes of this example, a working day is defined as :

a) any day that does not fall on a weekend; and

b) any week day that does not fall on a public holiday

To begin, note we have imported 3 tables into PowerPivot, our Calendar table which is our complete sequential list of dates at day level, our PublicHolidays table which contains the list of public holiday dates per state, and a state table with one row per state.

PublicHolidaysDiagram

In our Calendar table we need a column called WeekDayCount where the value = 1 if the date is a week day, and zero if the day falls on a weekend.  As the Calendar table was prepared as an Excel table, the formula used to calculate WeekDayCount is …


=IF([@Weekend]="No",1,0)

For Public Holidays we import a 4 column table into PowerPivot (Date, State, Holiday Name and Factor).  This table contains only the specific public holiday dates per state, not a full sequential list of dates like our Calendar table.  If you don’t have this data available in a feed, you may need to manually prepare this data yourself.  The good news it doesn’t change that often.   Our 4th column called Factor is set to -1 for all rows in this table.  This will be used in the DAX calculation to establish a working day count.

PublicHolidays3

Finally we add a Working Day Measure in PowerPivot that sums all of the week days in the Calendar table and subtracts any public holiday that falls on a week day.


WorkingDays:=SUMX(Calendar,Calendar[WeekDayCount]) + SUMX(PublicHolidays,RELATED(Calendar[WeekDayCount])*PublicHolidays[Factor])

Finally we can browse our PowerPivot model to see the number of Working Days by State.

PublicHolidays1

If you are interested in counting days between Date Ranges, for example, marketing activities or a school holiday calendar, the following article by Alberto is a good concise read ….

http://sqlblog.com/blogs/alberto_ferrari/archive/2011/05/12/powerpivot-counting-active-days.aspx

Nanu Nanu !