What are Fact and Dimension tables?

Wednesday, January 27, 2010

Overview
• A multidimensional storage model involves two kind of tables:
1. Dimension tables
2. Fact tables
Fact Tables: A fact table contains the fact data of business like sales amount, quantity, customers and reference keys to dimension tables.



· Dimension Tables:
· A dimension table holds the data that we use to group the value to derive summary.
· A dimension table has primary key.
E.g. Customer table has information about city, state and zip code to summaries geographically sales. A dimension table contains the more detailed information of fact data like product details, customer details, store details etc.


· The fact table mainly holds transaction data.
· E.g. fact table data shows units amount and sold quantities.

Data Modelling for Data Warehousing

Overview
Data Cubes =
A data which are available in multidimensional matrices is called Data Cubes.
· A excel sheet is a two dimensional matrix, it can have sales revenue of corresponding geographical regions.
· Change process of converting one dimensional to another in the data cube is called pivoting.
• In pivoting, the data cube can rotate to see the data in different orientation.
• So, this technique is equivalent to product sales table by region.
• There are two kinds of Hierarchical views of multidimensional models:
1. Roll-up display
In the Roll-up display the hierarchy will move up and it will group into the larger units.
E.g.: Weekly data summation by quarter and by then by year.
2. Drill-down display
In the Drill-down display it will have opposite view.
E.g. dividing the region sales into sub-region and then again dividing sub-region sales country specific sales.

Storage types in Data Warehousing

Tuesday, January 26, 2010

Overview

There are mainly three kinds of storage types based on where the data or facts are stored.
1. MOLAP (Multi-dimensional OLAP)
2. ROLAP (Relational OLAP)
3. HOLAP (Hybrid OLAP)

MOLAP
• In this storage mode the data and aggregated data are stored in multi dimensional cube.
• It processes data which is already stored in multidimensional array with all possible combinations of data reflected, each in a cell that can be accessed directly.
• The main advantage is it can retrieve data very fast.

ROLAP
• The data which is stored in the relational database. Aggregated or summary data is also stored in the relational database.
• Performance can be slow. Because each ROLAP report is essentially a SQL query (or multiple SQL queries) in the relational database, the query time can be long if the underlying data size is large.

HOLAP
• A combination of relational OLAP (ROLAP) and multidimensional OLAP.
• Data will be stored in relational format (ROLAP). Aggregations, on the other hand, are stored in multi dimensional format(MOLAP) in the cube to give better query performance.

Key elements of Data Wahrehousing

Overview
• A multi-dimensional database is created from fact and dimension tables to form objects called dimensions and cubes

Dimensions
•Dimensions are most often made up of several hierarchies
•Examples: time, geography, employee

Hierarchy
• Logical entity by which a business user might want to analyze fact data
• Each hierarchy can have one or more levels
Example: A hierarchy in the geography dimension –Country, State, County, City

Completely Balanced Hierarchy
• All leaf(end) nodes would be an equal level from the top level.
• E.g. : Geography dimension

Unbalanced Hierarchy
• Hierarchy in dimensions having an unbalanced distribution of leaf nodes relative to the top level.
E.g : Organization chart

Ragged Hierarchy
• Some hierarchies are typically balanced but are missing a unique characteristic of some members in a level.
• Example: geography hierarchy that contains the levels Country, State, and City. (USA-Washington-Seattle, Greece-Athens)

Cubes
• The cube is a multi dimensional data structure which we can query for business information.
• Each block of the cube is called a cell and is uniquely identified by a member in each dimension.
• Cubes reduce the query response time for the information worker to extract knowledge from the data – contain precalculated summary data called aggregations
• That is, cubes not only store multi dimensional data from fact tables, but also aggregations of that data.
(summing of sales figures up from stores level, to district level, to regional level)

Overview of Data Warehousing

Monday, January 25, 2010

A data warehousing is a process to transform one data source to structure conductive to business analysis. Mathematical calculations are also performed on the newly created organized data to make usefulness for making business decisions. Finally, the data is available for user for analysis.
A well architecture data warehouse can return query results very quickly.
There are two types of Data Analysis.
1. OLAP
2. Data Mining
Data Warehouse is a collection of decision support technologies aimed at enabling the knowledge worker (executive, manager, and analyst) to make better and faster decisions.
OLTP Database
• An OLTP database is a transaction-based and normalized to reduce the amount of redundant data storage generated.
• Results in fast update.
• For speed of information retrieval, especially for the purpose of business analytics, an OLAP database is called for.

