Lookup Transform

 -                  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.

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

Optimize Construction Lending Costs using the principles of 'the Goal'

 How to use ‘The Goal’ Philosophy to optimize resources for construction lending

The given note is based on the philosophy of ‘The Goal’ by Eliyahu Goldratt. ‘The Goal’ concentrates on the constraints faced by the firms in terms of resources.

Effect of delay between milestone achievements: The variance between the actual and the target dates in achievement of the milestones results in additional costs which result in increased cash outflows for the end client.

Hence it becomes important to predict the impact of variances between the actual and the target delivery dates and give the management multiple options in case of delays happening in achievement of milestones.

Predicting the costs of delays:

The following is the step by step guide for resolving the above issues:

·         Track the target versus actual milestones for the construction projects. In such a scenario the target dates need to be fixed. Accordingly the variances shall be calculated based on the achievements of the actual versus the target dates. If a software is not available for data capture then the software could be developed for the same.

·         The costs associated with the delays shall need to be tracked using dashboards. For the same data connectors are available with the ETL team for data consolidation, data transformation, data cleansing and data auditing.

·         Based on the cost of delays, hypothesis shall need to be developed for optimization purposes.

·         Best case and worst case scenario: based on the variances for the project implementation, the best case and the worst case scenario for each project stage shall be developed. The most probable cost shall be arrived based on the same.

·         Allocation of resources based on maximizing the NPV of the project: based on the expected variance in the cost of the project, the expected NPV is to be calculated. The projects having the maximum NPV shall be addressed first.

·         Crashing of the activities to meet the project deadlines: in case of delay and a set delivery date for the project, route optimization techniques to be used to meet the required deadlines at minimal costs.

·         Optimization of purchase costs based on delivery dates, delivery requirements of the end client.

    Other activities: purchase optimization through Turbodata
     Ledger optimization

Presented by:

Apoorv Chaturvedi

Email: apoorv@mnnbi.com
Blog: http://mndatasolutionsindia.blogspot.in/
Website: www.mnnbi.com

Other website links: http://www.mnnbi.com/mnnbi_constructionanalytics.html

Phone: +91-8802466356

Why Turbodata GST?

 Inspired by 22 Immutable Laws of Marketing: AL Ries

Based on the above philosophy, the following are the reasons one should look at Turbodata GST as a GST filing solution.

·         Law of Focus: The end client should have one word embedded into customer’s mind. In such a scenario most of the end clients have Tally embedded into their minds for GST filing. Turbodata GST matches the numbers with Tally GST reports to give customers peace of mind and satisfaction.

·         Law of opposites:

o   Turbodata offers cloud based GST filing system while Tally offers desktop/server based GST filing system

o   Turbodata enables faster, easier and more convenient data upload facilities than Tally ERP 9.0.

o   Turbodata offers historical data correction automatically for GSTR reports while in case of Tally the end client shall have to re file the offering.

o   Turbodata GST can work with multiple ERP systems and is extremely scalable.

·         Law of Ladder: Tally has top of mind recall for the customers for accounting accuracy. Turbodata GST team recognizes the same. It matches the GSTR reports with those of Tally GSTR while making it easier and more convenient than Tally to file GST taxes.

·         Law of Mind: Tally stands for accuracy while Turbodata stands for speed matching with Tally numbers. Turbodata GST offers GST filing services matching with Tally ERP 9.0 for any ERP.

For other details regarding the GST filing, please consider the following page linkage:

http://mndatasolutionsindia.blogspot.in/2018/01/automated-gst-Turbodata.html

Contact:

Name: Apoorv Chaturvedi

Email: apoorv@mnnbi.com

Website: www.mnnbi.com

 For GST filing services, do the following:
email apoorv@mnnbi.com
Or fill up the contact form on the website  http://mnnbi.com/.

Developing automated consolidated trial balance for 36 companies in the trading domain

 Developing automated consolidated trial balance for 36 companies in the trading domain

Problem: the end client required consolidated ledger balances and balance sheet details across 36 companies. With the given software that the end client had the process was taking a lot of time. The system would hang during the process of consolidation and generation of the required reports.

Methodology of the ETL team: the ETL team consolidated data ledger data from all the 36 companies. In order for the end client to generate balance sheet/trial balance details on any fiscal date the ETL team did the following activities:

·         Perpetual ledger balance details were stored by partyledgername and ledgername.

·         The associated cost center details for the ledger were also stored. The Profit and Loss statements could be generated according to the cost center details.

·         The ETL team was able to generate the balance sheet details, trial balance details across all the companies.

·         The end client could get the access to the balance sheet details across multiple companies.

The following system was used to match the trial balance details:

·         Data audit: the ETL team used the perpetual ledger balance details to arrive at the closing ledger balance details on the given fiscal date. The closing ledger balance on the given fiscal date was matched with the trial balance details from the software. The software was able to handle the cases where opening ledger balance was non zero.

Final result:

·         The audit numbers of the resulting output were matching with the software output.

·         The report refresh times was crashed by more than 90%(ninety) percent

·         The software did not hang during the process of initial and incremental data load and during the process of report generation.

Other benefits to the end client:

·         Better scope of cash flow availability: since the end client is having the cash flow balances on each fiscal date, hence the end client is able to capture the variances in payments across all ledgers. This helps the end client at better planning of the cash flows.

For the process of data consolidation, the following actions were done:

·         Data cleansing

·         Data consolidation

·         Data transformation

·         Report generation using C#/.net interface.

       Further ledger analysis was done as given in the following link:
       Ledger analysis link

Prepared by :

Apoorv Chaturvedi

Email: support@mndatasolutions.com;support@turbodatatool.com

Phone: +91-8802466356

Blogspot: http://mndatasolutionsindia.blogspot.in/

Ledger analytics/Ledger analysis

 Ledger Analytics

Problem statement: a number of firms use periodic statement for ledger analysis(monthly, quarterly and yearly). In such a scenario these firms loose the day by day and transaction by transaction history of ledger balances. This information is required for the ageing analysis, in depth accounts receivable analysis per ledger. As an example, consider the following:

The above snap shot indicates the ledger balance on any fiscal date by partyledgername and ledger name(the group name is a roll up). From the daily ledger balance, the end client should be able to extract the trial balance, balance sheet and even profit and loss statements.

As an example consider the following snap shot:

Because of keeping the ledger balance history, the end client is able to find the cash balance as of the given fiscal date. Thereafter it has been able to capture the cash balances on a monthly, yearly, quarterly basis as given below:

Monthly report:

Daily Report:

Pre requisites for achieving the same:

The historical ledger balances need to be calculated and the closing ledger balance on the current fiscal date shall need to be matched with the ledger balance on the last day of the ledger balance history table as given below:

Alternatively the debit and credit balances need to be matched as given below:

The process replicates the ledger balancing related with bitcoins.

For achieving the same the end client needs to do the following:

• Data capture 
• Data cleansing
• Data consolidation
• Data transformation

The ETL team would be also able to offer Business Intelligence and predictive analytics services along with ledger analytics.

Ledger analytics is also related with GST filing.

Further case studies for consolidation of data can be seen from the following link.

Apoorv Chaturvedi

Email: apoorv@mnnbi.com

Email: www.mnnbi.com

Blogspot: http://mndatasolutionsindia.blogspot.in/

GST Filing Services-Turbodata

 Automated GST Filing using Turbodata and GSP Partners

Are you facing the following issues with regards to GST filing?

• ·         Delay in filing
• ·         Concern regarding the changing regulations from the government
• ·         Concern regarding reconciliation: specially for customers using MS Excel upload.
• ·         Have a manual process for GSTR filing. The manual process is prone to error
• ·         Have high manpower costs related with GST filing.

Turbodata shall help your firm with faster, easier and more convenient GST filing.

How is Turbodata different?

• ·         All the reports for the end client shall be developed on the cloud installation. Only a minimal extract for all the vouchers and masters shall be done from the end client location. The ETL team shall commit to usage of maximum amount of RAM for the same(say 1 GB for incremental data extract)
• ·         The end client can do the prior change of the data. The system shall automatically take care of the same. This is enabled through incremental data load process using data normalization.
• ·         No reports shall be developed at the client location. All the reporting work shall be done at the server location.
• ·         Initial and incremental transaction data extract shall be done from the end client location.
• ·         The end client need not worry about re filing the GST reports since it shall be done by the GSP partner automatically.
• ·         The package is very easy to deploy, deliver and maintain. No high end software are required. The system can extract data from SAP, Tally and other source systems with ease.
• ·         Dependence on MS Excel for tax filing purposes is taken away since it could result in data errors and discrepancies.

  Current system:

Why is the Turbodata system better?

Turbodata system:

·         Turbodata system is inspired by ‘The Deming Way’, ‘The Goal’ and the Toyota production system and the Inmon methodology.  In a nutshell the following are the features copied from the above systems by Turbodata:

o   No error prone data should be passed for the reporting purposes. The data needs to be cleansed, audited and consolidated before report development.

o   The processing of the transaction should be done as soon as the transaction has been fed in the source system. That is the processing should take place on a real time basis and not specifically at the end of the month. Turbodata enables this feature in the following manner:

§  Each transaction fed into the end client source system is assumed to be an order from the end client.

§  The system offers the facility for real time extract and upload(current system is manual but the data can be loaded on a daily basis by the end client go the server)

o   Once the data has been loaded onto the server, it is transferred to a normalized database(insert, update and deletes). At the data warehouse level the data cleansing, data transformation, data consolidation activities are done

o   Once the data has been cleansed at the datawarehouse level then the reports for GST are developed. In one single lot, GSTR1, GSTR2 and GSTR3 reports can be developed.

o   Turbodata is integrated with at least one GSP partner. The end client could look at other GSP partner solutions if it desires the same.

o   The deployment of the solution is very easy and convenient. For any end client the deployment should take not more than 20(twenty) minutes. Minimum installation pre requisites are required.

o   The data for the end client is stored in a datawarehouse. The end client does not need to worry about changes in the statutory requirements. Other high end services like inventory optimization and predictive analytics are possible on the cloud.

To check why should the end client consider Turbodata GST, please check the following linkage:
http://mndatasolutionsindia.blogspot.in/2018/02/why-turbodata-gst.html

GST Reporting Issues:

Contact

Apoorv Chaturvedi

Phone: 8802466356

Email: support@mndatasolutions.com;support@turbodatatool.com

Website: www.mnnbi.com

Indicate the following:

·         ERP system/ERP systems

·         Turnover: frequency of load

·         Number of locations

  Sample video link: https://www.youtube.com/watch?v=sYbeBfc3ozo&feature=youtu.be

       The product uses optimum RAM so that the source system does not hang during extraction as given in the following video:
       https://youtu.be/7CULkzc5h2g

FOR FREE MICROSOFT POWERBI DASHBOARDS: please do one of the following:Email: support@mndatasolutions.com;support@turbodatatool.com