Popular Business Intelligence

Sharepoint BI Demo Machine Full Download

Posted by popbi on October 2, 2014

Shortcut your Sharepoint Business Performance experience with this pre-packaged virtual machine available for partners and customers, pre installed with industry specific Sharepoint dashboards like Education, Energy and Communications.

contoso dashboard

If you get along to http://mssalesdemos.com/DefaultAnonymous.aspx you will find some online demo’s but this one is a full downloadable virtual machine – ok it is only Sharepoint 2010 on SQL 2012, it has a cool footer but for the tragic Windows Phone 7,  and it is only a Generation 1 VHD Hyper V machine.    If you are a Virtual Machine Jedi with an extremely high Midi-chlorian count, you may have a chance of converting the image to run on virtual machine clients like VMWare Client and Virtual Box.  I had best luck using Hyper V host on Windows 8.1.  Download is approximately 20Gb, so if you are having problems, try a Download Manager.

Here is the actual link to the Virtual Machine – http://www.microsoft.com/betaexperience/pd/BIVHD/enus/default.aspx.  Instructions for how to mount the Virtual machine under Hyper V are included in the page comments including administrator password, domain and ip addresses.  If you are running Hyper-V in Windows 8.1 or Windows Server 2012 R2, you will notice the instructions are written for the older version of Hyper-V – the terminology has changed slightly.   Instead of importing the Virtual Machine, you will find it easier to create a Generation 1 Virtual Machine, assign the internal network that you created using the Virtual Switch Manager, then add the add virtual hard disk from the uncompressed image files. Once the virtual machine starts, you are almost there.  Login with these initial login details, the following the remaining steps in the setup procedure :

User: administrator

Password: pass@word1


Link to Free Download Manager below, which I am no way affiliated with, it just solved my headaches with Akamai download manager and Internet Explorer.   This worked well to download this particular file but had no luck with resuming interrupted MSDN downloads.


Posted in Share (SharePoint 2013, 2010 and 2007), SQL 2012/2014 Installation and Upgrade, Windows and Windows Phone 8 | Tagged: , , , , | Leave a Comment »

SSAS: Access Denied – The file specified in the restore command is damaged or is not an AS backup file

Posted by popbi on September 30, 2014

You have attempted a database restore in Analysis Services from a .abf file and get the error : “The file specified in the restore command is damaged or is not an AS backup file.  The following system error occurred : Access is denied”.   The key clue in the error is the last part.


Go to the actual file, i.e. not the folder, right click, select properties, the click the Security tab.  Edit the file’s security so that the Analysis Services service account has sufficient permissions i.e. full permissions will work.  If you don’t know the Analysis Services Services account, go to SQL Server Configuration Manager and find your Analysis Services service where you will see the service account.

Posted in Analyse (SSAS, Excel, MDX, DAX and Powerpivot), Manage (SQL Server Admin) | Tagged: , , | Leave a Comment »

Windows 8.1 – No Upgrade Apps Option From Windows 7

Posted by popbi on September 28, 2014

Looking forward to the new features in Windows 8 ?   Taking a flying leap but make you flinch at first.  If you have no Upgrade Apps option chances are you are Installing the Windows 8.1 Enterprise media which doesn’t support upgrade apps – try the Professional edition media instead.   If you are chasing the Hyper V host this is available in both Professional and Enterprise but otherwise you will notice Professional doesn’t get a handful of “Enterprisey” things.  Get along to the Features v Editions page to see what you are missing between Enterprise and Professional http://www.microsoft.com/en-us/windows/enterprise/products-and-technologies/windows-8-1/compare/default.aspx.

Even if you are happy to punch out a clean install there is a Windows 8.1 Upgrade Assistant, which will give you an assessment on what is compatible and what isn’t.


Note, if you want the upgrade option, there are several options here, summarised down, these are essentially,

1. Keep Nothing (clean install)

2. Keep Personal Files (make sure your files are in your Desktop or Documents folder)

3. Keep Apps (if you don’t see this try the Professional edition media)

If you get along to here you will find more details about the upgrade process http://windows.microsoft.com/en-au/windows-8/upgrade-from-windows-7-tutorial.



Posted in Windows and Windows Phone 8 | Tagged: , | Leave a Comment »

SSIS: VS 2012 One or more projects in the solution were not loaded correctly

Posted by popbi on September 15, 2014

The following workaround http://up10it.blogspot.com.au/2013/07/VS2012-projects-not-loading.html was tested successfully for SQL Server Data Tools for Visual Studio 2012.   You come across the error “One or more projects in the solution were not loaded correctly” on opening a SSIS solution file in SQL Server Data Tools for Visual Studio 2012 and then encounter a head scratching moment.



Steps taken to work around :

1. Close SQL Server Data Tools for Visual Studio 2012

2. Navigate to the C:\Users\<Your UserName>\AppData\Local\Microsoft\VisualStudio\11.0

3. Rename folder ComponentModelCache to ComponentModelCache.old

4. Re-open SQL Server Data Tools for Visual Studio 2012

