Tuesday, June 9, 2015

Visual Studio SSDT COM Error When Opening an SSAS Cubes DSV - Fix and Work Around

Summary of Issue:
In Visual Studio (versions 2010, 2012, 2013), when opening some dimensions of Analysis Services project, the data source view does not display the table that is being used. When opening the Data Source View (.dsv) we get the error below:
 "An error prevented the view from loading. Additional Information: Error HRESULT E_FAIL has been returned from a call to a COM component. (msddsp)"

Root Cause:
The relevant error being returned is coming from a call to AtlIPersistStreamInit_Load in the DdsShapes.dll.  The first thing this code does when reading data from the stream is check the version of ATL that was used to save the data stream.  If the version used to save the data was greater than the version used to build the DDSShapes.dll which is reading the data, then it will return E_FAIL. 

In Summary, an object in the DSV was either created or modified and saved with a newer version of Visual Studio SSDT that has a newer version of the DDSShapes.dll

Summary of the Solutions:
Installing a newer version of Visual Studio SSDT and Registering the DdsShapes.dll for use on the machine regardless of the version of SSDT needed to develop in.

This solution will correct previous version of Visual Studio SSDT (2010, 2012, etc) even though a newer version is being installed. The goal is to setup the latest version of the DdsShapes.dll for the machine to use.


Step-by-step guide

Step 1
Verify the latest version of SSDT being used by Developers, as of this guide it will be SSDT Visual Studio 2013 for SQL Server 2014

If you are currently running the latest version skip ahead to Step 2

If you are not running the latest or same latest version as the other developers you can download the latest version here: https://msdn.microsoft.com/en-us/data/hh297027

Install SSDT Visual Studio 2013 for SQL Server 2014

Step 2

2.1 Run the command prompt as an Administrator on the machine you are updating

2.2 Run regsvr32 command with the newest version of the DdsShapes.dll
The DdsShapes.dll should be located in for the SSDT for SQL Server 2014 location:  
C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Bin \DdsShapes.dll

  2.3 Confirm Registration of the DLL.



This will complete the fix, and now allow all version of Visual Studio on the machine to use the newly registered version of the DdsShapes.dll.

Thursday, May 7, 2015

APS / PDW AU 2 – SSIS PDW DESTINATION COMPONENT BUG AND WORK AROUND

This is something I discovered during a migration to an APS/PDW Appliance. I have seen little documentation on it and figured I would write a small post about it.

PDW Destination Component:

Loading Mode: Upsert

Issue: When specifying a key column(s) for the Upsert.
In situations where the key column name has a space at the APS destination table. I.E. [Column Name]. The Upsert will throw an error that the destination columns: Column, Name do not exist.
Upon further investigation the component is reading the selected key column names as delimited by a space as well as comma. It will then treat each word separated by a space in a column name as a separate column specified for the key. Note that this includes columns wrapped in brackets []

Work Around:
Rename the column(s) on the APS destination by removing the spaces from the key column names.
For Example: [Column Name] Renamed to [ColumnName] or [Column_Name]

Steps to Recreate the Issue:

Step 1
Create a Destination Table in the APS/PDW Appliance with a column name that has spaces. Such as [Sample Column]. This table can either be Distributed or Replicated.

Step 2
Create an SSIS Package with a sample data set to load into this table.

Step 3
Use a PDW Destination with the Loading Mode set to Upsert.

Step 4
Select the Key Column for the Upsert to the column that has the name [Sample Column]

Step 5
Execute the load and record the error.

Tuesday, November 12, 2013

How to Give Your Boss a Headache - Introduction to Dashboard & Report Design


Click Here For Presentation Material
Charts, Gauges, Heat Maps,blinking icons and Don't forget about 3D. There are many ways to visualize a message, and just as many ways to make your bosses head spin. So if your just starting out and need a step in the right direction of what kind of things can cause a headache or if for some reason you would like to avoid them. Then this session is for you. This session is tool agnositc, and is tailored to the ideas around communicating infomation visually and effectively in which every visualization tool you plan to use.


A Matter of Time... In SSAS

Presentation Materials Uploaded and Now Available.


Ever as yourself what time of day it is? Like what time of day does our support desk get the most cases? What are my average sales per hour? When do I need to add more staff, or even perhaps am I over staffed? There are many situations in which being able to slice your data by time of day would be valuable. In this session we will review how to setup a time key and time dimension, configure it in an OLAP cube, and discuss implementing in a tablular model. 

Presentation Materials

Monday, November 11, 2013

Presentation Material for A Matter of Time in SSAS

From my Presentation on Creating and Using Time in SSAS.

Use the below scripts and samples to create a time dimension that will allow you to slice and dice down to the second.

A Matter of Time in SSAS

Friday, November 2, 2012

Why Departmental BI projects are more successful than Enterprise BI Projects.

This is something that came up the other day that I thought was a valuable topic. So here is the brief synopsis.

Scope is usually smaller. A Department has a specific series of questions to answer by implementing a solution and typically have a readily available infrastructure.

In an enterprise model you have stake holders from multiple buisness units that all want something specific or from such a high level the scale of the implementation and integration takes a significantly larger amount of time.
Suddenly getting the data is more complex and even though having a stake holder at a high level, it's not a priority of the day to day.

So how do you successfully implement a BI Project at the Enterprise Level thats successful?

Well it depends on the departments contributing, and scope of the project. Top down doesn't always work in this case, start with the business units and establish a consistant tool set.

Don't hesitate to focus on a business Unit that is more cooporative than another, as soon as you show results and demonstrate value with the companies data and not an example, the rest of the pieces start to fall into place.

You can then start to put dates around the completion of the integration of each business units piece and as this happens the visibility and value to the stake holders increases.

Thursday, April 12, 2012

Using an SSAS Calculation in SSRS: ERROR: A set has been encountered that cannot contain calculated members

Now I am going to provide a cliff notes version of resolving this error and will provide links to other resources for more details on the solution.

While working on a project we discovered when using certain calculations (Business Intelligence Time Calculations in this example) that SSRS would error when query building the Analysis Service Cube.


ERROR: A set has been encountered that cannot contain calculated members.
















Now the quick and easy way to resolve this error...
  1. In the SSRS Solution Explorer double click the datasource to open the Data Source Properties box.
  2. In the text box for connection string: append the connection string with ;Subqueries=1
  3. Click OK
  4. Have fun using your Calculations.


Need a little more: Here is a like to Mike Milligan's Blog with some more detail and step by step.