OLAP Database
• An OLAP database is highly de-normalized and therefore has rows of data that may be redundant.
•This makes for very fast query responses because relatively few joins are involved.
• Fast responses are what we want while doing business intelligence work.
• Data Marts – Mini data warehouses and quite often act as part of a larger warehouse.
Data Marts are subject-oriented data stores for cleaned data
•E.g. Sales data mart, an inventory data mart, or any subject rooted at the departmental level.
• A Data Warehouse functions at the enterprise level and typically handles data across the entire organization.

SSIS (SQL Server Integration Service) Control Flow Overview

Monday, December 14, 2009



Overview
The control flow in a SQL Server 2000 Integration Services (SSIS) package is constructed by using different types of control flow elements.
• Container
• Task
• Precedence constraints





Precedence Constraints

• Precedence constraints connect container and task in packages into an order control flow.

• You can control the sequence execution for tasks and containers, and specify conditions that determine whether tasks and containers run.

• Precedence constraint links two executables:

1 –The precedence executable and

2- The constrained executable.

• The precedence executable runs before the constrained executable and the execution result of the precedence executable may determine whether the constrained executable runs.

Execute SQL Tasks

The Execute SQL task runs SQL statements or stored procedures form a package. Execute SQL task can be used for the following purpose.

• Truncate a table or view in preparations for inserting data.

• Create, alter and drop database objects such as tables and views.

• Re-create fact and dimension tale before loading them.

• Run stored procedures.

Bulk Insert Task

The Bulk Insert task provides the quickest way to copy large amounts of data into a SQL server table or view.You can configure the Bulk Insert task in the following ways.

• Specify the OLE DB connection manager to connect to the destination SQL server database and table or view into which data is inserted.

• Specify the File or Flat File connection manager to access the source file.

• Define the format used by the Bulk Insert task

File System Task

The File System task performs operations on files and directories in the file system.

• All File System task operations use a source, which can be a file or a directory.

• The operations that copy and move file and directories and rename file use destination and a source.

SSIS Tools and Utilities

SSIS provides the following tools and utilities:
• Integration Services and Studio Environments= Describe how to use Business intelligence development studio and SQL server management studio with integration services.
•SSIS Designer = Graphical tool for building complex packages that include control flows, data flows, and event-driven logic.
•Integration Services Wizards= These wizards are for importing and exporting data, installing packages, migrating packages and creating package configurations.
•Command prompt utilities (SSIS) = For managing and running packages.
• Query Builder (SSIS)= Graphical tool for building queries.
• Expression Builder (SSIS) = Graphical tool for building expressions.

Overview of SOAP

• SOAP (Simple Object Access Protocol) is communication protocol which communicates between applications using XML format over internet.
• SOAP is platform and language independent. It is based on XML.
• SOAP is simple and extensible and it allows passing around firewalls.
• A SOAP message is an ordinary XML document containing the following elements.
1. Envelope (Required) = this element identifies XML document as a SOAP message.
2. Header (Optional) = It contains header information.
3. Body (Required) = It contains call and response information.
4. Fault (Optional) = It provides information about errors that occurred while processing the message.

Overview of SSIS Package

  • The SSIS package is the unit of work that is retrieved, executed and saved.
  • Package is an organized collection of connections, control flow elements, data flow elements, event handlers, variables and configurations.
  • SSIS includes SSIS Designer and SQL Server Import and Export Wizard, in addition to the SSIS object model for creating packages.
  • A package typically includes at least one connection manager.

Overview of SQL Server Integration Service

Monday, December 7, 2009

What is SSIS
- SSIS is a platform for building high performance data integration and workflow solutions, including the extraction, transformation and load (ETL) operations of data warehousing.
Uses of SSIS
- Populating Data Warehouses and Data Marts
- Merging Data from Heterogeneous Data Stores
- Cleaning and Standardizing Data
- Building Business Intelligence into a Data Transformation Process
- Automating Administrative Functions and Data Loading
SSIS Architecture
- The new integration services architecture is divided into two main sections.



1- Data Transformation pipeline (DTP). - Take the place of the old DTS data pump that was used in SQL server 7 and 200. - Primary function is handle the data flow between the source and target destinations
2 – Data Transformation Runtime (DTR) - A job execution environment that controls the control flow that is used in an SSIS package.
Data Transformation Pipeline (DTP): Overview The DTP takes care of the data flow and transformations that take place as rows are moved between the data source and data target - Each of these components exposes its own distinct object model

