Business Intelligence overview using Microsoft SQL

Business Intelligence (BI) it’s all about data. Grab data, view it, make decisions and hopefully make some extra money because you understand what’s happening.

There are three components:

Each of these have a separate role:

SSIS – get / grab your data, either txt files, other databases or xml files

SSAS – analyze your data

SSRS – view your data in a meaningful way

 

The problem

We will presume that:

  • we need to read a log file, which contains error and info. A single line looks like this (“error 02/26/2012 error message 1 today”)
  • we need to transform that file to contain another field, process date, to know exactly the time we’ve processed the log file
  • write that transformation in the database
  • design and deploy a report in Report Server

 

Steps

1. You need first to lookup after SQL Server Business Intelligence Development Studio (more details here)

imageimage

 

 

 

 

 

 

 

 

 

2. Create a new Integration Services Project

Drag a Data Flow Task (in toolbox it’s under Control Flow Items) and double click it to design how should we read data and transform it.

image

 

3. Because we want a transformation of the original log file we will need to add the list of components that is described below and also make sure that this composition describe a valid flow :

image

 

3.1 Use a Flat File Source component (in toolbox is under Data Flow Sources). This component provides functionality like reading plain text from a file, skip header, … .

When you double click on it to edit, flat file connection manager is empty, just click the new to define a new one. Your settings should look like these:

image

image

 

3.2  Add a Derived Column component (in toolbox is under Data Flow Transformations). This component will update columns using expressions.

This component get as input the content of the file that is read  using flat file source. As you can see in the screenshot, under Columns node is a Column 0 item which represents a line from that file. To transform that file we need to apply several expressions on that row.

In our example, we have a Type that can be found from the position 1 to 6. So we apply a substring on the line to obtain that.

image 

 

3.3 After all the transformations are done, we need to write those in another text file. Add a Flat File Destination component (in toolbox is under Data Flow Destinations).

The input for this component is the output of the Derived Column component. We’ve transformed one column 4 columns and we need to write them in a new file.

image

image

image

image

 

4. As per step 2, add another Data Flow Task. This will be in charge with reading the transformed text file and write that in the database.

The end result will look:

image

 

4.1 Add a Flat File Source. Configuration of this component needs to be as in picture:

image

4.2 Add a Data Conversion component (in toolbox is under Data Flow Transformations).

image

4.3 Add a ADO NET Destination component (in toolbox is under Data Flow Destinations). On setting this component, you need to provide a connection string to the database/table to which you want to write.

image

image

 

CREATE TABLE [dbo].[RawDataLog](    [Id] [int] IDENTITY(1,1) NOT NULL,    [Type] [nvarchar](50) NOT NULL,    [EventDate] [datetime2](7) NOT NULL,    [Message] [nvarchar](max) NOT NULL, CONSTRAINT [PK_RawDataLog] PRIMARY KEY CLUSTERED     (        [Id] ASC    ))

.csharpcode, .csharpcode pre{ font-size: small; color: black; font-family: consolas, “Courier New”, courier, monospace; background-color: #ffffff; /*white-space: pre;*/}.csharpcode pre { margin: 0em; }.csharpcode .rem { color: #008000; }.csharpcode .kwrd { color: #0000ff; }.csharpcode .str { color: #006080; }.csharpcode .op { color: #0000c0; }.csharpcode .preproc { color: #cc6633; }.csharpcode .asp { background-color: #ffff00; }.csharpcode .html { color: #800000; }.csharpcode .attr { color: #ff0000; }.csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em;}.csharpcode .lnum { color: #606060; }.csharpcode, .csharpcode pre{ font-size: small; color: black; font-family: consolas, “Courier New”, courier, monospace; background-color: #ffffff; /*white-space: pre;*/}.csharpcode pre { margin: 0em; }.csharpcode .rem { color: #008000; }.csharpcode .kwrd { color: #0000ff; }.csharpcode .str { color: #006080; }.csharpcode .op { color: #0000c0; }.csharpcode .preproc { color: #cc6633; }.csharpcode .asp { background-color: #ffff00; }.csharpcode .html { color: #800000; }.csharpcode .attr { color: #ff0000; }.csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em;}.csharpcode .lnum { color: #606060; }

5. The main flow looks very simple

image

 

Source code: