Building a Data Warehouse using SSIS

Following are the normal steps to build a Data Warehouse.

1st Step: Add databases for raw tables, staging tables, and dimensional tables. 

The raw tables are usually the exact copy of the original table. The raw tables can be totally different than the raw tables. You apply business rules, flatten two or more tables into one, mark records for filtering, for raw tables, so that your data are ready for the data warehouse. Dimensional tables can be fact tables or dimension tables that come together in star, snowflake, and constellation schemas. You can place the Raw and Stage tables in the same staging database. And place dimensional tables into Data Warehouse database, which will be used by SSAS cubes directly.

2nd Step: Install BIDS for Visual Studio.

You need to build BI development environment under Visual Studio. If you worked with SQL Server 2012, it's better to install BIDS 2012 for Visual Studio 2012, which is by far the most stable and popular BI development environment. BIDS will install SSIS, SSAS, SSRS packages for all of your BI developing requirement.

3rd Step: Move source data into Staging DB raw tables using SSIS.

You need to create a new SSIS project at first. Then you need to add new connections for DestinationConnectionOLEDB and SourceConnectionOLEDB within the SSIS project for the Staging DB and Source DB. 

4th Step: Design Staging tables using demoralization on raw tables.

Transactional database always do normalization into 3NF (The Third Normal Form) because loading data should be very quick. Unlike transactional database, data warehouse don't need quick data load, the primary usage is reporting and ad-hoc query, thus denormalization or flatting would be required. 

For designing data  warehouse, how to identity fact table is essential. As a general rule, transactions and events from the transactional database become fact tables,whereas lookup tables and profile data become dimension tables. The fact tables becomes the center of the data warehouse model.

Following figure showed a transactional database model.





















Following figure showed a data warehouse database model.

During the denormalization process, Color and Color Group have been combined into one Color table. Product, Line, Brand, and Manufacturer have been combined into one Brand table. Order and OrderDetail has been combined into FactOrders table. In addition, because report staff needs orders by zip codes rather than by individual customers, thus a new Geography table is created based on Customers and CustomerAddress table, and the left data of the last two tables has been combined into FactOrders table. Finally, Pattern and Style have very little change during the demoralization process. 

Note denormalization can improve query performance. This link can show you more detailed information. 

5th Step: Move raw tables into staging tables using SSIS.

Source:

http://sqlmag.com/sql-server-2008/ssis-novices-guide-data-warehouses-moving-data-data-warehouse
http://sqlmag.com/sql-server-integration-services/ssis-novices-guide-data-warehouses-flattening-while-staging-data

Attach Local Database Data File

You can easily attach local MDF database data file within Microsoft SQL Server Management Studio. But after you installed the SQL Server successfully and run the SQL Server Management Studio at first time to do attach your local MDF data file, you would experience the following issue.

It happened because you start the SQL Server Management Studio with your normal account, which usually don't have administration privilege. 

Just simply open the SQL Server Management Studio under Administration, and you can attach the file without any problem. 

Visual Studio 2013 has compatibility issue with BIDS 2012

Regarding the BI development environment for Microsoft technology, BIDS, named Business Intelligence Development Studio, is the must-have tools for Visual Studio as it will add SSIS/SSAS/SSRS development resources such as specific project type for BI, variety of designers, tools, and wizards to work with these specific project. Just like other software packages from Microsoft, there are compatibility issues.

BIDS has many versions. The latest 2 versions are 2012 and 2014. BIDS 2012 contains SSIS 2012/SSAS 2012/SSRS 2012, and BIDS 2014 contains SSIS 2014/SSAS 2014/SSRS 2014. BIDS 2012 includes SQL Server version 2012, and BIDS 2014 includes SQL Server version 2013. If your working DB is SQL Server 2012, you should use Visual Studio 2012 with BIDS 2012. If your working DB is SQL Server 2013, you can use Visual Studio 2014 with BIDS 2014.

The issue is here, you can't use BIDS 2012 and Visual Studio 2012 if you want to use SQL Server 2013. The reason is BIDS 2012 SSIS part will deploy final applications only based on SQL Server 2012, which will not work with SSIS 2014 and SQL Server 2013.

Also, if you want to install SQL Server 2012 locally along with Visual Studio 2012 Express and BIDS 2012, the installation order is important as well. You need to install Visual Studio 2012 Express and BIDS 2012 at first, and BIDS 2012 will install included SQL Server 2012. After this, you can then install your own SQL Server 2012. If you don't follow the order, for example, you installed SQL Server 2012 at first, the BIDS 2012 installation will fail because the included SQL Server 2012 will not override the pre-installed SQL Server 2012.

ArrayLIst VS List

ArrayList is an array with ability to resize.

Each ArrayList element is an object. ArrayList simply stores object references. Thus you can store a value type into an ArrayList, however, you have to incur box/unbox while accessing those value type.

Exception could happen if the object to be added has a wrong type.

ArrayList array1 = new ArrayList();
array1.Add(1);
array1.Add("Pony"); // No error at compile process
int total = 0;
foreach (int num in array1)
{
    total += num;   //-->Runtime Error
}

You can use List<T> to avoid above error.

List<int> list1 = new List<int>();
list1.Add(1);
//list1.Add("Pony"); //<-- Error at compile process
int total = 0;
foreach (int num in list1 )
{
   total += num;
}

ArrayList now is replaced with List<T>.

List<T> is a generic class. It supports stores value type without casting. As a generic collection, it implements the generic IEnumerable<T> interface and can be used easily in LINQ.

Source: http://stackoverflow.com/questions/2309694/arraylist-vs-list-in-c-sharp

How to use LINQ to select an object with order by and group by

Context.Facility
       .Select( c=> new CityModel {ID = c.CityID, Name = c.CityName} )  //Column Order must be same as following GroupBy columns
       .OrderBy ( c=> c.Name )
       .GroupBy ( c=> new { c.Name, c.ID } )  //Multiple Column GroupBy
       .Select  ( c=> c.FirstOrDefault() )    //To filter out repeating
       .ToList();

Javascript Prototype

Every JS object has a prototype.

All JS objects inherit their properties/methods from their prototype.

The standard way to create an object prototype is to use object constructor function:

function person(first, last age) {  
    this.firstName = first;
    this.lastName = last;
    this.age = age;
};

var farther = new person("Frank", "Zhang", 40);

farther.Country = "Canada"; //Add methods/property to the exist object

father.name = function () {
    return this.firstName + " " +this.lastName;
}

You can extend the object with new function and new properties as above. However, the new added function/property are limited only for the class you defined as above.

If you want to add function/property for all person object, you need to user prototype.

person.prototype.name = function () {
    return this.firstName + " " + this.lastName;
};

Last but not least, Never modify the prototypes of Standard Javascript Objects.

Javascript Revealing Module Pattern

The Revealing Module Pattern is based on a pattern referred to as the Module Pattern. It makes reading code easier and allows it to be organized in a more structured manner. The pattern starts with code like the following to define a variable, associate it with a function and then invoke the function immediately as the script loads. The final parenthesis shown in the code cause it to be invoked. 

var Calculator = function () {  
    service1 = function() {......}
    service2 = function() {......}
    return {
        myService1: service1,
        myService2: service2
    }
};

You can define which members are publicly accessible and which members are private. This is done by adding a return statement at the end of the function that exposes the public members. 

Source: http://weblogs.asp.net/dwahlin/techniques-strategies-and-patterns-for-structuring-javascript-code-revealing-module-pattern