Prepare Microsoft 70-452 Certification Exam

Recently I want to get the Microsoft 70-452 certification, so I could become a Microsoft Certified IT Professional (MCITP) for Business Intelligence Developer 2008.  There is a pr-requirement for this exam which I already had:
Exam 70-448: TS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance
After I search the internet for free/cheap training materials, I find the following:

1. A free 6-part webcast series to prep for 70-452 available from the Microsoft Partner Learning Center:  https://training.partner.microsoft.com/learning/app/management/LMS_LearnerHome.aspx
2. A free 12 hours self-paced course: Essential SQL Server 2008 For Developers from Microsoft e-learning: https://www.microsoftelearning.com/eLearning/courseDetail.aspx?courseId=166269

To me, the first webcast is introduction type information and it is very generic. It is suitable for people who are new to SQL 2008 BI.  If you are expert or advanced BI developer, you may not want to waste you time on it. I did find the 2nd course very helpful.  Since I don’t have time and I feel I am expert in some area, I skip the first several topics and only review those that I am not familiar with such as Data Mining for SQL 2008, Understanding SSAS Query & Performance Improvements, etc.. Lots contents in there are very helpful for 70-452 exam.
I also use Transcender’s practice material: http://www.transcender.com/practice-exam/microsoft/70-452.kap. It is a very useful tool to simulate the actual certification exam and provides validated answers.
After studying for a week using above materials, I passed the exam and got the MCITP.
Hope you will find this information useful.

A Good Time at PASS Summit 2010

There are lots session I could choose, lots people to meet with, and definitely lots parties and fun after hours. I really enjoyed the atmosphere and the different ways people share their knowledge.

This is the best week of the year for me and I hope I will see you all there next time.

Process SSAS Dimensions and Cubes Individually and Automatically using AMO .NET objects

I posted a blog entry before to Process the SSAS dimensions individually and automatically. If you use the same method to process the cube, you will find that it may not work all the times since the cube could be in an invalid state and unable to retrieve the Cube ID/Cube Name.

To overcome the issue, I will need to use the AMO .Net in stand of using MDV to accomplish this.

1. Add a Script Task first in the the ETL control flow to retrieve the Dimension and Cube information.

   Here is the steps in the Script Task:

   • Create the AMO.Server object and connect to the SSAS database; Make sue you add Microsoft.AnalysisServices reference to the project.
   • Retrieve the Database ID once it is connected.
   • For each AMD Dimension in the database, retrieve the Dimension ID into an Array List
   • For each AMO Cube in the database, retrieve the Cube ID into an Array List
   • Save the ArrayLists as the SSIS Object Variables to be used later.

   // Create instance of Analysis Services object

   AMO.Server aServer = new AMO.Server();

   ArrayList aDimensionList = new ArrayList();

   ArrayList aCubeList = new ArrayList();    

   try

   {

       //Get SSAS_DataMart connection string

       string strConnection = "";

       foreach (ConnectionManager aManager in this.Dts.Connections)

       {

           if (aManager.Name == "DataMart")

           {

               strConnection = aManager.ConnectionString;

           }

       }

       //Throw error if not such connection manager

       if (strConnection == "")

       {

           Exception ex = new Exception("No OLAP Connection manager: OLAP_DataMart found");

       }

       // Connect to DataMart instance of Analysis Services

       aServer.Connect(strConnection);

       string strCatalog = aServer.ConnectionInfo.Catalog;

       //Get the Database name

       AMO.Database myDataBase = aServer.Databases[strCatalog];

       System.Diagnostics.Debug.WriteLine(" Database Id = " + myDataBase.ID);

       Dts.Variables["_DatabaseName"].Value = myDataBase.ID;

       //Get list of dimension and set to the DTS Variable

       foreach (AMO.Dimension aDimension in myDataBase.Dimensions)

       {

           System.Diagnostics.Debug.WriteLine(" Dimension Id = " + aDimension.ID);

           aDimensionList.Add(aDimension.ID);

       }

       Dts.Variables["_DimensionList"].Value = aDimensionList;

       //Get list of Cube and set to the DTS Variable

       foreach (AMO.Cube aCube in myDataBase.Cubes)

       {

           System.Diagnostics.Debug.WriteLine("Cube = " + aCube.ID);

           aCubeList.Add(aCube.ID);

       }

       Dts.Variables["_CubeList"].Value = aCubeList;

   }

   catch (Exception ex)

   {

       System.Diagnostics.Debug.WriteLine(ex.ToString());

       Dts.TaskResult = (int)ScriptResults.Failure;

       Dts.Variables["Pamlico_PackageExecutionResult"].Value = Dts.TaskResult;

       throw (ex);

   }

   finally

   {

       // DisConnect to the local instance of Analysis Services

       if (aServer != null)

       {

           if (aServer.Connected)

           {

               aServer.Disconnect();

           }

       }

   }

   Dts.TaskResult = (int)ScriptResults.Success;

   Dts.Variables["PackageExecutionResult"].Value = Dts.TaskResult;

   
   
