Data Engineering Technical Blog

When we think about dimensional modelling then two major and very important modeling schema comes into picture called the start schema and the snowflake schema. So lets go into details on start and snowflake schema. Star schema What is star schema? The star schema architecture is the simplest data warehouse schema. It is called a star schema because the diagram resembles a star, with points radiating from a center. The center of the star consists of fact table and the points of the star are the dimension tables. Usually the fact tables in a star schema are in third normal form(3NF) whereas dimensional tables are de-normalized. Despite the fact that the star schema is the simplest architecture, it is most commonly used nowadays and is recommended by Oracle . Snowflake schema What is snowflake schema? The snowflake schema architecture is a more complex variation of the star schema used in a data warehouse, because the tables which describe the dimensions ...

TYPES OF DIMENSIONS Conformed Dimension: Conformed dimensions mean the exact same thing with every possible fact table to which they are joined. Eg: The date dimension table connected to the sales facts is identical to the date dimension connected to the inventory facts. Junk Dimension: A junk dimension is a collection of random transactional codes flags and/or text attributes that are unrelated to any particular dimension. The junk dimension is simply a structure that provides a convenient place to store the junk attributes. Eg: Assume that we have a gender dimension and marital status dimension. In the fact table we need to maintain two keys referring to these dimensions. Instead of that create a junk dimension which has all the combinations of gender and marital status (cross join gender and marital status table and create a junk table). Now we can maintain only one key in the fact table. Degenerated Dimension: A degenerate dimension is a dimension whic...

Step by step to add custom code in SSRS Introduction SSRS custom code extends re-usability of certain logic in multiple places inside a report. It also helps get rid of writing lengthy calculations in many places with a function. SSRS 2005/2008R2 allows us to write custom codes in VB. If somebody is familiar with C# can choose another option called - Custom Assembly. We will create a sample report with a simple Custom code which will accept two numbers and return the greater of two. So for the sample report I have a given table  Test table with three columns A,B,C Now we will try creating a report from the source data so first we will add a new report in BIDS SSRS project. create proper data source and data set referring to the above table. Now first we will go to Report Property and will click on the Code portion and write the below code. After writing the code we will drag a table and will specify all the columns and will add one extra column that will...

Merge join without SORT Transformation Merge join requires the IsSorted property of the source to be set as true and the data should be ordered on the Join Key. So when we add a SORT transformation it sets the IsSorted property of the source data to true and allows the user to define a column on which we want to sort the data ( the column should be same as the join key). Now to avoid the using SORT transformation we need to set the metadata of the source properly for successful processing of the data else we get error as IsSorted property is not set to true. We will try to join two tables Department and Employee on DeptID column without using SORT transformation in our SSIS package. Department Table details Employee Table details Steps in SSIS package Create a new package and drag a dataflow task. Now right click on Data-flow and click on edit, the data-flow container opens. First task is to create a connection to the database.    Now w...

For retrieving the SQL Server Reporting Services configurations using custom program in C# or .NET we need to use WMI (Windows Management Instrumentation) script. The process involves the following steps. RETRIEVING WMI NAMESPACE FOR SSRS For retrieving the WMI namespace we write the following function.   public static string GetNamespace( string machineName)  {    string rSroot = @"root\Microsoft\SqlServer\ReportServer" ;    string strNamespace = "" ;    System.Management. ManagementClass mc = new ManagementClass ( new     ManagementScope ( @"root\Microsoft\SqlServer\ReportServer" ), new ManagementPath ( "__namespace" ), null );             foreach ( ManagementObject ns in mc.GetInstances())             {                 if (ns[ "Nam...