DTP uses data adapters to connect to the source and destination data sources.
The DTP engine is accessed using the DTP Pipeline object model.
Data Transformation Runtime (DTR): Overview
The DTR is essentially a job execution environment that controls the control flow that’s used in an SSIS package.


The DTR is accessed using the DTR object framework.
The DTR run-time object framework is the API that supports the integration service import/export wizard and the integration service designer in addition to the command-line dtexe tool.

How to know LOC (Line of Code) for all the files in the project?

Wednesday, November 11, 2009

If you want to know the KLOC for every files in your project then Microsoft line of code counter tool is very useful in getting KLOC. This tool is freeware and it’s very useful in knowing the exact line number of code in particular files. You can use Team Foundation Server, Visual Source Safe or File System for input of the file. This tool also gives the report and that can be export to excel also!! You can download from below given link. http://technet.microsoft.com/en-us/library/bb897860.aspx Software Name - Microsoft Line of Code (LOC) Counter Type – Freeware Size- 10.7 MB
KLOC, Line of Code, LOC

About Click Once Deployment Architecture

Friday, November 6, 2009

• The first step in ClickOnce deployment is publishing the application.
• Publishing an application generates two manifest files associated with a ClickOnce publication: the deployment manifest and the application manifest.
• The deployment manifest contains information about the publication, including the deployment provider URL, which indicates where the application will be launched from, the update policy, and a reference to the application manifest.
• The application manifest contains the list of files that the application is composed of.
• There are four stages of the clickonce deployment.
1- Before Deployment

2- After Intial Deployment

3- Update Published

4- Update Applied

Smart Client Software Factory – Default Project Creation

Tuesday, November 3, 2009

Default Projects created by SCSF
When we create the new Smart Client Application, The SCSF creates the following projects by default. SCSC creates one Shell Projects and four Infrastructure projects.
Projects
1. Shell = This Shell project is the main starting point of the any windows based smart client application. It contains the configuration and the actual main form that hosts the shell layout.
2. Infrastructure.Interface = Interface project defines interfaces for basic services created by SCSF.
3.Infrastructure.Library = Library project contains the implementations of the additional services generated by SCSF.
4.Infrastructure.Module = Module is an empty module where you can add your own infrastructure services. By default, this module consists of an empty module controller (which is a default root WorkItem for a module). In this module, you should only add services used by the whole smart client application that is typically always loaded with your application independently of other modules.
5.Infrastructure.Layout = A Layout project SCSF creates if you selected that you want to put the shell’s layout into a separate module. It contains a SmartPart and a presenter for this SmartPart for the shell layout.

Brief details about Smart Client EndPoint Catalog Application Block

About Smart Client EndPoint Catalog Application Block
• It provides features to expose the physical addresses and other details of remote services used by smart client applications.

• Uses a dictionary to store individual endpoints and the associated credential for each endpoint.
• An endpoint consists of a name, a default URL (address), credential, and a series of child network items.
Following is the sample of EndPoint Configuration.
<configuration>
<Endpoints>
<EndpointItems>
<add Name="ProjectPlanning" Address="http://localhost/ProjectDetails/ProjectPlanningService.svc" /> </EndpointItems>
</Endpoints>
</configuration>

About Smart Client Disconnected Service Agent Application Block

• It provides management features for running Web services from occasionally connected smart client applications.
• With a disconnected service agent, the application can maintain a queue of Web service requests when offline (disconnected) and then replay them when a connection to the server application becomes available.
• Provides following features :
– Queues web service requests and keeps track of the asynchronous callbacks that the application expects to receive when the Web service responds.
– Includes a dispatcher that determines exactly when to dispatch each call to a Web service, depending on connectivity conditions.
– When the correct conditions occur, the dispatcher makes the request, using the appropriate credentials and addresses for the endpoint.

Smart Client Connection Monitor Block

Monday, November 2, 2009

About Smart Client Connection Monitor Block
• Connection Monitor Block monitors the available connections and networks through which the smart client application can connect to services.
• A logical network is defined by the developer and represents a set of remote resources (for example, Web services). The following are examples of logical networks:
- The Internet
- A corporate or home network
- A set of public Web services
• You define a name for each logical network along with a representative address.
• The name is used to identify the network, while the representative address is used to determine the connectivity status for all network addresses in the collection.
• You can also use the application block to monitor connectivity changes for physical network adapters.
• The key component of the Connection Monitor Application Block is a class named ConnectionMonitor, that exposes keyed collections of Connection and Network instances.

Following is the configuration settings required to configure Connection Monitor Block.

