MSC-ERR-0008 An error has occured at the transport layer and OP-ERR-0181 At least one invalid member – Cognos and SSAS errors


MSC-ERR-0008 An error has occurred at the transport layer and OP-ERR-0181 At least one invalid member – Cognos and SSAS errors

A client has found a solution to two errors occurring in an environment with Cognos 10.1 and SQL Server 2008.

  • MSC-ERR-008 An error as occurred at the transport layer.
  • OP-ERR-0181 At least one invalid member reference was encountered in the query.

A hot-fix was applied to Windows as described in these two following articles and initial testing has been positive. The scenario is a single sign-on Cognos environment with Active Directory using Kerberos to authenticate to SQL Server Analysis Services 2008. The errors seemed intermittent.

http://blogs.msdn.com/b/psssql/archive/2009/04/03/errors-may-occur-after-configuring-analysis-services-to-use-kerberos-authentication-on-advanced-encryption-standard-aware-operating-systems.aspx

http://support.microsoft.com/kb/969083

Advertisements

Microsoft Announces New Developer Certification – MCSD


Microsoft Certified Solution Developer – MCSD

Microsoft has reworked the certification process for developers and introduced a new MCSD – Microsoft Certified Solution Developer credential. This redefined certification now addresses new generation developers by including cloud and application marketplace domains. In this way, they intend to embrace the current technologies.

The new certification is created around Visual Studio 2012, a development environment that allows applications to be built across devices and for specific devices. The focus is fast, light and stunning. The skills required go beyond the traditional environments and extend to the new world of multiple platforms.

The new MCSD certification will be useful to establish credibility in the new world of development. It demonstrates the ability to compete in the latest markets.

The first MCSD certification will be be MCSD: Windows Metro Style Apps. This will focus onskills needed to create beautiful, elegant and fast apps using Windows 8 with Visual Studio, HTML5, and C#.

There’s a free ebook from MS Press to help get started: Programming Windows 8 Apps with HTML, CSS, and JavaScript (First Preview).

Microsoft Changes Certification Titles … Again


Microsoft has overhauled the levels of certifications to meet their perceived changes in the skill sets of developers.  There will be four levels of certification in the Microsoft Solutions Expert program

  1. Microsoft Certified Solutions Associate (MCSA) – core skills and a prerequisite for further levels
  2. Microsoft Solutions Expert (MCSE) – relevant and up-to-date skills for IT professionals working with the solutions
  3. Microsoft Solutions Developer (MCSD) – Like MCSE, but geared toward developers, rather than IT professionals (not to imply that developers are unprofessional).
  4. Microsoft Certified Solutions Master (MCSM) — Beyond expert level, the highest level of skills are validated by this rigorous testing process.

Microsoft has also integrated Cloud Computing concepts into many of the exams to reflect the changing environments that are emerging.

A track that has been changed, probably relevant to the readers of this article is MCSE Business Intelligence: http://aka.ms/MCSEBI

FYI, there’s a 2-for-1 offer by Prometric at this time: http://aka.ms/Prometric241

Opinion:  I like the clarification of levels of skill proficiency and the distinguishing of breadth versus depth with IT Professional and Developer, but MCSE has long been associated with Microsoft Certified System Engineer and this could cause confusion with older managers.

IBM Cognos BI report error against SSAS – At least one invalid member reference was encountered in the query


At least one invalid member reference was encountered in the query

Problem

When encountering this error message “At least one invalid member reference was encountered in the query…”, when reporting against SQL Server Analysis Services with IBM Cognos Report Studio 10.1 using a crosstab, the problem may be caused by having no data in a row and column intersection.

Solution

A potential solution is to go to the query properties and, under Query Hints, change Suppression to Nulls.  This can also improve performance as it can reduce the data returned from the cube.  Note that this is a different setting than the Zero Suppression that suppress rows and/or columns.  It’s on the query, not the reporting object.

There could be many reasons that a missing member reference was encountered, but this simple change often fixes the problem.

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing


Duplicate attribute key error

A frequent error when processing an SSAS cube begins “Errors in the OLAP storage engine: A duplicate attribute key has been found when processing”. I have found that this is usually one of two conditions:

Typical Causes

  1. An actual duplicate key is found where a value for a child in a hierarchy occurs with two different parent values.
  2. A value for a data item is NULL.

Solution for actual duplicate key

The former is easier to discover and can be resolved by using a compound key for the parent that includes the child key. Look for it when the field in question is part of a hierarchy.  A child can only have one parent.

Solution for null data

The latter can be confounding because the error message is often misleading. It can show a value in the error message, not indicating that the real underlying cause is the NULL value. This can be repaired by using a COALESCE statement to replace a NULL with a value such as ‘Unknown’ or simply ”.

A good practice is to use views as the source of data to a cube, rather than making any modifications within the SSAS data source view. It is in the view that I add the COALESCE function around the data item.

For example, if there are nulls in the MIDDLE_NAME data item, the view

CREATE VIEW vwPeople AS

SELECT FIRST_NAME [First Name],
COALESCE(MIDDLE_NAME,”) [Middle Name],
LAST_NAME [Last Name]
FROM PEOPLE

will use the MIDDLE_NAME value for [Middle Name] unless it is NULL and then will use the empty string instead.

The correct order to set up a single server PowerPivot installation.


The order in which you install SQL Server 2008 R2, SharePoint 2010, and PowerPivot for SharePoint on a single server currently matters.  There is a glitch that occurs when you install some components of SQL Server 2008 R2 prior to installing PowerPivot.

This is the order that works for me:

  1. Provision a new Windows 2008 SP2 server.
  2. Add IIS role.
  3. Make it a domain controller with dcpromo.
  4. Install SharePoint 2010 prerequisites.
  5. Install SharePoint 2010, do not check configure at the end.
  6. Install PowerPivot instance (choose new server) from SQL Server 2008 R2 installation.

Now you may install any other instances of SQL Server 2008 R2.

 Here is a link that helped me identify the problem:

http://powerpivotgeek.com/2010/06/14/installation-failure-could-not-load-file-or-assembly-microsoft-analysisservices-sharepoint-integration-dll/

Microsoft Business Intelligence Conference 2010


I’m getting excited about the Microsoft BI Conference coming up in New Orléans.  I scheduled all of my sessions with a heavy emphasis on very technical deep dives into PowerPivot and PerformancePoint Services.  I am also looking forward to learning more about Master Data Services.

Here’s a link: http://bi2010.eventpoint.com/default.aspx

I hope to see you there.  Tweet me.