Turbodata : capture GST filing errors with quickness and ease(ETL tools: SSIS, Qliktech Expressor, Alteryx, SAP Data Services)

Turbodata ETL solution migrates the GST SQL code to ETL code for better data audit and data profiling.

Capture the errors better by using one of the 3(three)options:

•          Fail the task
•         Redirect the error rows: for example in case there is a registered customer. However at the voucher level, the GST number has not been indicated for the given transaction. In such a case these rows can be redirected.
•      Ignore the error: In case the end client has typed in a wrong reference number for the credit note then the given transaction could be ignored and the transaction passed. In such a scenario the error check point to indicate that the reference number in the credit note should reference the sales voucher number in the datawarehouse

Capture the details at each of the transformation points within the dataflow and in the target table.

Send emails and alerts in case of any errors to the required stakeholders(event handlers)

•         Breakpoints: stop the job on any of the errors at the procedure level(container level) . For example in case the unit of measurement logic after extraction is not clear for all the transactions then  there could be a breakpoint for the given job.       
•       Checkpoints: restart the job from the place where the error has happened. This is specially critical for those companies where data is been consolidated, cleansed and thereafter GST reports have been developed. For example if there was a problem with the rate or Unit of measurement extraction in the container at row number 450. The check point shall ensure that the jobs shall be restarted from row number 451.

How is the Turbodata ETL solution better than sql solutions/custom solutions for GST filing:

•          Built in data consolidation facility: handle missing extraction inputs. Automate data extraction from multiple data sources.
•          Data cleansing before GST reporting: use the error handling facilities to load the data. The unclean data is thereafter corrected by the end user at a date and time of its convenience
•          GST reports using standard GST logic across all ERPs
•          Re run the tasks from the place where error has happened: extremely critical for those customers that have multiple data sources. This is important for those customers that have large scale implementations.
•          Develop the GST reports based on your criteria: critical for those customers who desire to develop their own reports(based on their own business logic)
•           GST reconciliation with ease and convenience: data cleansing, data consolidation, data auditing and data profiling has been done. GST reports have been filed using standard business logic.

Contact:

Sharad Maheshwari, Apoorv Chaturvedi

Email: sharad@govisory.in, apoorv@mnnbi.com, tureasesolutions@gmail.com

Load of the datawarehouse without the lookup transform-SSIS

               Load of the datawarehouse without the use of lookup tables

This particular module has been developed for those customers where the lookup tables are very large and the join transformations shall help process the information faster than lookup. In this particular case the ETL team has replaced the lookup with join transforms.

Note: This method cannot be applied for type 2 entities

This method currently handled insert, updates and deletes.

The solution shall be developed over 3 data flows as given under:

In the supply load dataflow the ETL team has developed the methodology for inserts, updates and deletes as follows:

Method for handling inserts and updates

·         Join the source and the target table using the merge transform based on the composite keys. The end client could pick up the surrogate keys based on the same.

In case of the insert, the identity key column should be checked for the identity row insert.

For updates the table data is stored in a copy of the target table. Thereafter the update sql is run for the same using the sql transform.

Handling deletes:

In the case of deletes, the ETL team looks for the composite key details in the target table and the given details should not be in the source table. In case the given condition is applicable then the row in the target table is marked as ‘D’(delete flag)

Contact details:

For resource requirements:resources@mnnbi.com

For training and consulting services: apoorv@mnnbi.com

Blogspot details: https://mndatasolutionsindia.blogspot.com/

Written by Gauri(gauri@mnnbi.com)

https://mndatasolutionsindia.blogspot.com/p/gauri.html

Deployment of SSIS package

Deployment of SSIS package

This note intends to address the following issues with regards to deployment of the SSIS packages.

·         Migrate the packages from one environment to another-development to integration to production

·         Code the job dependency-once a job is completed the second job is to begin.

·         Handle multiple database connections across different environments.

·         Handle errors while execution of the jobs

Attached is the flow of steps for the deployment of the package.

1.       Build the package

2.       Convert to deployment model.

3.       click on ‘Properties’

Change the deployment utility to ‘true’.

4.       Creation of the Manifest file

5.       Run the package installation wizard.

6.       go to next and check on ‘file system deployment’

7. go to ‘next’ and select that path where you want to deploy your package

8.       then go to ‘next’ and ‘finish’ and your deployable package is ready.

Attached are the other blog links:

Blog link: https://mndatasolutionsindia.blogspot.com/

Website: www.mnnbi.com

Linkedin page: https://www.linkedin.com/in/mn-business-intelligence-53ba285b/

Facebook page: https://www.facebook.com/Turbodatainventory/posts/292678764678735

email: resources@mnnbi.com;apoorv@mnnbi.com

.      

Scheduling in SSIS

                                             SCHEDULED PACKAGE IN SSIS TOOL

The SSIS packages can be automated and scheduled with SQL Server agents.

The method that we have used for automation is through the use of SQL Server agent.

Schedules the job by running sql server agent job and the same is shown below.

After creating the job define the name and go to step , specify the field inside new keyword.

In job configuration, set the properties optionally click ok to save when executing the package manually.

