Pictorial Diagram of SSIS, SSAS and SSRS in MS BI Platform

Pictorial Diagram of SSIS, SSAS and SSRS in MS BI Platform:

Hope you like it.

Posted by: MR. JOYDEEP DAS

SSIS package

In my previous article I am trying to explain related to What is data warehousing. If you don’t read it please follow this link before going to this…

http://sqlknowledgebank.blogspot.in/2012/05/data-warehousing.html

In this article I am trying to explain related to SSIS package.

A Package is the core object within SQL server Integration Services (SSIS) that contains the business logic to handle workflow and data processing. SSIS package can be used to move data from source to destinations and also handle the timing precedence of when thing process.

**BIDS [ Microsoft SQL Server Business Intelligence Development Studio ]

SSIS package can be accomplished by two ways.

Built-in wizard
By using the Built-in wizard in SQL Server 2005 that asks you to move the data from source to destination and automatically generate the SSIS package.


SSIS BIDS
By explicitly create a project in SSIS BIDS. We need to create projects the new package is automatically created and developed.

So we now trying to discuss about our first option and that is

By Built-in Wizard

In SQL Server 2005 we can use the Import and the Export Wizard to Import and Export the data. For Import Wizard the source is the SQL Server 2005 table and destination should be SQL Server database, ORACLE database, Flat file, Microsoft Excel spread sheet, Microsoft Access database.

Exporting data with the wizard lets us send the data from SQL Server 2005 tables, Views or custom query to flat file or database connection.

Initialize the Import Export Wizard

To initialize, please follow this steps mentioned bellow.

What we want to do

We want to import a flat file to our existing database.

1.    Through the SSMS connects to the installed database engine. That should be your source or destination.

2.    Click on view menu select Object Explorer (or press F8). From the database folder select the desired database. Then right click of the desired database and select Tasks. From Tasks we can select Import or Export wizard.

3.    Select the Tasks. If the database is source of data that needed to send out to the different system, select the “Export Data” and if the database is destination for the file currently exists outside the system, than select “Import Data”. Here is this example we are choosing “Import data”.

Database is source of data  à Export Data

Database is destination for the file àImport Data

4.     If we choose any one the “Welcome to SQL Server Import Export Wizard” appears. Then click the next button on the wizard. “Choose the data source” allow you to specify from the data is coming from. Here in this example I am choosing Flat file source and brows the flat file. Please specify others options if needed.

“Choose a Destination” allow us to specify the destination where the data will be sending. We can choose the destination if needed. The server name and the security settings must be specified. If we select a relational database source that allow customer queries.

5.    For now in “Save and Execute” page of wizard we choose the options Execute Immediate for now. In the complete the wizard gives us all the information that we selected. If needed we can go back and modified it. Now use the SQL query to see the result output.

SELECT * FROM <table name>

In my next session we are discussing about saving and Editing Package created by wizard.

Hope you like it.

Posted by: MR. JOYDEEP DAS

Data Warehousing

Lot of my friends and reader asking me to write a tutorial related to Microsoft BI tools. As I personally feel that the Data Warehousing is not just understand or practice via some Tools provided by Microsoft, it need deep understanding analysing with data. Well we can learn the tools very easily but sensing the data and information is quite tough to learn. It’s growing with maturity and hard work. 

Well if readers want me to write something, here I am trying to give them something by my article.

In this article I am trying to understand the concept behind data ware housing. Why we all think about it.

What is the Data Warehousing?

One of the main features of data warehousing is to combining data from heterogeneous data sources into one comprehensive and easily maintained database.

The common accessing systems of data warehousing includes


 Queries


Analysis


Reporting

As the number of source can be anything, the data warehouse creates one database at the end. The final result however, is homogeneous data, which can be more easily manipulated.

Data warehousing is commonly used by companies to analyse trends over time. Its primary function is facilitating strategic planning resulting from long-term data overviews. From such overviews, business models, forecasts, and other reports and projections can be made. Routinely, because the data stored in data warehouses is intended to provide more overview-like reporting, the data is read-only. If you want to update the data stored via data warehousing, you'll need to build a new QUERY when you're done.

We are not saying that data warehousing involves data that is never updated. On the contrary, the data stored in data warehouses is updated all the time. It's the reporting and the analysis that take more of a long-term view.

Data warehousing is not the be-all and end-all for storing all of a company's data. Rather, data warehousing is used to house the necessary data for specific analysis. More comprehensive data requires different capacities that are more static and less easily manipulated than those used for data warehousing.

Data warehousing is typically used by larger companies analysing larger sets of data for enterprise purposes.

Smaller companies wishing to analyse just one subject, for example, usually access data marts, which are much more specific and targeted in their storage and reporting. Data warehousing often includes smaller amounts of data grouped into data marts. In this way, a larger company might have at its disposal both data warehousing and data marts, allowing users to choose the source and functionality depending on current needs.

Hope you like it. In my next session I am directly jump over Microsoft BI tools Introduction and try to discuss when you used them.

Hope you like it.

Posted by: MR. JOYDEEP DAS