SQL Blog - Pieter van Maasdam, Macaw

SSAS 2008 - Connection from Excel

2009-03-03T12:31:00Z

I had some trouble connecting to an SSAS 2008 cube with Excel 2007. The problem was that Windows Firewall blocked the connection. A colleague of mine pointed out that you need to open 2 tcp ports on the server: 2382 and 2383. That did the trick.

SQL - Wrong results in Isoweek function

2009-02-24T12:25:00Z

See my previous post (http://sqlblogcasts.com/blogs/repieter/archive/2009/02/24/sql2008-isoweek-in-datepart-function.aspx)

In our organisation we use the ISOweek function to determine weeknumbers for a given date. This function appears to be dependant of the language setting (@@DATEFIRST) on the SQL Server. For example: January 5, 2009 should return weeknumber 2, but since the language is set to us_english it returns weeknumber 1 for the given date. Since the weeknumber returned by this function is not correct, we needed another solution.

We found another sql-function that checks the DATEFIRST setting before it calculates the weeknumbers:

http://www.rmjcs.com/SQLServer/TSQLFunctions/ISOWeekNumber/tabid/207/language/en-GB/Default.aspx

This gave us the expected result.

SQL2008 - IsoWeek in Datepart function

2009-02-24T07:01:00Z

To calculate the correct weeknumber I always used the IsoWeek function (found here: http://msdn.microsoft.com/en-us/library/aa258261(SQL.80).aspx).

In SQL 2008 this function is available in the DATEPART function:

SELECT DATEPART(wk, '4 jan 2009') AS WeekNumber, DATEPART(isowk, '4 jan 2009') AS IsoWeekNumber

Output:

WeekNumber	IsoWeekNumber
2	1

SSIS "Failure sending mail" problem

2008-10-01T11:23:00Z

I was trying to send an email from an SSIS package when a package failed. However, the Send Mail Task failed everytime. There was no error message except the one in the sysdtslog90 table:

An error occurred with the following error message: "Failure sending mail.".

I found the problem was McAfee. Here's the solution:

Open VirusScan Console
Double-click 'Access Protection'
Uncheck the option 'Prevent mass mailing worms from sending mail'
Click Ok

After that, McAfee is no longer blocking the outgoing mails.

SQL 2005 SSAS deployment error

2008-04-03T09:14:00Z

During a deployment of an SSAS project I got an error saying something about System.Data.Listener. The problem was that the server didn't have Service Pack 1 of the .Net Framework installed. After that the problem was solved.

SSAS2005 - Using logged in user within a role

2007-12-07T11:27:00Z

A customer of ours has a security model stored in a database and they wanted to have the security in the cube to be the same, so I came up with te following solution:

Example of the database in which the security is stored:

The DimUser table contains the users and their AD login account. The FactHours contains the hours that they have booked. The security is stored in the many-to-many table FactHoursDimUser, so the data in this table shows which user can see which fact. Go to the "Dimension Usage" tab to set the right relationships between the tables:

Next step is to add a role to the cube and add user groups on the Membership tab.

After that, you go to the dimension data tab, select the user dimension, the loginname attribute and then enter the following MDX expression to the "Allowed member set" section: {STRTOMEMBER("[User].[Loginname].[" + username+ "]")}. Also, make sure that "Enable visual totals" is enabled, so the calculations for the totals will only show what the logged in user is allowed to see.

After that, process the cube and go to the browser tab. Select "Switch user" to view the cube data with other credentials to see the results.

SSAS2005 - Processing failed due to collation difference

2007-11-28T14:03:00Z

Today, I was trying to create a simple dimension, but kept getting the error "Attribute key cannot be found". Since I was processing a dimension without any relationship to another table, I was very surprised to see this error. It appeared that there was one record that had a column containing the character 'ë'. When I changed this to the character 'e', everything worked. So, after checking the collation I saw that the relational database was Latin1_General_CP1_CI_AS and the cube was Latin1_General_CP1_CI_AI.

So, in order to fix this:

Open the management studio
Connect to the Database Engine
Right click the Instance and select properties to see which collation is used
Connect to the Analysis Services
Right-click the Instance and select properties
Click on "Language/Collation"
In my case: check the box "Accent-Sensitive"
Click Ok
Restart the SQL Server service
Restart the Analysis Services service
Re-deploy the Analysis Services database

After that it worked. I guess the person who installed the software didn't select the default settings (?)

SQL - Ordered list of tables for inserts

2007-11-22T12:00:00Z

I'm writing an sql script that automatically inserts "Unknown" records in dimension tables. Since there are foreign key constraints in the datawarehouse, I needed to have a list of tables in the right order, so I wouldn't get an integrity violation. The following scripts seems to be the answer:

WITH AllTables (TableId, TableName, ParentTableId) AS

(

SELECT

so.id,

so.name,

sr.rkeyid

FROM sysobjects so

LEFT OUTER JOIN sysreferences sr ON sr.fkeyid = so.id

WHERE so.xtype = 'U' AND so.name LIKE 'Dim%'

TableList (TableId, TableName, ParentTableId) AS

(

SELECT

at.TableId,

at.TableName,

at.ParentTableId

FROM AllTables at

WHERE ParentTableId IS NULL

UNION ALL

SELECT

at.TableId,

at.TableName,

at.ParentTableId

FROM AllTables at

WHERE at.ParentTableId = at.TableId

UNION ALL

SELECT

at.TableId,

at.TableName,

at.ParentTableId

FROM AllTables at

INNER JOIN TableList tl ON tl.TableId = at.ParentTableId

WHERE at.TableId <> at.ParentTableId

)

SELECT *

FROM TableList

NOTE: I used a WHERE-clause on the table names, since I use a naming convention where dimension tables always start with 'Dim'

SQL - How to add leading zeros to a column

2007-11-15T08:53:00Z

I needed to have a column that has a fixed length of 5 characters. The rest had to be filled up with zeros. Example:

SELECT

m.MemberId,

RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR, m.MemberId), 5) AS MemberCode

FROM Member m

Output:

MemberId MemberCode

1 00001
2 00002
3 00003
9 00009
10 00010
11 00011

SSAS2008 - Create a new dimension

2007-11-14T15:25:00Z

Just installed the SQL Server 2008 July CTP and started to play... Thought it'd be nice to throw it at you here... the first thing I did:

I created a simple table Member(MemberId, MemberName, MemberType) in my new Test database and put some test data in it.
Fired up the BIDS and created a new Analysis Services project
Created a new dimension and saw some new things:

I just wanted to create a dimension on the Member table, so I clicked Next a couple of times to finish the dimension

The first thing I saw - besides the new tab page for Attribute Relationships - was the blue line under Member. This was caused by the following message: "Create hierarchies in non-parent child dimension". So... I dragged the MemberType and MemberName property of my dimension to a hierarchy and gave it the name "MemberType - MemberName".

The blue line was still there, but now saying: "Avoid visible attribute hierarchies for attributes used as levels in user defined hierarchies". In order to make sure this warning goes away, you need to change the AttributeHierarchyVisible property of the attributes to False.

Since I created a hierarchy, I now need to define attribute relationships. This is easily done on the Attribute Relationships tabpage (I really like this new feature):

Be sure to set the appropriate relationships to Rigid. Not sure for which ones this has to be done... I now changed the bottom one and the warning about this (another blue line on the Dimension Structure tab) disappeared.

Also, I changed the key column of the MemberName attribute by adding MemberType to it, to make MemberName and MemberType a unique combination. Although I'm not sure if this is still needed in Sql2008 because I didn't see any warning for this...

After processing the dimension and browsing the data everything looks fine. I must say that I like the new features so far and I will look into all new stuff in the near future to see if I can find out how it works.

SSAS2005 - Creating folders for calculations

2007-11-13T09:38:00Z

A quick reminder for myself, because otherwise I forget where to do this:

On the calculation tab, click Calculation Properties

Enter a name for the DisplayFolder of the calculations:

The result:

SSAS2005 - Cube performance

2007-11-13T09:15:00Z

When you're using calculations in your cube, don't forget to set the "Non-empty behaviour" property of the calculation to give a little boost to the performance...

SSRS - Report filter based on logged in user account

2007-10-23T10:53:00Z

For a report in our organisation we wanted to have the filtering in the report to be based on the login name of the user. The data source is an SSAS cube, so we solved it by adding the AD login name to the Employee dimension and adding a filter to the report in which we put the login name with MDX.

Just create a new report and setup a filter
Select the Employee dimension
Select the attribute for the login name
For the operator, select MDX
Enter the following expression: STRTOMEMBER("[Employee].[Login Name].[" + username + "]")