SSRS -Create dynamic MDX dataset and tuning MDX query

How to create a dynamic MDX query
1 create a data set and select query designer

2 add all filter for the query and choose as the parameter ( after choose as the parameter , system will automatically create one parameter in you left hand of parameter folder)

3 click the design mode

4 we can start the modify the MDX query , normally we will keep the rest of after first "From" statement , coz those are the filter statement . we can deleted those highlighted part and add our own code

5 after modifying the query , we can click execute query and the system will automatically generate the columns
6 I will talk about the MDX query tuning , we may often use the crossjoin in our query , sometimes , we use function Crossjoin() , or using * to implement crossjoin ( normally when you use cube browser to drag the columns to data plant , the system will automatically use * to do crossjoin). both of crossjoin() function and * did very bad performance , coz it does not care the turple is null or not , so we should use nonemptycrossjoin() function , this function improved a lot on the performance

7 if you want to do dynamic query , there is another thing we need to pay attention , query format. normally what i did is to line up all code in the notepad, means put all code in one line , because sometimes some line termination activity ,like you click enter and start a new line,  will impact on the expression in the next step

8 click OK we will go to next step ( see the query text , it has been filled by your code )

9 click fx on right side of query and then we enter the expression window

10 next , we need to modify the commandtext , in this part , all the command text should be start with =. all the string should double quote and also we can add the parameter into the command
and then click OK . if you setup expression for query, the query design can not be used any more

you will see the query part was changed to <Exp> and gray out Query Designer

Great , we've done the setup for dynamic MDX query dataset



 

SSIS Row Level Error Tracking

Problem :
We extracted the data from Excel and load to Oracle database , sometimes the flow failed due to data issue , like length of field in Excel exceed the database length of datatype. we have to track those failed records and see what is the error message , why they are failed. this is row level error message tracking

Solution :
1 create a pl/sql procedure to extract the error message based on the error code

CREATE OR REPLACE PROCEDURE BIDW.GET_ERROR_DESCRIPTION (

   p_Error_Code   IN       NUMBER,

   x_Error_Description          OUT      VARCHAR2,

   x_return_code       OUT      NUMBER,

   x_return_msg        OUT      VARCHAR2

)

IS

BEGIN

 

   x_Error_Description := SQLERRM(p_Error_Code*-1);

   x_return_code := 0;

 

EXCEPTION

   WHEN OTHERS

   THEN

      x_return_code := -1;

      x_return_msg := SQLERRM;

      --DBMS_OUTPUT.put_line ('Others :' || SQLERRM);

END GET_ERROR_DESCRIPTION;

2 direct the destination error point to a script component and change the error option to Redirect row

 

 

3 edit the script property

A select input column, only select ErrorCode , this was automatically generated

B manually add one output

C add code

In this case , we use a parameter as connection string

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        '

        ' Add your code here

        '

        Dim cn As OleDbConnection = New OleDbConnection()

        Dim cmd As OleDbCommand = New OleDbCommand()

        'cn.ConnectionString = Connections.Connection.ConnectionString.ToString

        cn.ConnectionString = Variables.ConnectionStringOraOLEDBBI.ToString

        cn.Open()

        cmd.Connection = cn

        cmd.CommandText = "BIDW.GET_ERROR_DESCRIPTION"

        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.AddWithValue("P_ERROR_CODE", OleDbType.VarNumeric).Value = Row.ErrorCode

        cmd.Parameters.Add("X_ERROR_DESCRIPTION", OleDbType.VarChar, 500).Direction = ParameterDirection.Output

        cmd.Parameters.Add("X_RETURN_CODE", OleDbType.VarNumeric).Direction = ParameterDirection.Output

        cmd.Parameters.Add("X_RETURN_MSG", OleDbType.VarChar, 500).Direction = ParameterDirection.Output

 

        cmd.ExecuteNonQuery()

        If (cmd.Parameters("X_RETURN_CODE").Value = 0) And (InStr(cmd.Parameters("X_ERROR_DESCRIPTION").Value.ToString, "non-ORACLE exception", CompareMethod.Text) = 0) And (InStr(cmd.Parameters("X_ERROR_DESCRIPTION").Value.ToString, "User-Defined Exception", CompareMethod.Text) = 0) Then

            Row.ErrorDescription = cmd.Parameters("X_ERROR_DESCRIPTION").Value.ToString

        Else

            Row.ErrorDescription = ComponentMetaData.GetErrorDescription(Row.ErrorCode).ToString

        End If

        cn.Close()

    End Sub

End Class

 

 

4 load the records to flat file, which caused the error

 

Three new columns added

ErrorCode and ErrorColumn are generated by red Error Output , the ErrorDescription is generated by script , which called a procedure

5 sent the error log file to someone , by using .net code
read only variable: User::varErrorFile,$Project::varMailFrom,$Project::varMailSuccSub,
$Project::varJobEnvironment,$Project::varMailTo_Success,$Project::varMailSMTPServer

    Public Sub Main(

        Dim strCommandLineArgs As String = String.Empty

        Dim strSub As String = Convert.ToString(Dts.Variables("$Project::varMailSuccSub").Value)

        Dim strEmailTo As String = Convert.ToString(Dts.Variables("$Project::varMailTo_Success").Value)

        Dim strEmailFrom As String = Convert.ToString(Dts.Variables("$Project::varMailFrom").Value)

        Dim strEnv As String = Convert.ToString(Dts.Variables("$Project::varJobEnvironment").Value)

        Dim strSMTPServer As String = Convert.ToString(Dts.Variables("$Project::varMailSMTPServer").Value)

        Dim filePath As String = Convert.ToString(Dts.Variables("varErrorFile").Value)

        Dim lineCount As Integer = 0

        Try

            If (File.Exists(filePath)) Then

                Dim the_stream As StreamReader = New StreamReader(filePath)

                While (Not the_stream.EndOfStream)

                    the_stream.ReadLine()

                    lineCount = lineCount + 1

                End While

                the_stream.Close()

            End If

        Catch ex As Exception

        End Try                                       'check the file is existing or not

 

        If (lineCount > 1) Then                        ' check whether there are data in the file

            Try

                Dim smtp As New SmtpClient(strSMTPServer)

                With smtp

                End With

                Dim message As New MailMessage

                Dim strFile As Attachment

                strFile = New Attachment(Convert.ToString(Dts.Variables("varErrorFile").Value))

                Dim strFilePath As String = Convert.ToString(Dts.Variables("varErrorFile").Value)

                strCommandLineArgs = "List of the records in error during processing of data from enrichment file –(" + strFilePath.Substring(strFilePath.LastIndexOf("\") + 1, (strFilePath.Length - 4) - strFilePath.LastIndexOf("\")) + ")" + " at " + Date.Now.ToString() + " on " + strEnv + " Environment. "

 

                With message

                    .IsBodyHtml = True

                    .Subject = "PFA " + strCommandLineArgs

                    .To.Add(strEmailTo)

                    .From = New MailAddress(strEmailFrom)

                    .Body = strCommandLineArgs

                    .Attachments.Add(strFile)

                End With

                smtp.Send(message)

            Catch ex As Exception

                ' MsgBox(ex.Message)

            End Try

        End If

        Dts.TaskResult = ScriptResults.Success

    End Sub