2. Add Foreach Loop to process the Dimensions individually. In this step, you may add logging information to indicate the dimension being processed.
   
3. Add another Foreach Loop to process the Cubes individually after processing the dimensions.

This way even thought the dimension process failed for some reason, we will still be able to retrieve the Cube ID back and reprocess them later without problem.

Using Color in Reports

Recently I reviewed an article Using Color in SSRS Charts reported from Melissa Coates. It is a very useful article contains Pros  and Cons with various methods when choosing color for SSRS Charts. You may also want to check this out.

Reporting services failed to start after upgrading to SQL 2008 R2

I recently upgraded my x64 version SQL 2008 to SQL 2008 R2. After everything completed without error, the Reporting services failed to start. There are two exceptions in the Reporting Service log related to performance counter.

Error creating counter.  Category: MSRS 2008 R2 Windows Service, Counter: Report Requests, Instance: MSSQLSERVER.  Error Description: System.InvalidOperationException: The requested Performance Counter is not a custom counter, it has to be initialized as ReadOnly.

ERROR: Appdomain:1 DefaultDomain failed to initialize. Error: System.NullReferenceException: Object reference not set to an instance of an object. at System.Diagnostics.SharedPerformanceCounter.GetCategoryData()

After I tried to repair it, the following error has occurred:

 

The file does not exist on the DVD at all.

Since I could not figure out any solution and unable to repair it, I then uninstalled and re-installed the SSRS component. After that, the Reporting Services started successfully.  

I searched the internet and found the following post related to the repair bug:

http://connect.microsoft.com/SQLServer/feedback/details/588703/senior-developer

The workaround is to copy the setup files to a shared or local dir, and rename the \\1033_ENU_LP\x64\Setup\SSCERuntime.msi to SSCERuntimr_x86-enu.msi. Now, the pair runs successfully.

Read Write Image Data Using T-SQL

Recently I need to display an image as logo on the SSRS reports. Since the logo needs to be replaced easily without modify the reports, I plan to store the image in a SQL table and use it as a database source image. The Image could be stored in either [VARBINARY](MAX) or [IMAGE] data type.

I use the OPENROWSET T-SQL script to upload the image file into the table. Then I could use the SELECT statement to retrieve it in SSRS.

CREATE TABLE [dbo].[Images](

[ImageID] [int] IDENTITY(1,1) NOT NULL,

[ImageName] [nvarchar](40) NOT NULL,

[ImageFile] [varbinary](max) NOT NULL,

) 

INSERT INTO [dbo].[Images]

([ImageName]

,[ImageFile])

SELECT 'Logo.png', * FROM OPENROWSET(BULK N'C:\MyPhoto.png', SINGLE_BLOB) as Logo

GO

To promote this image data to different systems, I need to create INSERT or UPDATE T-SQL statements to facilitate it.

For VARBINARY data type:

• Use DATALENGTH()  to determine the size of the data first.
  
• Then, use SUBSTRING to read in the data in 1024 bits
  
• Use PRINT function to generate the Insert/Update statement that using .WRITE(expression, NULL, 1024) to append the image data in small pieces.

DECLARE @ptrval varbinary(1024), @Len int, @start INt, @end INT

SELECT @Len = DATALENGTH(ImageFile) FROM dbo.Images WHERE ImageName = 'Logo.png'

SET @Len = @Len-1    

SET @start = 0

