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.
What are Fact and Dimension tables?
Posted by Chirag at Wednesday, January 27, 2010 0 comments
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.
Posted by Chirag at Wednesday, January 27, 2010 0 comments
Labels: Data Modelling . Roll up display . Drill down display .
Storage types in Data Warehousing
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.
Posted by Chirag at Tuesday, January 26, 2010 0 comments
Labels: HOLAP, Storage Types; MOLAP; ROLAP
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
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.
Posted by Chirag at Monday, January 25, 2010 0 comments
SSIS (SQL Server Integration Service) Control Flow Overview
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.
Posted by Chirag at Monday, December 14, 2009 0 comments
Labels: Execute SQL task; bulk insert task, File system task, SSIS;SSIS Control Flow
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.
Posted by Chirag at Monday, December 14, 2009 0 comments
Labels: SSIS Tools and Utilities .
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.
Posted by Chirag at Monday, December 14, 2009 0 comments
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.
Posted by Chirag at Monday, December 14, 2009 0 comments
Labels: SSIS Package . SSIS .
Overview of SQL Server Integration Service
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.
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 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?
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
Posted by Chirag at Wednesday, November 11, 2009 0 comments
Labels: KLOC, Line of Code, LOC
About Click Once Deployment Architecture
3- Update Published
Posted by Chirag at Friday, November 06, 2009 0 comments
Labels: click once deployment, ClickOnce, ClickOnce Deployment modes
Smart Client Software Factory – Default Project Creation
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.
Posted by Chirag at Tuesday, November 03, 2009 0 comments
Labels: Smart Client, Smart Client Component, Smart Client Projects; Shell
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>
Posted by Chirag at Tuesday, November 03, 2009 0 comments
Labels: Smart Client, Smart Client Component, Smart Client EndPoint Catalog Application Block;EndPoint Address
About Smart Client Disconnected Service Agent Application Block
Posted by Chirag at Tuesday, November 03, 2009 0 comments
Labels: Smart Client, Smart Client Disconnected Service Agent Application Block;, Smart Client Features
Smart Client Connection Monitor Block
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 >
Posted by Chirag at Monday, November 02, 2009 0 comments
Labels: Smart Client, Smart Client Component, Smart Client Connection Monitor Block; Intranet; Internet
Microsoft Visual studio 2010 and .NET 4.0 CTP features
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
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.
Posted by Chirag at Friday, October 23, 2009 0 comments
Labels: Anonymous Types, Reference guide c#
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);
}
}
}
Posted by Chirag at Friday, October 23, 2009 0 comments
Labels: LinQ; LinQ Queries; XQuery
About WSE
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.
Posted by Chirag at Monday, October 12, 2009 0 comments