5. Open the Solution again

Posted in Integrate (ETL, SSDT and SSIS) | Tagged: , , , | Leave a Comment »

SSRS Migration: Data Sources Supported in Reporting Services 2012/2014

Posted by popbi on August 30, 2014

Migrating your Reporting Services instances to SQL Server 2012/2014 has a few key considerations.

First, the oldest version you can upgrade from is Reporting Services 2005 and that must be patched to SP4 (9.00.5000).

Next, any SQL Server 2005 report data sources are still supported in SQL Server 2012/2014. Here are the full list of data sources supported in SQL Server 2014 http://msdn.microsoft.com/en-us/library/ms159219.aspx. However your Report Server databases themselves can’t be hosted on SQL Server 2005 database instances http://support.microsoft.com/kb/2796721/en-nz.

Importantly, those pesky Report Models are depreciated in Reporting Services 2012/2014 http://msdn.microsoft.com/en-us/library/ms143509.aspx. They will migrate but you won’t be able to make changes after the migration – so at some point they will need to have their datasets rewritten. Getting a handle on how many reports hang off Report Models and giving your customers some options is a good way to manage this issue.

Finally, the full migration process is outlined here step by step http://msdn.microsoft.com/en-us/library/ms143724.aspx. There are risks in any major version migration, but running side by side environments and getting a business acceptance sign-off is the best way to mitigate those risks.

Posted in Manage (SQL Server Admin), Report (SSRS Report Builder Power View), SQL 2012/2014 Installation and Upgrade | Tagged: , , , | Leave a Comment »

SSIS: Execution History Catalog Dashboard To Go

Posted by popbi on August 7, 2014

The SSISDB contains a wealth of statistical data for your SSIS package executions and messages.   In a package failure scenario, trawling through SQL Management Studio to get detailed reports becomes quite tedious.  For a very quick and easy dashboard looking at the most recent events try a simple Reporting Services report with two tables and two datasets.  The two datasets are outlined in more detail below.

Screen Shot 2014-08-07 at 10.23.02 pm

The first dataset takes the last 3 days worth of execution history for your packages as per the top table. Whilst this dataset returns one row per execution as displayed, adjusting the groupings in this query will give you interesting perspectives on Average Duration and Execution Count.

The second table contains the last 15 messages.  In a failure scenario the second dataset will typically hold the most recent failure if no other packages have been run subsequent to the failure.  However you may need to adjust the dataset to suit, where TOP is specified, or parameterise as required – add a little Dijon mustard :)

Execution Summary – Last 3 Days

SELECT        folder_name, COUNT(*) AS tally_executions, SUM(DATEDIFF(ms, start_time, end_time))/1000 AS total_duration_s, package_name,
                         CASE status WHEN 1 THEN 'Created' WHEN 2 THEN 'Running' WHEN 3 THEN 'Cancelled' WHEN 4 THEN 'Failed' WHEN 5 THEN 'Pending' WHEN 6 THEN 'Ended Unexpectedly'
                          WHEN 7 THEN 'Succeeded' WHEN 8 THEN 'Stopping' WHEN 9 THEN 'Completed' END AS Status, start_time
FROM            catalog.executions AS e
WHERE start_time > getdate()-3
GROUP BY folder_name, package_name, status, start_time
ORDER BY start_time DESC

Message Detail – Last 15 Messages

SELECT        TOP (15) event_message_id, message, package_name, event_name, message_source_name
FROM            (SELECT        event_message_id, operation_id, message_time, message_type, message_source_type, message, extended_info_id, package_name,
                                                    event_name, message_source_name, message_source_id, subcomponent_name, package_path, execution_path, threadID,
                          FROM            catalog.event_messages AS em
                          WHERE        (operation_id =
                                                        (SELECT        MAX(execution_id) AS Expr1
                                                          FROM            catalog.executions)) AND (event_name NOT LIKE '%Validate%')) AS q
ORDER BY message_time DESC​

Here’s a couple of chilly reads that go a little deeper.



Posted in Integrate (ETL, SSDT and SSIS) | Tagged: , , | Leave a Comment »

SSIS: Task failed because sgen.exe was not found, or the correct Microsoft Windows SDK is not installed.

Posted by popbi on August 4, 2014

You may have come across this error if you try to program against web services in your SSIS packages.

“Task failed because sgen.exe was not found, or the correct Microsoft Windows SDK is not installed”.   If your I.T. Department won’t let you install the SDK and you are out of Freddo frogs, follow these instructions to navigate to and disable the Generate Serialization Assembly option.

1. Open SQL Server Business Intelligence Development Studio or SQL Server Data Tools.
2. Open the project file or the solution file.
3. In Solution Explorer, double-click the SSIS Package to open the Package Designer.
4. Double-click Script Task in the Package Designer to open the Script Task Editor dialog box – Click Edit Script.


6. In Project Explorer, right-click the project, and then click Properties.

7. Click the Build tab. In the Output area, select Off in the Generate serialization assembly list.

