Talend & Stored Procedures using SSAS 2008

ETL  and ELT process requires large volume of data. Processing large volume of data all at once without a filter can cause performance issues.

  1. The Database, Data Warehouse, Data Mart or the application systems may be active requiring multiple connections which can slow down other processes due to ACID properties.
  2.  The system may be distributed across different locations which may cause the query to timeout due to security settings.
 Talend Open Studio , Informatica Power Center , Pentaho,  Microsoft SSIS and other ETL applications  utilizes both stored procedures to restrict the follow of data based on specific criteria using context in terms of Talend or Variable (SSIS). How do you pass down the stored procedure parameter in Talend to filter the data based on specific value ? 


The process is not that hard at first. It begins with Metadata which contains all the database connection properties as illustrated  below. Once the  database connection has been established and the corresponding schema has been retrieved, the next process is to design the Talend job.


Microsoft SQL Server is being utilized, the stored procedure  has been created using the SQL Sever Management Studio. Other application like Oracle SQL Developer or IBM Data Studio can be used to connect to SQL Server and created various objects. A linked server was created to connect to SSAS 2008 to allow the MDX  query to be passed through OpenQuery.


Stored Procedure with SSAS linked Server name


Linked Server Connection to SSAS





Drag the tFixedFlowInput component to the job and the database connection from the Metadata which will open the dialog box. Since stored procedure are being utilized, select the last option with sp for stored procedure into the job site. Connect the two component together. 




Click on tFixedFlowInput  and edit schema as illustrated below. Type in the stored procedure  parameter and select the right data type.  Close it and now edit the database connection.



Click on the database connection and enter the stored procedure name as illustrated below. Enter the stored procedure name created for the job in double quotations and click sync columns. 

 This will populate the columns together by matching the output column with the input columns.  For Talend to capture the output of the data, an additional column has to be created with object type.  Click on the  edit schema which will open the dialog box and output column , click the  plus sign and add additional column as illustrated below.

Add the stored procedure name



Add new column 






Now add the input and output parameters by clicking the plus sign. 

Drag the tlogicRow to the job site. Click the Sync columns and edit schema.

Next the output of the stored procedure has to be extracted into a columns which can be accomplished using the tParaseRecordSet.  Click the edit schema and enter the corresponding output columns as defined by your stored procedure. Ensure that the datatype matches.



 Add another tlogicRow  to the job site and sync the columns. After wards edit the schema.




Depending on the destination of your project different component like flat file or XML palette may be utilized. For this exercise, the end destination is a SQL Server table.




 Now run your job.



For additional reading please see Transforming SSAS data

Comments

Post a Comment

Popular posts from this blog

ETL ( Automation vs Manual )

ETL applications like Talend, SSIS and Pentaho can simplify ETL or Data migration by eliminating or reducing the amount of SQL coding needed to complete the process. However same functions performed by ETL applications can be performed by scripting using various dialect of SQL and programming language like C# or Java. The downside of scripting manually without an ETL application is the need of connecting to data source and destination source. However most vendors like Microsoft SQL Server provides developers the option through linked server using SQL Server Management Studio or Oracle MySQL Workbench for MySQL.  Depending on the application a third programming language is need as interface like Java or C++ when performing the process from a developmental application. This will allow both the source and destination sources to be linked.  Another problem with the manual process is the complexity of SQL coding needed which needs to be altered constantly and the length of the co...
Read more

Data Migration

Migrating data  can be a challenging  task depending on the size of the data, platform and systems involved with the migration process. The age or the nature of data can also add to the complexity of the migration process. Legacy data or old data needs to be re-validated and cleaned to prevent any major issues when the data has been migrated just like you have with Data Warehouse system. ETL applications like Talend , Pentaho and Microsoft SSIS can be utilized in this process. The structure of the data plays a major role in determining the schema and the layer of the data.Texted based systems and RDBMS have different properties that needs to be examined and changed. The final properties to be examined is the platform. Migrating data from SQL Server 2008 to SQL Server 2012 is much easier than migrating from SQL Server 2008 to Oracle 12c or 11g due to different datatypes, objects , schema and properties. In some cases the underlying objects like ...
Read more

SSIS, SSAS & Stored Procedure

Image
In the following series SSIS would be utilized to extract data from Stored Procedure which contain query that is linked with SSAS MDX query via linked Server. Talend requires additional components while SSIS has fewer components but the output is the same. First process in extracting the query through SSAS via Linked Server is to create a linked server. Linked Server Second process is to create the stored procedure to extract the data. Stored Procedure definition with the MDX and regular SQL qeury Third process is to create an SSIS package. In the Control Flow component you need  two task which the Execute SQL Task and Data Flow task.  The fourth step is to create the database connection to the source and destination systems. In this case only one database system is being utilized. Another big difference between SSIS and Talend is that Talend  works well with JDBC but JDBC is allowed in SSIS rather .NET, OLED, ODBC etc which makes t...
Read more