I have been using the Rowcount Component for some time now. The thing I didn't like about it, is that I had to create an SSIS variable for every flow in the Data Flow Task. For example: when extracting data we sometimes have more than 30 tables in a data flow. So, I have been trying to find a more flexible way to add rowcounts to my packages, without having to create a lot of new variables.
Here's an example of what I came up with (It's not ready to be used in production environments by the way):
I created an SSIS package variable of datatype Object named RowcountList. In the first Script Task, i initialize it by assigning an ArrayList to it. Next step is in the data flow task:
In the Script component I use an integer to count the rows. Then, in the PostExecute method I create an ArrayList based on the RowcountList variable. Then, I add the name of the script component combined with the rowcount to the ArrayList and store that in the SSIS variable RowcountList.
Finally, in the last Script Task I iterate through the ArrayList and store the rowcounts in a custom logging table. Now, it seems to me this is a good way to do the rowcounts, but I'm very curious if other people have tried to do this and maybe have found a better way.
When executing reports (Reporting Services 2008) with Analysis Services 2008 as a source I sometimes get an error saying: "Query execution failed for...". The problem seemed to have something to do with large amounts of data (just guessing this...)
To fix this error we had to add SSPI=NTLM to the connectionstring:
Data Source=MyServer;Initial Catalog=MySSASDB;SSPI=NTLM;
I'm glad it's working now, although I don't understand what it actually does...
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.
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:
This gave us the expected result.
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 |
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:
After that, McAfee is no longer blocking the outgoing mails.
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.
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.
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 (?)
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'
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
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.
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:
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...
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 + "]")
