Feeds:
Posts
Comments

Archive for September, 2009

This warning message pops out because there has not been any dimension hierarchy created. So create one even though it is a simple one.
 
 
 
 
Advertisements

Read Full Post »

I have this SSIS package that runs successfully from Visual Studio but it fails to run when it is added as a SQL job. The errors that I am getting are :
 
Error loading ImpNetqNewsRss.dtsx: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Schlüssel ist im angegebenen Status nicht gültig.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
Executed as user: PRIGEMSQL01\sqlsvc. …9.00.3042.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  3:00:36 PM  Error: 2009-09-14 15:01:06.76     Code: 0x00000BB9     Source: Integration Task     Description: Resource/connection error validation [Error found while trying to connect to the Integration (Config) DB with string [Data Source=localhost;User ID=Test;Initial Catalog=InSite_Integration;Persist Security Info=True;]  due to underlying exception: System.Data.SqlClient.SqlException: Login failed for user ‘Test’.     at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception<c/> Boolean breakConnection)     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior<c/> SqlCommand cmdHandler<c/> SqlDataReader dataStream<c/> BulkCopySimpleResultSet bulkCopyHandler<c/> TdsParserS…  The package execution fa…  The step failed.,00:00:31,0,0,,,,0
 
It is obviously a connection problem because the data source is using SQL Server Authentication that calls another server. To resolve this, store the SSIS package in the Integration Server.
 
1) Open the SSIS package in Visual Studio
2) Go to File –> Save Copy of Package As
3) Set the following to below (BUT change the authentication to use SQL Authentication), set the package path to the name of the package and make sure that the Protection Level is set to "Rely on server storage and roles for access control", then click OK
 
 
 
4) Open SQL Integration Server in SQL Management Studio. You will see the package listed under Stored Packages –> MSDB.
5) Test running the package by right clicking on the package then select "Run Package".
6) Go back to SQL Job Agent and run the job. It should run successfully.
 

Read Full Post »

1) Set the dimension attribute USAGE property to "Parent"
2) Make sure the dimension attribute of Key usage is of the same type as the Parent attribute. For example, if the parent attribute is using parent_location_code of varchar type then the Key attribute will be location_code of varchar type. 
3) Make sure that each other dimension attribute has only one KeyColumn set.
4) Drag and drop field(s) such as Location Category and Location Type to the Hirearchy tab as 2 separate hirearchies. (NOTE: this step is not required in SSAS 2005)
 
 
Say the LocationCategory attribute has the following columns for the KeyColumn in ascending order. This is to define that the location parent has many location categories. With this setting, when you browse to it, the Location Category values will be repeated because location category can belong to more than one location parent. To remove duplicates, remove the Location_Parent_Code from the KeyColumn property.
 
Location_Parent_Code
Location_Category
 
 
 
 
 
 

Read Full Post »

Answer: Set the Usage property under "Basic" section to "Parent". It is set to "Regular" by default. Then set the MembersWithData property to "NonLeafDataHidden". Also ensure that the Key attribute has the same MembersWithDataProperty. As a parent attribute, set the AttributeHierarchyVisible to "True" so that users can browse from parent to children.

Read Full Post »

Whilst converting the cube from 2005 to 2008, I came across the following errors/warnings in 2008:
 
Warning: Do not ignore duplicate key errors. Change the KeyDuplicate property of the error configuration so that it is not set to IgnoreError

Resolution: By default, the ErrorConfiguration property of the dimension is set to "default" where the KeyDuplicate property is IgnoreError. Change this to "custom" then select the appropriate error handling in the KeyDuplicate property (i.e ReportAndContinue or ReportAndStop).

Warning : Set the AttributeHierarchyVisible property of the key attribute in a parent-child dimension to False.
Resolution: In the dimension attribute, set the AttributeHierarchyVisible property to False. It is True by default.
 
Error: RegularMeasureGroupDimension [Cube].[Fact Events].[Scenario] : Granularity is not defined.  
Resolution: Open the cube, go to the "Dimension Usage" tab, fill in missing Measure Groups for Scenario dimension.
 
 Warning:  AttributeRelationship [Material].[Material].[Material Group Code] : The name specified for the attribute relationship differs from the name of the related attribute. 
Resolution: Remap the attribute relationships.
 

 

Read Full Post »

If the error says "the dimension has attribute loop" or alike, go to Edit Dimension then check the "Attribute Relationships". Make sure that there is no red cross over the relationship arrows. If there is, delete it and remap the relationship.
 
If the error says "The following system error occurred:  Provider type not defined ssas"  or
 
"Errors in the metadata manager. An error occurred when loading the Dm Date dimension, from the file, ‘\\?\C:\Program Files\Microsoft SQL Server 2008\MSAS10.MSSQLSERVER2008\OLAP\Data\ICube.0.db\Dm Date.15.dim.xml’. (Source: \\?\C:\Program Files\Microsoft SQL Server 2008\MSAS10.MSSQLSERVER2008\OLAP\Log\msmdsrv.log, Type: 3, Category: 289, Event ID: 0xC1210013)
(2/09/2009 10:47:49 AM) Message: The file ‘\\?\C:\Program Files\Microsoft SQL Server 2008\MSAS10.MSSQLSERVER2008\OLAP\Data\Cube.0.db\Dm Date.7.dim\15.Hierarchy.Quarter.sstore’ could not be opened. Please check the file for permissions or see if other applications locked it. (Source: \\?\C:\Program Files\Microsoft SQL Server 2008\MSAS10.MSSQLSERVER2008\OLAP\Log\msmdsrv.log, Type: 3, Category: 289, Event ID: 0xC1210023)"
 
then rebuild the ssas solution followed by process. If it throws errors again, rebuild and reprocess. Repeat this until the deployment and processing of the solution and cube is successful. Some websites suggest cleaning the data in the C:\Program Files\Microsoft SQL Server 2008\MSAS10.MSSQLSERVER2008\OLAP\Data, which I did but it did not resolve the issues.
 
If the errors says "Unexpected internal parser error" whilst trying to open Calculations tab, it basically  means that Visual Studio has failed loading this due to lack of computer memory (i.e. out of memory). The error does not specify this at all. To resolve this, freeze up the memory and rebrowse the cube.
 

Read Full Post »

In SSAS 2008, there are 2 places in the Data Source Designer that needs to be configured i.e. General tab and Impersonation Information.

I get the following error when I set the Impersonation Information of the Data Source to Inherit and the Data Source to use Windows Authentication (under General tab):

OLE DB error: OLE DB or ODBC error: Login failed for user ‘CSU’.; 28000.
 
and when set to "Use the credentials of the current user", I get the following error instead:
The datasource , ‘DW’, contains an ImpersonationMode that is not supported for processing operations. 
 
Solution: set the Impersonation Information to "Use a specific Windows user name and password" (for Windows Authentication). Alternatively, set the Data Source to use SQL Authentication and set the Impersonation Information to use "Inherit".
 

Read Full Post »

%d bloggers like this: