Author: Sneha Valia
Tally.ERP 9
Tally is at the forefront of simplifying business management for business through automation. Tally.ERP 9 is a windows–based Enterprise Resource Planning software. The software handles Accounting, Inventory Management, Order Management, Tax Management, Payroll, Banking and many such requirements of the business. It supports all day-to-day processes from recording invoices to generating various MIS reports.
Tally’s product caters to millions of users across industries in over 100 countries in the past three decades since it was founded.
Microsoft Power BI
Microsoft Power BI is a suite of business intelligence (BI), reporting, and data visualization products and services for individuals and teams. Power BI stands out with streamlined publication and distribution capabilities, as well as integration with other Microsoft products and services.
Integration using ODBC Interface
Open Database Connectivity (ODBC) is an interface for accessing data in a heterogeneous environment of relational and non-relational database management systems. It is an Application Program Interface (API) specification that allows applications to access multiple database systems using Structured Query Language (SQL). ODBC provides maximum interoperability a single application can access many different database systems.
Tally.ERP 9 is an ODBC enabled application. The Tally ODBC Driver could also be a strong tool that allows you to connect with live data from Tally, directly from any applications that support ODBC connectivity. Also, you can access Tally data like you would access a database – read, write, and update Tally SalesOrders, PurchaseOrders, Accounts, etc. through a typical ODBC Driver interface. With Open Database Connectivity you can exchange data between Tally ERP 9 and any other application dynamically. Using the ODBC Interface, Tally.ERP 9 can make ODBC calls to an external database and retrieve data from them. In such a case Tally acts as a Client to pull Data from disparate Data Sources. This data can be consumed in Tally as per requirement.
Let’s go ahead and see the demonstration of connecting Tally Data to Power BI using Tally ODBC Driver.
Step 1: Set the following connection properties to connect to Tally Instance. Press Ctrl + Alt +F for Configurations.
Step 2: In Client Server Configuration, Goto → Tally ERP 9 is acting as → Both.
Step 3: Press Enter for rest of the settings and lastly in Accept window select yes to save the settings.
Step 4: Open Power BI → Select Get data option → More…
Step 5: Goto Others → Select ODBC → Click on Connect.
Step 6: In Data Source Name, Select TallyODBC_9000 → Press OK.
Step 7: In Navigator Screen → ODBC dsn → Click ODBC(dsn = Tally_9000) → Select the dataset → Build visuals.
For direct extraction of data from Tally, ODBC method is the most optimum method. But a challenge to this is the limitations of Power BI. Power BI can only Load and Transform 60 thousand rows which is not an appropriate feature for large datasets. And hence Azure Data Factory is used to overcome the limit of records that can be processed.
Let’s see how …
Azure Data Factory
Azure Data Factory is a platform that solves data situations. It is the cloud-based ETL and information combination service that permits you to make information driven work processes for coordinating information development and changing information at scale. Utilizing Azure Data Factory, you can make and schedule information driven work processes (called as pipelines) that can ingest information from divergent information stores.
The Tally Pipeline:
The Tally Pipeline consists of a ‘ForEach’ Activity, which includes one copy activity. The parameter defined for the pipeline consists of the source table name, the destination table name and the script. The copy activity picks up these values from the parameter and then completes the data copy process from Tally ERP 9 software to SQL database. A trigger makes sure that the pipeline runs on a pre-defined schedule.
Before you start with this exercise, you will need to have the following prerequisites:
- You need to have an active Azure Subscription.
- Azure Data Factory – You need to have a data factory created in azure, integration runtime beforehand and two linked services.
- In Overview, Click ‘Author & Monitor’ to begin creating datasets and pipelines.
Steps to create the Resources –
Step 1: Goto Author → Datasets → Click on three dots at the right of the Pipeline actions → New Dataset.
Step 2: Select ODBC → Click on Continue → Select the linked service, integration runtime and table → Finally test the connection.
Step 3: Repeat Step 1 & Select ‘SQL Database (to sink dataset to SQLserver)’ for this step of creating dataset.
Step 4: Goto Pipelines → Click on three dots at the right → New Pipeline.
- Under Activities → Goto Move & Transform → Drag Copy Data → Select Copy Data.
- In below Goto Source, mention Source dataset. Source dataset will be the dataset that we created in Step 1 & 2.
Step 5: Goto Sink option and mention the sink dataset.
Step 6: Once you have mentioned source and sink → Click Validate and Validate Copy runtime options.
- After validation, click Debug.
- Debug feature runs the Pipeline and in output tab we can see the status of the pipeline.
- If Status shows ‘succeeded’ , your pipeline has successfully implemented the copy activity.
Conclusion – Accesing Tally ODBC Driver and storing data in SQLserver with the help of Azure Data Factory (for large datasets of Tally),overcomes the limitations of Power BI. Also you get live connectivity to your Tally data by connecting SQLDatabase in PowerBI, allowing you to build real-time charts, graphs & more.
