Monday, 30 December 2013

Measures, Attributes and Hierarchies


       A measure represents a data column which contains quantifiable data, usually numeric, that can be aggregated. A measure is generally mapped to a column in a fact table.

      In SSAS measures are grouped by underlying fast tables, which called Measure Groups. Measure Groups are used to associate dimensions with measures.

      Measure are selected while creating Cube, which can later be modified for various usage like its formatting, aggregate behaviour etc. Click here to see how to modify measure for formatting. 


       Attribute represent one or more column in dimension table. Attribute is used to particular expect of the dimension. For example product colour  represent colour attribute of the product dimension, which can later be used for slicing/dicing/selecting the cube values. Each dimension in cube contains one Key attribute, which is generally primary key in dimension table.

     Click here to see how to define/update attribute for dimension.


       As name suggest Hierarchies is used to define hierarchy in dimension. For example Product and Product Category, National and International region etc. Hierarchy can be defined in Hierarchies table in cube design.

     Click here to see how to add/delete user-defined Hierarchy.

Monday, 16 December 2013

Tool to view big data file

I have to load big data files using SSIS or SQL server Import/Export wizard. Most of the time it works fine but if it fails and I have to view the data to debug then it is simply impossible to view the data because most of the text editor cannot open file which too big (size in multiple of GBs). So  I have developed small tool which can help to read or view a big file which cannot be opened in normal text editor.  This tool can also be use full if you are developing SSIS package to load big file and want to create small sample file from big file during development.

Click here to download the tool.

Features of the tools

 - Load big file (no limit on size)
 - View data page by page. Page size is configurable.
 - Save page in different file.
 - Go to any page in file.
 - Displays line number across  pages.

 - It uses Temp directory to store data pages while opening file. So virtually it can occupy similar disk space as the original file.
- It can only read file line by line. So if lines are not separated by {CR}{LF} then it cannot do data paging.
- It can only work with ANSI data.

Read big data file | View big data file | Open big data file | tool to open big file | tool to read big data file | Preview big data file | Get sample data from big data file | Read specific data from big file | View specific data from big file

Dimension and Cube

        A database dimension is a collection of attributes usually related objects, which can be used to provide elaborated information about fact data in cube(s). Typical example of attributes in a Employee dimension might be Employee name, Employee Designation, Employee Qualification, Joining Date etc. Dimensions are linked to one or more columns in one or more tables in a data source view. By default, these attributes are visible as attribute hierarchies and can be used to understand the fact data in a cube. Attributes can be organized into user-defined hierarchies that provide navigational paths to assist users when browsing the data in a cube.

Click here for step by step instruction to create dimension in using existing table in SSDT.
Click here for step by step instruction to create dimension in using Dimension wizard in SSDT.

         A cube is a multidimensional object that contains information for analytical purposes; which contains dimensions and measures. Dimensions define the structure of the cube that is used to slice and dice over, and measures provide aggregated numerical values of interest to the end user. As a logical structure, a cube allows a client application to retrieve values, of measures, as if they were contained in cells in the cube; cells are defined for every possible summarized value. A cell, in the cube, is defined by the intersection of dimension members and contains the aggregated values of the measures at that specific intersection.

Click here for step by step instruction to create Cube in using Cube wizard in SSDT.