PRINT 'INSERT INTO [dbo].[Images]

           ([ImageName]

           ,[ImageFile])

     VALUES

           (''Logo''

           ,CAST('''' AS VARBINARY))'

While @start < @Len 

BEGIN

    SELECT @ptrval = SUBSTRING(ImageFile, @start, 1024)  FROM dbo.Images WHERE ImageName = 'Logo.png' 

    PRINT 'UPDATE [dbo].[Images] SET [ImageFile].Write('

    PRINT @ptrval

    PRINT ', NULL, 1024 )

    WHERE [ImageName] = ''Logo'''

    SET @start =@start + 1024 

    SET @end = @start + 1024-1

    IF @end > @Len

    BEGIN

        SET @end = @Len-@start+ 1

        SELECT @ptrval = SUBSTRING(ImageFile, @start, 1024)  FROM dbo.Images WHERE ImageName = 'Logo.png' 

        PRINT 'UPDATE [dbo].[Images] SET [ImageFile].Write('

        PRINT @ptrval

        PRINT ', NULL, 1024 )

        WHERE [ImageName] = ''Logo'''

        SET @start = @Len

    END

END

• After executing above query, the following result will be created and could be used as Insert/Update statements.

INSERT INTO [dbo].[Images]([ImageName] ,[ImageFile])VALUES ('Logo', CAST('' AS VARBINARY))

UPDATE [dbo].[Images] SET [ImageFile].Write(0xFF123462ABCE657033ED..........., NULL, 1024 )WHERE [ImageName] = 'Logo'

UPDATE [dbo].[Images] SET [ImageFile].Write(0x5C493D2522B62D1C894E3C..................., NULL, 1024 ) WHERE [ImageName] = 'Logo'.......

For IMAGE data type:

• Use DATALENGTH()  to determine the size of the data first.
  
• Use TEXTPTR() to retrieve a VARBINARY pointer.
  
• Then use READTEXT function to read in the data in small pieces.

DECLARE @ptrval varbinary(16), @Len int, @start INt, @end INT

SELECT @Len = DATALENGTH(ImageFile) FROM dbo.Images WHERE ImageName = 'Logo.png'

SET @Len = @Len-1 

SELECT @ptrval = TEXTPTR(ImageFile) FROM dbo.Images WHERE ImageName = 'Logo.png'

SET @start = 0

While @start < @Len 

BEGIN

    READTEXT dbo.Images.ImageFile @ptrval @start 1024;

    SET @start =@start + 1024 

    SET @end = @start + 1024-1

    IF @end > @Len

    BEGIN

        SET @end = @Len-@start+ 1

        READTEXT dbo.Images.ImageFile @ptrval @start @end;

        SET @start = @Len

    END

END

• After executing above query and storing the results in a text file,  Construct the Insert statement with a pointer and using UPDATETEXT statement to append the image data.

EXEC(N'INSERT INTO [dbo].[Images] ([ImageName], [ImageFile]) VALUES (''Logo'',0x89504E470...)')

EXEC(N'DECLARE @pv binary(16)

SELECT @pv=TEXTPTR([ImageFile]) FROM [dbo].[Images] WHERE [ImageName]=''Logo''

UPDATETEXT [Report].[Images].[ImageFile] @pv NULL NULL 0x493D2522B62D.......

....

');

Now, I could execute the T-SQL statement created against any new server to upload the image data.

SSIS Eliminating Duplicate Rows using The PARTITION BY clause

Recently I reviewed a post from  SQLServerCentral about how to eliminate the duplicated rows. We used this kind technique in the ETL process to de-duplicate rows from source tables.

Here is the sample code snippet used in OLE DB source :

SELECT

ROW_NUMBER() OVER (PARTITION BY LocationCode ORDER BY SourceID DESC) RowNumber,

SourceID,

LocationCode,

Column1,

Column2

FROM dbo.DimLocation WITH(NOLOCK) WHERE ......

The Row_Number() function will return a number started from 1 based on the PARTITION BY clause and ORDER BY clause. Whenever the column RowNumber is greater than 1 in the result set above is a duplicate row. We could then add a conditional split based on this column to send those rows that having RowNumber > 1 to error flow for review or drop them on the floor.

Should you use the source primary key order ( SourceID column as example above) or others to make the decision in your query?  This is a very import question to ask. You need to know your business rules well  in order to pick the correct data to load into the destination table, otherwise you may drop the right data on the floor during the de-duplicate process.

Here is the post Eliminating Duplicate Rows using The PARTITION BY clause by Suresh Maganti which I encourage you to check out.

Mariner is looking for talent

My employer, Mariner, is currently looking for talent individuals with strong SSIS, SSRS, and SSAS experience.  Mariner is based in Charlotte, North Carolina. 

We are a small company with several SQL MVPs and MCPs who are talented, dedicated, and fun to work with.  If you have experience developing Microsoft BI solutions and like to work in a truly collaborative team environment, then please consider Mariner.

The best way to inquire is through the email address on the Jobs page of the Mariner corporate website.

Using Table-Valued Function for SSIS Lookup

Table-Valued function is a user defined function that returns a table data type. It can be used where table or view expressions are allowed in T-SQL queries. While views are limited to a single SELECT statement, the Table-Valued user defined function can contains additional statements that allow more powerful logic than in views. Unlike the stored procedures, It provides the row set based meta data which could be easily parsed in SSIS OLE DB source component.

Here is how to use it for lookup component

• Create a Table-Valued Function as below

CREATE FUNCTION [dbo].[udf_MyFunction] 

(    

    @Parameter NVARCHAR(30)  

)

RETURNS 

    @MyTable TABLE (

        [Code]              NVARCHAR (30)   NULL,

        [Column1]           NVARCHAR (50)   NULL,

        [Column2]           NVARCHAR (50)   NULL

      )

AS

BEGIN    

    INSERT  INTO @MyTable

    SELECT    @Parameter, Name, Color

    FROM dbo.MyTable

RETURN 

END



• In the SSIS Lookup component, specify the OLE DB connection and use SQL query result as

SELECT * FROM dbo.udf_MyFunction (N’1’)

• To pass in parameter at runtime, an expression to derive the SQL command could be used for the Lookup component as

"SELECT * FROM dbo.udf_MyFunction (" + (DT_WSTR, 30) @[User::MyVariable] + ")"