From the category archives:

Business Intelligence

ScriptMain is not CLS-compliant

January 21, 2011

in SSIS

While working with the script component in SSIS, sometimes we may run into the below warning. 

Warning           XX       ‘ScriptMain’ is not CLS-compliant because it derives from ‘UserComponent’, which is not CLS-compliant.

And an error like,

Error XX Validation error.DdataFlowTaskName Script Component [842]: Microsoft.SqlServer.Dts.Pipeline.CannotCreateUserComponentException: Cannot create user component class. Make sure there is one class marked with SSISScriptComponentEntryPointAttribute in your script.     at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent()

This basically happens because “UserComponent” that we inherit from is not CLS (Common Language Specification) compliant. As per MSDN “A class or interface is marked as <CLSCompliant(True)> when it derives from or implements a type that is marked as <CLSCompliant(False)> or is not marked.”

 So, this issue can be easily rectified easily by adding the below code near the ‘ScriptMain’ line 

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _

<CLSCompliant(False)> _

Public Class ScriptMain

Inherits UserComponent

And also just check the number of input columns being passed and if there are any duplicate columns getting into the script component. This should resolve the issue being faced.

Tagged as: SSIS, SSIS Programming

{ Comments on this entry are closed }

SSAS Attribute Relationships

May 11, 2010

in Business Intelligence, SSAS

In the SSAS attribute relationships, what is the difference between Rigid and Flexible Relationships?

In simple terminology, by Rigid the following terms will come into our mind, something which is strict, something which is not so flexible, something which is fixed, firm or stiff and so on. So, even in Analysis Services, it has the same meaning, we use these for dimension attribute which do not change their values over a period of time, for example, if we take Time Dimension, the month May 2010 would always remain under the year 2010 (it’s true, you can believe me on thisJ, and any doubts, check calendar…), the same thing applies to a week in a month or a day in a week, the relationships never change.

So, with the same analogy, flexible is something that has a possibility of change (This is what we have to deal with most of the times, remember the rule, “Change is only constant”), in other terms, there is a possibility of members moving between dimension, taking a standard example for more clarity, let us take Organization -> Employee Department, an employee may be in Sales Department today, he or she can move to Marketing department, or vice versa or what ever other possibility… And another good example would be with respect to customer, customer’s address has a possibility to change, so this relationship between customer name and customer address is definitely going to be a Flexible one!!!

Now, with respect to analysis services, the default value for a relationship type property is flexible, so what does it really mean for us, flexible relationships require Analysis Services to drop and re-compute any existing aggregations during incremental dimension. If we change or define relationship between two attributes to Rigid, the analysis services by default takes the relationship as fixed and does not actually go and re-compute existing relationships and in other words lets the existing relationships remain without clearing them, thereby reducing the incremental process time.

SSAS Attribute Relationships

In the above, the hard lines with dark arrows are the once representing the rigid relationships and the lighter once with transparent arrows are the flexible once.

If looked from other angle, with respect to MDX Query’s on the Cube with say… 30% aggregation, if we fire a MDX and say the numbers we need are not there in the aggregations present, so analysis services in turn has to calculate the numbers that we need from the details, now, it takes in to consideration all the relationships, suppose I need a number @ semester level which is not there in my current aggregations, so if I design a relationship in the time dimension like Year points to semester, semester point to quarter , quarter to month, month to week and week to day. So, here if analysis services calculate the required data for date level, by default it has the required information for returning the data that you need by doing proper summation. So, thus we can say that, the hierarchies with rigid relationships can be queried faster than those with flexible relationships. But, the issue that we would face is, changing any dependent attribute that has a rigid relationship within any hierarchy requires full process of the entire dimension.

Note:this should be present in a checklist for cube development process, because these settings really count for processing as well as query performance.

If you have liked the article and want to get the articles (or) new posts at sql like, subscribe to articles through email by entering your email address in the top right and verifying the same. If you have any comments, then the below form is for you, go ahead!!!

Tagged as: Performance, SSAS

{ Comments on this entry are closed }

SSAS Allowed Browsing Folders

February 20, 2010

in SSAS

This article is in response to a comment in my previous article “SSAS Database Backup”, the user said that he wants to take the backup of the analysis services database to a custom location and he could not exactly figure it out. When you are in taking the backup of a SSAS Database, and when yu are in the SSAS Database Backup dialog box, then you will see a message

“The server property, AllowedBrowsingFolders, determines which folders appear in this dialog box.  To add or remove folders from this list, open the Analysis Server property box, and then edit the AllowedBrowsingFolders.”