<ConnectionMonitor>
<Networks>
<add Name="Intranet" Address="http://companynet"/>
<add Name="Internet" Address="http://www.microsoft.com"/>
</Networks>
<Connections>
<add Type="WiredConnection" Price="1"/>
</Connections>
</ConnectionMonitor >

Microsoft Visual studio 2010 and .NET 4.0 CTP features

Tuesday, October 27, 2009

The latest release of microsoft visual studio 2010 and .NET 4.0 CTP has many enhanced features ,which include
1) Enhanced User experience providing better support for floating documents and windows.Enhanced document targeting and improved animation support
2) Parallel Programming providing IDE support and native C++ libraries that use lambda functions.Resource management of hardware and parallel debugging views as well as windows are provided.
3) Better Application Lifecycle management which helps you to design and share multiple digram types of usecase and sequence diagrams.It also provides better tooling for documentation of test scenarios and includes a new Test Impact View.
4) It provides C++ development experience which helps developers navigate and understand complex C++ source code.
5) For web development it has enriched Javascript Intellisense,one click deployment and full support for silverlight
6) Windows Azure tools for visual studio provides C# and VB templates for building cloud services,tools to change service role configuration and building packages of cloud services
7)Multiple database support which helps Developers to work with IBM DB2 and Oracle in adition to SQL Server.

Anonymous Types

Friday, October 23, 2009

Anonymous Types (C# Programming Guide)
Anonymous types provide a convenient way to encapsulate a set of read-only properties into a single object without having to first explicitly define a type. The type name is generated by the compiler and is not available at the source code level. The type of the properties is inferred by the compiler. The following example shows an anonymous type being initialized with two properties called Amount and Message.
var v = new { Amount = 108, Message = "Hello" };
Anonymous types are typically used in the select clause of a query expression to return a subset of the properties from each object in the source sequenceAnonymous types are created by using the new operator with an object initializer.
Anonymous types are class types that consist of one or more public read-only properties. No other kinds of class members such as methods or events are allowed. An anonymous type cannot be cast to any interface or type except for object.

LinQ Queries

LINQ Queries
A query is an expression that retrieves data from a data source.

Queries are usually expressed in a specialized query language. Different languages have been developed over time for the various types of data sources, for example SQL for relational databases and XQuery for XML. Therefore, developers have had to learn a new query language for each type of data source or data format that they must support.

LINQ simplifies this situation by offering a consistent model for working with data across various kinds of data sources and formats. In a LINQ query, you are always working with objects. You use the same basic coding patterns to query and transform data in XML documents, SQL databases, ADO.NET Datasets, .NET collections, and any other format for which a LINQ provider is available.
Three Parts of a Query Operation

All LINQ query operations consist of three distinct actions:
1. Obtain the data source.
2. Create the query.
3. Execute the query.


The following example shows how the three parts of a query operation are expressed in source code. The example uses an integer array as a data source for convenience; however, the same concepts apply to other data sources also.

class IntroToLINQ
{
static void Main()
{
// The Three Parts of a LINQ Query:
// 1. Data source.
int[] numbers = new int[7] { 0, 1, 2, 3, 4, 5, 6 };
// 2. Query creation. // numQuery is an IEnumerable
var numQuery = from num in numbers
where (num % 2) == 0
select num;
// 3. Query execution.
foreach (int num in numQuery)
{ Console.Write("{0,1} ", num);
}
}
}

About WSE

Monday, October 12, 2009


Introduction
Web Services Enhancements for Microsoft .NET (WSE) is a .NET class library for building Web services using the latest Web services protocols, including WS-Security, WS-SecureConversation, WS-Trust, and WS-Addressing. WSE allows adding these capabilities at design time using code or at deployment time using a policy file.

WSE Architecture
At its heart, WSE is an engine for applying advanced Web service protocols to SOAP messages. This entails writing headers to outbound SOAP messages and reading headers from inbound SOAP messages. It may also require transforming the SOAP message body — for instance, encrypting an outbound message's body and decrypting an inbound message's body, as defined by the WS-Security specification. This functionality is encapsulated by two sets of filters, one for outbound messages and one for inbound messages. All messages leaving a process — request messages from a client or response messages from server — are processed using the outbound message filters. All messages arriving in a process — request messages to a server or response messages to a client — are processed using the inbound message filters. The following diagram shows this simple architecture.

WSE filter chains are integrated with the SOAP Messaging built-into WSE, as well as the ASP.NET Web services infrastructure.

 
 
 
Your Ad Here