In the next step we can opt action on the time of success or failure of the job inside the advanced page. When the job executes successfully by default it go to next page otherwise quit the job and report at the time of success or quit the job and report at the time of  failure. Likewise on the failure action by default quit the job and report the failure when the errors occurs otherwise go to next page or qiuit the job and report the success.

Retry attempts is used when job jails due to some reasons such as network connection problem.When connection works properly retry all the attempts again.

In the next step schedules the execution time of package we can opt one of the schedule type like recurring which is used to execute periodic events or we can choose one of these three options also according to task requirement. Set the frequency of the job occurs like weekly,daily,monthly and hourly.

Sql server agent fires an alerts on an event and response to that event .sql server agent can monitor performance and windows management instrumentation events .

Alert  willll be raised on the basis of multiple conditions like severity level,message number,WMI events.

Contact details:
For resource requirements:support@mndatasolutions.com
For training and consulting services: support@turbodatatool.com
Blogspot details: https://mndatasolutionsindia.blogspot.com/

Phone:+91-8802466356

Lookup Transform

In this particular case, the ETL team devised a job for converting the sql code for loading the order master table in its product with SSIS code. For the surrogate key upload, the ETL team used the lookup transform.

Reason for converting the code to SSIS:-

• ·         Increase the speed and ease for debugging the error and audit errors. The ETL team intends to add features such as auto correct load(re starting the job once the error takes place from the place the error has taken place)

• ·         Enable easy connectivity with large number of  ERPs
• ·         Help automate the process over a large number of deployments with SSIS.

  Attached blog indicates how one can convert multiple join used for ssis lookup transform loading        the Data warehouse into SSIS code.

The input sql code was converted to cte (or common table expressions). These common table expressions formed the procedure that needed to be converted into SSIS jobs.

Understand the joins between the tables and the required columns for developing the required dataflow.

·         Develop the code in SSIS for various join and using SSIS transforms as below:

In this section we indicate the join between the STG_DAYBOOK and Dim_DATE  based on order_date.

·         Pick up the relevant columns from ‘merge join’ after each join transformation.

 SSIS INPUT:-Using merge join transform:-

In this portion join with dim warehouse table get the relevant input:-

Develop the code in SSIS for various joins as below:

SSIS INPUT:-Using merge join transform:-

In this portion join with Dim_Supplier  table get the relevant input:-

SSIS input:-in this portion using the sort transform  on Dim_supplier table and above merge join  

Transform .

SSIS  input :- In this portion  use the merge join transform  based upon the partyledger name  and supplier name.

In this portion  of code is used for the get the unique  voucher type name  from stg_voucher_ type table.

For following condition : voucher  type  would be sales,purchase,journal(the specific voucher types have been extracted using conditional split)

Ssis input:-in this portion  use   the stg_voucher_type  table  and  aaply on  sort transform on this table get the unique voucher type name conditional split transform and apply the above  condition. The final sort was used to remove any duplicates that could arise.

SSIS:-Finally applying the SSIS  Lookup transform and combinebelow column one is available input columns

And  available lookup table based on the composite key and surrogate keys.

Final  table:-

Ssis input:-

The ETL team used the Full cache option to minimize the number of times the database shall be accessed. All the lookup values should be in the lookup cache .

The ETL team used the OLEDB connection manager since the lookup was been done from a local machine.

In case of no mtach the ETL team assumed a case of insert and hence the option of ‘Redirect rows to no match output’ was chosen.

The ETL team has assummed that there are no exceptions to be handled.

In case of ‘no match’ the output was put into another table(that is a copy of item supplier order as below:

The ‘execute sql task’ enables insertion of the non-matched rows into the target table of item supplier order.

Usage of sequucne container for completing the Lookup transformations:

The other tasks to be executed are ‘sending the mail’ etc.

Converting complex SQL Queries into SSIS code

Attached blog indicates how one can convert highly complex sql queries used for loading the

 Data warehouse into SSIS code.

End client requirement: the end client desired that the sql queries used to load the datawarehouse be converted into SSIS code for better auditing purposes and better error logging purposes. The graphical and intuitive visuals from SSIS shall better help manage the complex sql code.

The input sql code was converted to cte(or common table expressions). These common table expressions formed the procedure that needed to be converted into SSIS jobs.

The data flow that was developed in SSIS was a procedure with multiple common table expressions.

Step 1:

·         Understand the joins between the tables and the required columns for developing the required dataflow.

·         Develop the code in SSIS for various joins as below:

·         Pick up the relevant columns from ‘merge join’ after each join transformation.

           

SSIS input:

·         Identify any filter parameters: I have used the conditional split for the filter parameters.

SSIS package:

·         Identify any calculated columns.

SSIS package implementation: derived column.

·         Aggregation with required group by parameters:

SQL implementation:

Finally insertion into the target table.

In this manner the ETL team converted complex sql code into SSIS code for easy maintainability.

Website: www.mnnbi.com

Blog links: https://mndatasolutionsindia.blogspot.com/

Linked in profile: https://www.linkedin.com/in/mn-business-intelligence-53ba285b/

Email: resources@mnnbi.com;apoorv@mnnbi.com