Business Intelligence is simply the transformation of raw data into the useful Business form.
SSIS stands for SQL Server Integration Services. This Integration Service plays a major role in our daily life. Integration is defined as the collection of data from various Fields. Why do we need Integration?
The data in a company will not be in the single unit. This company experiences the data from different departments. In order to analyze the company progress, it is not necessary (or) not sufficient if we get the data from the department. We will be getting the company progress only if we combine all the data from multiple departments.
Know more on MSBI from OnlineITGuru through msbi online Training.
This Integration Services will be provided by many people today. Among them the Integration Service provided by Microsoft is different.Let us discuss How Integration happens in MSBI. This Business Intelligence will be applied by Microsoft in three different ways namely SSIS, SSAS, SSRS as shown below.
In this article, we are going to know How SSIS plays a major role in MSBI?
As explained above data will be from multiple sources in multiple forms. The data may appear from different fields like flat files, excel files, databases etc. All these data from this source may be in homogeneous from (or) in Heterogeneous form. There is no problem if the data is in Homogeneous form. Problems arise only if the data is in Heterogeneous form. In this format, data needs to be combined from various formats into one form. Analytics and Analyses will be done in the changed form.
After transforming the data into one form, it needs to undergo ETL Process (E- Extract, T- transform, L-load). This ETL process happens in the following format.
Extractions refer to the process of collecting data from multiple sources like ERP products, Databases and many more. After extraction, the data must be scrubbed in order to remove the malfunctions of the code. After this process, data will be transformed into the desired format, in order to load into the Data Warehouse.This loaded data will be retrieved by the end users in order to know the future analyses. Sometimes, this data is will be moved into OLAP (Online Analytical Processing) Cubes.
There are three components in SSIS namely Import and Export Wizard, SSIS designer and SSIS API programming. The import and export wizard simply transfers the data but does not include the transformation capabilities. The Microsoft Business Intelligence development studio contains the integrated package of SSIS which is used for development and maintenance of integration services packages. The advantage of this package is that Its API module allows you to code SSIS packages using the number of various numbers of programming languages.
Need of Packages:
The need of packages will be explained with the following example:
Suppose we have oracle data and SQL server on different continents. For a particular project, we need the data from these two sources. In order to fulfill this requirement, we need to create a single (or) multiple projects in the packages. This package contains the data from these two databases where the end user can retrieve the data from one (or) multiple data sources (or) packages in order to achieve the task.
This package is of two types. In a first way, the developer can write a program as per the client requirements. In a second way, he can just drag and drop from the tool palate in order to provide a good UI to the people. With these packages, it allows the user to create a workflow of the process and task. With the workflow models, the analyst can analyze pros and cons done in the past and have a clear idea on what were the steps that need to do for the good career of the business. And the additional feature is that it allows the color coding and real-time monitoring.
My dear readers, I hope you got an idea of How SSIS plays a major role in MSBI.
It’s better to have a fundamental knowledge of keeping up and examining them for implementation. No Programming abilities were required to take in this course. It’s great to have knowledge of the utilization of Excel or the worksheets to understand the whole design of MSBI. Checkout in OnlineITGuru now MSBI online training Bangalore.