Category Archives: SQL Server

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

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/

Which OLAP cube should I use, Microsoft SSAS or IBM Cognos PowerCube (Powerplay)?


An Online Analytical Processing (OLAP) cube is a powerful tool to analyze large amounts of data quickly. There are a number of products available that can create an OLAP cube. This article will discuss some of the factors that should be considered in choosing an OLAP technology and evaluate two major vendors’ products – Microsoft SQL Server Analysis Services 2008 (SSAS) and IBM Cognos PowerCube.
Three factors that are important in considering OLAP technologies are:
1. Your existing database and reporting technologies.
2. The amount of data that will be brought into the cube.
3. The skill set of workers that will design and create cubes.

Microsoft SQL Server

If your data warehouse platform is based on Microsoft SQL Server, especially if you are also using SQL Server Integration Services (SSIS), then SSAS is a natural extension of the architecture. SSIS can used to load and process the SSAS cube. SQL Server data will be optimized for loading into the SSAS cube. (It is important to note that SSAS can also source from Oracle, DB2, Access and Teradata data sources.) This Microsoft database environment can also be used as source data for an IBM Cognos cube, but the Cognos cube cannot be used with Microsoft reporting tools such as Reporting Services. IBM Cognos does have a software product that allows Powercube browsing with Excel; Microsoft SSAS cubes can be browsed natively with Excel pivot tables.

SharePoint
If your portal and collaboration solution is Microsoft SharePoint there, are exciting features in SharePoint 2010 that combines PerformancePoint and new PowerPivot software. These will leverage SSAS cubes and will not support IBM Cognos Powercubes.

IBM Cognos environment
If your database platform is not Microsoft and your reporting environment is purely IBM Cognos Business Intelligence, then using Powercubes is a natural extension. Analysis Services cubes may also be used in this environment if other considerations make this desirable, but all things being equal, Cognos is a better fit.

Size of data
The size of the source data and resulting cube is of paramount importance in your choice. Cognos Powercubes have an inherent limit of 2 Gb, although there are workaround techniques. Microsoft SSAS cubes are commonly 300-400 Gb in size with source data measured in terabytes. Multi-terabyte SSAS cubes are in use today. SSAS also gives the ability to use relational tables for aggregation (known as ROLAP) or a hybrid (known as HOLAP). This allows for even more scalability. For large amounts of data, Microsoft is a clear winner.

Learning Curve
Microsoft SSAS requires a more technical skill set for developers than IBM Cognos Powercubes. Microsoft cubes will require a working knowledge of the multi-dimensional language, MDX. For developers that know SQL it will look similar, but is a paradigm shift analogous to moving from procedural languages to object oriented languages. There will be a learning curve. The return for that investment is much more flexibility, programmability and extensibility.
IBM Cognos Transformer, the software used to design a Powercube, was designed with the developer or power user in mind. IBM envisions strong financial analysts creating their own cubes. The result is simpler and easier to use but lacks the rich capabilities present in SSAS. Organizations with a medium amount of data and limited technical resources can build solutions quicker using IBM Cognos cubes.

Conclusion
In conclusion, Microsoft SQL Server Analysis Services will provide more scalability, extensibility and functionality with a cost of complexity and learning curve. This makes it a more strategic choice and IBM Cognos Powercubes a tactical choice in the appropriate situations. Powercubes are best used with smaller sets of data in an existing Cognos environment and when a less advanced technical skill set is required by the designers.

IBM Cognos BI 8.4.1 release supports SQL Server Analysis Services 2008


IBM has released Cognos BI 8.4.1 and they claim that it finally supports SSAS 2008. I will be upgrading a client in early January 2010 and put this to the test. Prior to 8.4.1 there was a way to access an SSAS 2008 cube but it would not pass the user credentials with Kerberos as it does with SSAS 2005. I will be testing the new release for integrated security in particular.

Cannot convert from unicode to non-unicode error for Oracle 10g data source when running in 64-bit mode


Problem:

For packages that I have created to read Oracle 10g tables, that work fine with debugging in 32-bit mode, I get an error message on all string fields when I try to run in 64-bit mode. An example error message is:

[OLE DB Source [1]] Error: Column “ACCT_UNIT” cannot convert between unicode and non-unicode string data types.

Another interesting warning included is:

[OLE DB Source [1]] Warning: The external columns for component “OLE DB Source” (1) are out of synchronization with the data source columns. The external column “ACCT_UNIT” needs to be updated.

I cannot even try to convert this data with a Data Conversion item because the (red) error is on the OLE DB Source item and stops there. It doesn’t matter what the destination is or even if there is a destination in the package yet.

I’m using Oracle Provider for OLE DB, Oracle Client version 10.203 for 32-bit and Oracle Client 10.204 for 64-bit.
Oracle is 10g on a UNIX 64-bit server and the data is not unicode.
I’m using SQL Server Enterprise 2008 (10.0.1600) on Windows Server 2008 Standard SP1 on a 64-bit server.

The packages work fine in 32-bit mode and the data is not unicode data. When I change Run64BitRuntime to True in the Debugging Property Page, I get the error on the OLE DB Source item. I also get the error when I schedule a package to run using the SQL Server Agent.

Solution:

Install Oracle 11g clients. There is a bug in the Oracle 10g 64-bit client that does not exist in 11g. The 11g client is compatible with Oracle 10g.