Let’s follow the message and get our task done…

  1. Open management studio and connect to the desired instance of analysis services/ desired server.
  2. Select the particular instance you want to check the properties for and right click and select the properties option
  3. Now you have a new window where you see a list of properties, with fields for Name, Value, Current Value, Default Value and if a restart of the analysis services is required if the particular property is changed.
  4. At the bottom you have a couple of check boxes one being “Show Advanced (All) properties”, check this box.
  5. Now at the beginning of the list of the properties, you will find “AllowedBrowsingFolders”, Now select the value field, press end, and first add a pipe symbol to tell the analysis services that you are adding a new folder in the list of folders which can be browsed(“|”), and add the full folder path, and press “OK”.
SSAS Properties Allowed Browsing Folders

SSAS Properties Allowed Browsing Folders

That’s it when we go to the backup dialog box, we can see the newly added folder in the list of folders!!!

If you have liked the article and want to get the articles (or) new posts at sql like, subscribe to articles through email by entering your email address in the top right and verifying the same. If you have any comments, then the below form is for you, go ahead!!!

Tagged as: SSAS

{ Comments on this entry are closed }

SSAS Database Backup

February 4, 2010

in SSAS

We need to take regular backup of SSAS database as we do with the SQL Server database to be able to restore it if something severely happens.

When we backup an SSAS Database, it is carried out by analysis services into one single operating system file with an extension of “.abf”, meaning analysis services backup file.

What is stored in a backup file depends on the storage mode of an analysis services cube/ partition.  The below table shows how analysis services takes the backup operation

  • Multidimensional OLAP (MOLAP) – Meta Data of the Cubes, All the detail data pulled while processing the cube and all the aggregations that are designed.
  • Relational OLAP (ROLAP) – Backups up only the Structure/ Metadata of the cube.
  • Hybrid OLAP (HOLAP) – Back up all the aggregations and Structure/ Metadata of the Cube.

 

The point to be noted over here is, when we backup or archive an SSAS Database, the underlying Data Source/ Data Warehouse/ Data Marts which are there in the relational database format are not backed up.

For taking a backup of an Analysis Services database, you should either be a member of a server role of SSAS instance or having administrator permission on the SSAS Database

Now, Let us see how to backup an SSAS Database

Open the Management Studio and Connect to Analysis Services.

Select the database that you want to backup and right click on it, now you will get a menu where you will have an option for “Back Up…”, select it

SSAS Database Backup Menu

Now you have the main screen where you have the Database Name, the “.abf” file name and a few other options

 SSAS Database Backup Dialog Box

There are three main options that you get here

  1. Allow file Overwrite – This option as the name suggests overwrites and existing file with the same name.
  2. Apply Compression – This option specifies if the backup that you are taking needs to be compressed to save space on disk.
  3. Encrypt Backup File – This option is kept for extra security to the backup file, you will need the password before the file can be unencrypted and restored on to an analysis services instance.

In this article we have looked at how to backup a SQL Server Analysis Services Database. If you have like it and want to get the articles (or) new post at sql like, subscribe to articles through email by entering your email address in the top right and verifying the same.

 if you have any comments, then the below form is for you, go ahead!!!

Tagged as: Backup

{ Comments on this entry are closed }

SSRS Data Source Losing Credentials

January 21, 2010

in Business Intelligence, SSRS

This article is based on an issue that I was facing from a couple of days, although did not figure out an exact solution for the same, but the procedure followed and for the learning’s.

In the SQL Server Reporting Services, I was constantly loosing the database credentials for a Shared Data Sources in a Report Server project in Visual Studio after a doing a check in to the source control and after that do a get latest, this was really annoying.

SSRS No Credentials

The quick fix was to update the credentials on the Report manager front. This way the data is stored all in the ReportServer database that was specified when setting up SQL Server Reporting Services.

But, once that was done, I just dug into why this was happening and the things responsible for this was found to be the file which was missing from the source control.

When you create a data source “.rds” file is created at the back end which is used in Visual Studio, if you open the file up in a text editor, notice that the username and password is not stored in the file. It is actually stored in the .rptproj.user file. And at many a places people don’t like to keep it on the source control.

SSRS Data Source XML

SSRS Data Source XML

So, in this kind of situation we can either go to the report manager and edit the data source credentials there, and another work around is to add the “User; Password=pass” as part of the Connection String, When the .rds is opened up, the Connection String won’t show this portion, but the Credentials tab should contain the right values.

Tagged as: SSRS

{ Comments on this entry are closed }

Using Temporary Tables with SSIS