8. Make sure you Save Selected Items before you exit.



9. You should now be able to Build your code without error.

Posted in Integrate (ETL, SSDT and SSIS) | Tagged: , | 1 Comment »

SSRS: Driven to the Brink with the Data Driven Subscriptions Error

Posted by popbi on July 1, 2014

Clam Chowder….but first, you attempt to create a Data Driven subscription.  You have setup your SMTP settings for your Reporting Services instance, you have setup your encryption key for storing sensitive data and you have configured stored credentials for all data sources used in the report.

Alas you get the following error when creating your Data Driven subscription.


If you have checked all of the above and you think you might be going crazy, then it is likely you have made a reference to the User!UserID Report Collection within your report somewhere.  You will need to get rid of it, or use a workaround that I stumbled across http://prologika.com/CS/blogs/blog/archive/2011/03/28/data-driven-subscriptions-and-row-level-security.aspx (read through this link a little because it has more detail than my blog) but the example below simplifies the expression a little.

Use the following code snippet in any expression where you need to see the User running the report.


And make sure you have the following function set out in the Code section of your Report.

Public Function ClamChowder() as String

return Report.User!UserID.ToLower()

End Function

You gotta love Clam Chowder with sour dough.


Posted in Report (SSRS Report Builder Power View) | Tagged: , , , | Leave a Comment »

MDX: How to Pass a SSRS Parameter Label into a MDX Parameter

Posted by popbi on June 30, 2014

Ok Computer, Sing… us a song …

You have to pass a text string from your Reporting Services parameter (Label) into a MDX dataset as a parameter. An example MDX dataset is outlined here …

SELECT Measures.[some stuff]

ON 0,

FROM [Your Model]



StrToSet("[Dimension].[Attribute].&[" + @YourString + "]")


Posted in Analyse (SSAS, Excel, MDX, DAX and Powerpivot), Report (SSRS Report Builder Power View) | Tagged: , | Leave a Comment »

Always On Availability Groups: Do I need a File Share Witness or No ?

Posted by popbi on June 26, 2014

This blog sets about doing a shallow dive – to simply understand how a File Share Witness is used and required in an Always On Availability Group cluster.

An Always On Availability Group cluster requires no shared storage – Windows 2012 supports up to 16 nodes, SQL Server 2012 supports up to 5 nodes in an Availability Group, and SQL Server 2014 supports up to 9 nodes.  A cluster can sustain itself  as it loses nodes and Quorum is described as the state of the cluster.

The basic rule for determining how many nodes your cluster can sustain losing is a “majority” …. I love a good analogy… here is one … Your cluster is like a bar stool … quorum is its ability to remain standing… or rather, how many legs it can lose before it falls over.

So… File Share Witness, Yes or No ? … Mathematically speaking the short answer is …

…Even number of legs = Yes

…Odd number of legs = No

A bar stool with an odd number of legs isn’t an issue, an odd number of legs means a majority can always be determined, but for an even number of legs, losing half of the legs creates an issue – a majority can’t be determined, unless you have a witness (a backup leg).

Lets take a really simple example – a bar stool with 2 legs – this is your 2 node cluster.  Each leg is considered a vote, until it is removed, in which case its vote doesn’t count.

The state of the bar stool (quorum) is determined by how many legs are still standing (votes).  It would be pretty pointless setting up 2 node Always On cluster if you had no redundancy.  The mathematical  formula which explains how many legs a bar stool can afford to lose is… (legs/2)-1 … that’s (2/2)-1 … which equals zero legs.  Without a backup leg (file share witness), the bar stool could not sustain losing any legs… so it has no redundancy.


File Share Witness -> With a backup leg however (file share witness), the bar stool can lose half its legs (1), form a majority and meet the number of Votes required to keep the bar stool standing.


Let take a 4 leg bar stool example…because lets face it, they usually do … how many legs can a bar stool afford to lose to keep standing ? …The formula is (legs/2)-1 … that’s (4/2)-1 … which is equal to 1 leg.  If a 4 leg bar stool loses 1 leg … this still forms a majority and the stool maintains its quorum…


But if it loses two legs, (half of its legs), we are in trouble… a count of standing legs won’t reach a majority and the quorum can’t be sustained… unless…


File Share Witness -> For a bar stool with an even number of legs, a bar stool CAN lose up to half its legs, as long as a backup leg (file share witness) is used.   You can see when half of the nodes are missing, a majority can still be determined and satisfied, when a file share witness is used.


In advanced Always On architecture, a node can be configured so that it as no vote.   It still participates in the cluster and the availability group, but for the purposes of counting a majority, the vote doesn’t count.  So a 4 node cluster, with 1 node set to no vote, gives a 3 node vote.  A majority can be determined with an odd number of votes so a file share witness is not needed.   Locating your file share witness in a third datacentre can also provide another level of resilience.


Posted in Manage (SQL Server Admin), SQL 2012/2014 Installation and Upgrade | Tagged: , , | Leave a Comment »


Get every new post delivered to your Inbox.

Join 89 other followers