December 3, 2009

in Business Intelligence, SSIS

Many people keep on asking on different SQL Server forums on how to use a temporary table using in an SSIS Package, below mentioned is a mechanism that I have learned and been using for long time for now, below it is…

We are going to take this to achieve this goal, we have to create a temporary table, insert some data into it, and using data flow task which we shall dump this data into a permanent table.
We shall take the below script for this,
Create table ##TempTable(Id int identity(1,1),FName nvarchar(100),LName nvarchar(100))
 
Insert into ##TempTable(Fname,Lname)Values ( ‘John’,‘Smith’)
Insert into ##TempTable(Fname,Lname)Values ( ‘Aaron’,‘James’)
Insert into ##TempTable(Fname,Lname)Values ( ‘Albert’,”)
 
DROP TABLE ##TempTable
To be done on SSIS Front.
1.       Create a new data source
2.       Create a connection manager based on this data source
3.       Select the connection manager created in step to and go to properties, and mark the “RetainSameConnetion” as “TRUE”
4.       Now in the control flow – drag an execute sql task in to designer space.
5.       Copy paste the above SQL statements from the above script into that, except the drop table once.  J
6.       Now select the execute sql task, go to properties and make the “Delay Validation” property under execution as “True”.
Before we create the Data Flow Task, we shall first go to the Management Studio and Create a Global Temporary Table so that we have a basic table to map the columns with
7.       Drag the data flow task into the designer
8.       In this Data Flow task, create and OLEDB Source Connection, and have this select query in place.
select * from ##TempTable
9.       Now create an OLEDB Destination connection with a suitable data source, use the create new table option to create a table for the dump.
That’s it, now right click on the package and say execute, zoom it goes…
Now let’s see how we have to convert the global temporary table to a local temporary table.
10.   Select the execute sql task, and then properties and under expressions we have our sql query, here, at every place replace”##” with “#”.
11.   Now go to the data flow task, and in the properties, custom properties, you will find the sql query in “sqlCommand” property, change it here as well.
That’s it, now we have a package with Local Temporary Table as well.
Horray!!! Reached our Goal….

Tagged as: SSIS

{ Comments on this entry are closed }

Incremental Loads using SQL Queries

November 1, 2009

in Business Intelligence, Developer, SSIS

Incremental Loads using SQL Queries

Problem: Using SSIS we have very easy ways to load a SQL Server Data Mart Incrementally, but how to perform Incremental Data Load when we do not have SSIS with us, in other words using T-SQL Queries.

Solution: To begin with let us have a look at the architecture for loading the data mart, we can get our data from one or more data sources into a staging area. This step should purely depend upon the business and technical needs.
Whenever we want to load data incrementally, we should make sure that we are able to find a unique identifier to distinguish one record from the other. We need to take a look at the source timestamp, primary key, identity key, and unique key columns of the rows that we capture to ensure there is a continuous representation of each unique record in the dimension or a fact table.
 
Loading Fact Tables
In most cases we would be able to find a unique time stamp column or a unique key which is Bill No, Invoice No or a Incident No. Generally here we can go for Delete and Insert method between Stage and Mart Levels, for this we shall first identify the rate of Insertion and Updation of data in the source, and decide the number of days records that we need to get from the source to stage
 
Loading Dimension Tables
Loading dimension would be the trickiest part as we have to make sure all the changes are processed up to the data marts. Suppose, for example if we take a customer dimension, we can have a new customer being added, customers details being updated and in some rare cases customers information being deleted when there is no relevant fact data.
 
The above scenario can be tackled easily depending upon the amount of data by
Should import all the records or last “X” days records depending upon the need and
1.       Doing complete truncate and Insert. à If we want to have History information which is currently not available in transaction tables this method should not be used.
 
2.       Using Except Keyword:  If we have large number of duplicates and handle them on the fly we can go with the Except keyword, using it between the stage and mart, this would give us all the records that are not there in Mart and it would automatically remove duplicates.
 
Insert into Mart_Table
SELECT * FROM Stage_Table
 EXCEPT
SELECT * FROM Mart_Table;
 
 
3.       Doing a delete and Insert à we should delete the records that are available in the stage from mart and Insert all the records from stage to mart. Doing this we will also be having records that are not available in stage and this will also take care of any updating being data at the source level.
 
The above described were a few methods that I have used for loading data incrementally without using SSIS.
Conclusion: we can perform incremental loads using T-SQL when we do not have an access to SSIS.

Tagged as: Incremental Loads, SQL Queries, SSIS

{ Comments on this entry are closed }