Profiling (Identifying data quality issues).
Generalized Cleansing (tests to meet business rules).
Parsing and standardization (restructing data into a common format).
Matching (finding unique identifiers and performint de-duplication).
Enrichment (phone and email validation)
Monitoring (checking conformance to data quality requirements).
----------------------------------------------------------------------------------------
The construction of data warehouses involves data cleaning, data integration, and data transformation.
OLAP operations such as roll-up, drill-down, slicing, and dicing.
A data warehouse is a subject-oriented, integrated, time-variant, and nonvolatile collection of data in support of management’s decision making process
-----------------------------------------------------------------------------------------
Sunday, June 14, 2009
Thursday, March 12, 2009
SSRS 2008 Tablix -- Repeating Column Header for Tablix (Table)
Go to the Tablix advanced mode, select the static member from the Row Groups and set the properties as shown in the figure
Thursday, January 8, 2009
Relational VS MultiDimensional (OLAP) Databases
The key difference between OLAP dimensions and simple relational dimensions is the central role played by hierarchies in OLAP implementations.An OLAP dimension is strongly structured around its hierarchies, and the metadata of a cube definition includes the hierarchical levels.
This is one of the great strengths of an OLAP implementation.
OLAP is a sibling of dimensional models in the relational database, with intelligence about relationships and calculations defined on the server, that enable faster query performance and more interesting analytics from a broad range of query tools.
The recommended architecture for most purposes feeds the OLAP server from a dimensional data warehouse in the relational DBMS.
Overall the following points are important in OLAP.
1. Meta Data (like semi additive and non additive info),
2. Calculations Defined (Pre-aggregated data) and
3. Analaytical Functions defined on the Server
Example explaining the advantage of Hierarichies in OLAP
A query such as total sales for Q1 2002 is simple to formulate and should return from an OLAP server nearly instantaneously. But the user who wants total sales for an arbitrary period such as January 3 through March 12, 2002, for which no predefined hierarchy exists.
Advantages are as follows
1. It provides an intuitive user interface for browsing data.
2. It gives you spectacular query performance, primarily owing to the intelligent navigation of aggregates and partitions.
3. Parent-child dimension structures are easy and intuitive to implement.
3. It gives you server-defined rules for handling semiadditive and nonadditive measures.
The above explanation holds good for SSAS vs Sql Server Relational, Sql Server Relational vs Microstrategy,
This is one of the great strengths of an OLAP implementation.
OLAP is a sibling of dimensional models in the relational database, with intelligence about relationships and calculations defined on the server, that enable faster query performance and more interesting analytics from a broad range of query tools.
The recommended architecture for most purposes feeds the OLAP server from a dimensional data warehouse in the relational DBMS.
Overall the following points are important in OLAP.
1. Meta Data (like semi additive and non additive info),
2. Calculations Defined (Pre-aggregated data) and
3. Analaytical Functions defined on the Server
Example explaining the advantage of Hierarichies in OLAP
A query such as total sales for Q1 2002 is simple to formulate and should return from an OLAP server nearly instantaneously. But the user who wants total sales for an arbitrary period such as January 3 through March 12, 2002, for which no predefined hierarchy exists.
Advantages are as follows
1. It provides an intuitive user interface for browsing data.
2. It gives you spectacular query performance, primarily owing to the intelligent navigation of aggregates and partitions.
3. Parent-child dimension structures are easy and intuitive to implement.
3. It gives you server-defined rules for handling semiadditive and nonadditive measures.
The above explanation holds good for SSAS vs Sql Server Relational, Sql Server Relational vs Microstrategy,
Fact and Dimenisional Tables Relationships
Star schema, which maintains one-to-many relationships between dimensions and a fact table is widely accepted as the most viable data representation for dimensional analysis.
Realworld DW schema, however, frequently includes many-to-many relationships between a dimensionand a fact table.
Issues of many-to-many relationships.
1. losing the simplicity of the star schema structure
2. increasing complexity in forming queries, and
3. degrading query performance by adding more joins.
Two ad-hoc methods to resolve
1. Denormalizing the dimension tables.
2. Building the Bridge Table.
To find more information : go to the white paper An Analysis of Many-to-Many Relationships Between Fact andDimension Tables in Dimensional Modeling
Realworld DW schema, however, frequently includes many-to-many relationships between a dimensionand a fact table.
Issues of many-to-many relationships.
1. losing the simplicity of the star schema structure
2. increasing complexity in forming queries, and
3. degrading query performance by adding more joins.
Two ad-hoc methods to resolve
1. Denormalizing the dimension tables.
2. Building the Bridge Table.
To find more information : go to the white paper An Analysis of Many-to-Many Relationships Between Fact andDimension Tables in Dimensional Modeling
Dimensional Modeling - Design Changes
The following changes are anticipated to the design after the data warehouse is up and running
1. Adding new unanticipated facts (that is, new additive numeric fields in the fact table), as long as they are consistent with the fundamental grain of the existing fact table.
2. Adding completely new dimensions, as long as there is a single value of that dimension defined for each existing fact record.
3. Adding new, unanticipated dimensional attributes.
4. Breaking existing dimension records down to a lower level of granularity from a certain point in time forward.
--------------------------------------------------------------
1. Adding new unanticipated facts (that is, new additive numeric fields in the fact table), as long as they are consistent with the fundamental grain of the existing fact table.
2. Adding completely new dimensions, as long as there is a single value of that dimension defined for each existing fact record.
3. Adding new, unanticipated dimensional attributes.
4. Breaking existing dimension records down to a lower level of granularity from a certain point in time forward.
--------------------------------------------------------------
Sunday, December 21, 2008
Monday, November 24, 2008
OLAP Versus Data Mining
OLAP is a data summarization/aggregation tool that helps simplify data analysis, while data
mining allows the automated discovery of implicit patterns and interesting knowledge hidden in large amounts of data.
Key is OLAP
OLAP tools are targeted toward simplifying and supporting interactive data analysis,
whereas the goal of data mining tools is to automate as much of the process as possible,
while still allowing users to guide the process. In this sense, data mining goes one step beyond traditional on-line analytical processing.
Key is OLAP tools
OLAP functions are essentially for user-directed data summary and comparison
(by drilling, pivoting, slicing,dicing, and other operations). Data mining covers a much broader spectrum than simple OLAP operations because it performs not only data summary and comparison but also association, classification, prediction, clustering, time-series analysis, and other data analysis tasks.
Key is OLAP Operations
Data mining is not confined to the analysis of data stored in data warehouses. It may
analyze data existing at more detailed granularities than the summarized data provided
in a data warehouse. It may also analyze transactional, spatial, textual, and multimedia
data that are difficult to model with current multidimensional database technology. In
this context, data mining covers a broader spectrum than OLAP with respect to data
mining functionality and the complexity of the data handled.
Key is OLAP data analysis
Because data mining involves more automated and deeper analysis than OLAP,
data mining is expected to have broader applications. Data mining can help business
managers find and reach more suitable customers, as well as gain critical
business insights that may help drive market share and raise profits. In addition,
data mining can help managers understand customer group characteristics
and develop optimal pricing strategies accordingly, correct item bundling based
not on intuition but on actual item groups derived from customer purchase patterns,
reduce promotional spending, and at the same time increase the overall net
effectiveness of promotions.
mining allows the automated discovery of implicit patterns and interesting knowledge hidden in large amounts of data.
Key is OLAP
OLAP tools are targeted toward simplifying and supporting interactive data analysis,
whereas the goal of data mining tools is to automate as much of the process as possible,
while still allowing users to guide the process. In this sense, data mining goes one step beyond traditional on-line analytical processing.
Key is OLAP tools
OLAP functions are essentially for user-directed data summary and comparison
(by drilling, pivoting, slicing,dicing, and other operations). Data mining covers a much broader spectrum than simple OLAP operations because it performs not only data summary and comparison but also association, classification, prediction, clustering, time-series analysis, and other data analysis tasks.
Key is OLAP Operations
Data mining is not confined to the analysis of data stored in data warehouses. It may
analyze data existing at more detailed granularities than the summarized data provided
in a data warehouse. It may also analyze transactional, spatial, textual, and multimedia
data that are difficult to model with current multidimensional database technology. In
this context, data mining covers a broader spectrum than OLAP with respect to data
mining functionality and the complexity of the data handled.
Key is OLAP data analysis
Because data mining involves more automated and deeper analysis than OLAP,
data mining is expected to have broader applications. Data mining can help business
managers find and reach more suitable customers, as well as gain critical
business insights that may help drive market share and raise profits. In addition,
data mining can help managers understand customer group characteristics
and develop optimal pricing strategies accordingly, correct item bundling based
not on intuition but on actual item groups derived from customer purchase patterns,
reduce promotional spending, and at the same time increase the overall net
effectiveness of promotions.
Tuesday, November 11, 2008
Table Expressions
Four types of table expressions:
1. Derived tables
2. Common table expressions (CTEs),
3. Views
4. Inline table-valued functions (inline TVFs) also known as Parametrized views.
Beneifts.
Table expressions are virtual Tables.
The benefi ts of using table expressions uses a modular approach and not to performance.
Any Table Expression should meet the following three requirements.
1. Order is not guaranteed because the rows in a relational table are not ordered except with one restriction using Top Clause with Order By. Here the Order by is used for restricting the rows.
2. All columns must have names.
3. All column names must be unique.
To reuse the definitions , use views and inline TVFs.
Not to reuse the definitions, use dervied tables and CTEs.
APPLY operator
Multiple instances of the same CTE The APPLY operator operates on two input tables, the second of which may be a table expression; I’ll refer to them as the left and right tables. The right table is usually a derived table or an inline TVF.
If the right table expression returns an empty set, the CROSS APPLY operator does not returnthe corresponding left row. To return the empty set use the OUTER Apply.
--------------------------------------------------------------------------------
In its non-recursive form, a CTE is an alternative to derived tables, views, and inline user-defined functions (UDFs).
A derived table is a named table expression that exists for the duration of a query.
Views are also named table expressions that persist in the database until you explicitly drop them. Unlike with derived tables
1. a query can refer to a view name multiple times;
2. the view can't refer to variables.
Inline UDFs have the same characteristics as views except that they can refer to the function's input arguments.
CTEs have the best features of derived tables, views, and inline UDFs—they are named table expressions that exist only for the duration of the query, that an outer query can refer to multiple times, and that can refer to variables defined in the calling batch. . . .
1. Derived tables
2. Common table expressions (CTEs),
3. Views
4. Inline table-valued functions (inline TVFs) also known as Parametrized views.
Beneifts.
Table expressions are virtual Tables.
The benefi ts of using table expressions uses a modular approach and not to performance.
Any Table Expression should meet the following three requirements.
1. Order is not guaranteed because the rows in a relational table are not ordered except with one restriction using Top Clause with Order By. Here the Order by is used for restricting the rows.
2. All columns must have names.
3. All column names must be unique.
To reuse the definitions , use views and inline TVFs.
Not to reuse the definitions, use dervied tables and CTEs.
APPLY operator
Multiple instances of the same CTE The APPLY operator operates on two input tables, the second of which may be a table expression; I’ll refer to them as the left and right tables. The right table is usually a derived table or an inline TVF.
If the right table expression returns an empty set, the CROSS APPLY operator does not returnthe corresponding left row. To return the empty set use the OUTER Apply.
--------------------------------------------------------------------------------
In its non-recursive form, a CTE is an alternative to derived tables, views, and inline user-defined functions (UDFs).
A derived table is a named table expression that exists for the duration of a query.
Views are also named table expressions that persist in the database until you explicitly drop them. Unlike with derived tables
1. a query can refer to a view name multiple times;
2. the view can't refer to variables.
Inline UDFs have the same characteristics as views except that they can refer to the function's input arguments.
CTEs have the best features of derived tables, views, and inline UDFs—they are named table expressions that exist only for the duration of the query, that an outer query can refer to multiple times, and that can refer to variables defined in the calling batch. . . .
Friday, October 31, 2008
Friday, October 3, 2008
Data Mining Books
Data Mining Books to buy
1. maraaret h. dunham
2. Jaiwei hau
Introduction to Data Mining By Pang-Ning Tan, Michael Steinbach, Vipin Kumar
Data Mining Videos from Google Talk
http://www.youtube.com/watch?v=-tWS0tN8sW0 -- Day 13
http://www.youtube.com/watch?v=fmZYH3rmqDQ
http://www.youtube.com/watch?v=l4a3e__QzoY
http://www.youtube.com/watch?v=CzvgrcQhWGg
http://www.youtube.com/watch?v=xpuB9ydmBsM
http://www.youtube.com/watch?v=N5i85v0ckzY
http://www.youtube.com/watch?v=zRsMEl6PHhM
-----------------------------------------------------------
Course from stanford university
Sunday, September 21, 2008
Statistics topics needs to cover for Data Mining
Data Analytics Competence Spectrum
From Pricing, Risk, Cross-selling, Retention, Time-to-Market, Ad Efficiency and Campaign Management to Acquisition and Growth, and Web Analytics, our Data Analytics capabilities cover a number of possible analyses, using different programming tools and software such as SAS, SPSS, Excel, SQL, Matlab and Siebel. The following is a sample of the techniques used for various types of analysis.
Link Analysis
Multivariate, Linear, Ordinal and Multinomial Regression
Conjoint Analysis
Logistic Regression
Factor Analysis
Survival Analysis
Decision Trees
Clustering
Bayesian Analysis
Neural Networks
Kohonen Maps
Simulation Techniques
Discriminant Analysis
Time Series Analysis
Vendor Performance Management Tool
Sales and Retail Dashboards
Contact Search Tool
Launch Performance Tracking Tool
Market Basket Analysis
Econometric Forecasting
Cycle Research for Technical Analysis
Reporting and Analysis
Optimising Cash and Accounts Payable Management
From Pricing, Risk, Cross-selling, Retention, Time-to-Market, Ad Efficiency and Campaign Management to Acquisition and Growth, and Web Analytics, our Data Analytics capabilities cover a number of possible analyses, using different programming tools and software such as SAS, SPSS, Excel, SQL, Matlab and Siebel. The following is a sample of the techniques used for various types of analysis.
Link Analysis
Multivariate, Linear, Ordinal and Multinomial Regression
Conjoint Analysis
Logistic Regression
Factor Analysis
Survival Analysis
Decision Trees
Clustering
Bayesian Analysis
Neural Networks
Kohonen Maps
Simulation Techniques
Discriminant Analysis
Time Series Analysis
Vendor Performance Management Tool
Sales and Retail Dashboards
Contact Search Tool
Launch Performance Tracking Tool
Market Basket Analysis
Econometric Forecasting
Cycle Research for Technical Analysis
Reporting and Analysis
Optimising Cash and Accounts Payable Management
Thursday, August 28, 2008
Wednesday, August 20, 2008
Thursday, August 7, 2008
Sql Server 2005 DMV's
Some interesting DMV's in SQL SERVER 2005
sys.dm_tran_locks
sys.dm_exec_cached_plans
sys.dm_exec_query_plan
sys.dm_exec_sessions
sys.dm_tran_session_transactions
sys.dm_exec_requests
sys.dm_tran_locks
sys.dm_exec_cached_plans
sys.dm_exec_query_plan
sys.dm_exec_sessions
sys.dm_tran_session_transactions
sys.dm_exec_requests
Sunday, July 27, 2008
SSRS - Improvoing Performance
1. Avoid long running reports, by creating a report snapshot to run nightly or during low activity on your system.
2. Define pagination to hide overhead. for example, if a user requests a report that returns 1000 rows of data, you can use page breaking to render the initial page or results while the report server renders the rest of the pages in the background. without pagination, the user would have to wait until it rendered all 1000 rows.
3. Implement filters for performance.
If you use report-level filtering on data regions and data grouping rather than using query parameters as filters.
4. Enable drill down for detail.
Instead of giving users a report that shows all the data at once, design a summary report that
presents a subset of data and let usrs drill down to get more detail. using master detail
reports that take advantage of SSRS's navigation capabilities. Many parts of the report
controls have navigation property, which we use to create a hyperlink on the control for
passing the specific values.
2. Define pagination to hide overhead. for example, if a user requests a report that returns 1000 rows of data, you can use page breaking to render the initial page or results while the report server renders the rest of the pages in the background. without pagination, the user would have to wait until it rendered all 1000 rows.
3. Implement filters for performance.
If you use report-level filtering on data regions and data grouping rather than using query parameters as filters.
4. Enable drill down for detail.
Instead of giving users a report that shows all the data at once, design a summary report that
presents a subset of data and let usrs drill down to get more detail. using master detail
reports that take advantage of SSRS's navigation capabilities. Many parts of the report
controls have navigation property, which we use to create a hyperlink on the control for
passing the specific values.
Saturday, July 26, 2008
Dimensional Vs ER Modeling
1. The relationship in a Dimensional model don't represent business rules instead they are navigational paths used to help write reports or create graphs. But whereas relationship in ER modeling represents business rules.
2. The primary goal of the ER modeling is to remove all non key data redundancy.
But Dimensional modeling controls data redundancy by confirming dimension and fact tables.
The table that has been confirmed can be used in more than one dimensional data model.
How to Create a Dimensional Model
1. Identity business process by business process and Each business process can be expressed
as a data mart
---- a modular , highly focused, richly detailed, incrementally designed componenet
of the datawarehouse.
Initially try to focus on the Single-Source data mart not on multiple source datamat.
example of single data marts are retail sales, purchase orders, shipments and payments.
example of multiple data mart is Customer profitability which combines revenue and costs that often come from sales and inventory databases.
2. Grain of the Fact table which is the level of detail that the table captures.
2. The primary goal of the ER modeling is to remove all non key data redundancy.
But Dimensional modeling controls data redundancy by confirming dimension and fact tables.
The table that has been confirmed can be used in more than one dimensional data model.
How to Create a Dimensional Model
1. Identity business process by business process and Each business process can be expressed
as a data mart
---- a modular , highly focused, richly detailed, incrementally designed componenet
of the datawarehouse.
Initially try to focus on the Single-Source data mart not on multiple source datamat.
example of single data marts are retail sales, purchase orders, shipments and payments.
example of multiple data mart is Customer profitability which combines revenue and costs that often come from sales and inventory databases.
2. Grain of the Fact table which is the level of detail that the table captures.
Saturday, June 28, 2008
SSRS -- Reporting
Report Structure Types
Structured Reports -- Relational
Structured Grouping -- OLAP
Structured Attributes -- Data mining
Structured Reports -- Relational
a. Pre-Define Report structure(i.e., Report Structure is Static).
b. Allow Interaction (drill down,slicing) but notdynamic structing.
Structured Grouping -- OLAP
a. Pre-define grouping buckets --
b. Report Structure is Dynamic (Slice & Dice, Dynamic Pivot, Sort and filter)
c. Pre-Summarize data for speed.
Structured Attributes -- Data Mining
a. Pre-define attributes (and model)
b. Mining model calculates grouping and structure.
c. Use for Prediction , exploration and analysis.
Reporting Approaches
1. OLTP Data ODS -- Report
2. Star Schema ODS -- Staging Table -- Start Schema -- Reeport
3. OLAP Data ODS -- Staging Table -- Star Schema -- cube -- Report
Here we use Extract, Transform and Load.
4. UDM
Structured Reports -- Relational
Structured Grouping -- OLAP
Structured Attributes -- Data mining
Structured Reports -- Relational
a. Pre-Define Report structure(i.e., Report Structure is Static).
b. Allow Interaction (drill down,slicing) but notdynamic structing.
Structured Grouping -- OLAP
a. Pre-define grouping buckets --
b. Report Structure is Dynamic (Slice & Dice, Dynamic Pivot, Sort and filter)
c. Pre-Summarize data for speed.
Structured Attributes -- Data Mining
a. Pre-define attributes (and model)
b. Mining model calculates grouping and structure.
c. Use for Prediction , exploration and analysis.
Reporting Approaches
1. OLTP Data ODS -- Report
2. Star Schema ODS -- Staging Table -- Start Schema -- Reeport
3. OLAP Data ODS -- Staging Table -- Star Schema -- cube -- Report
Here we use Extract, Transform and Load.
4. UDM
Friday, May 30, 2008
Junk dimensions
OLTP tables that are full of flag fields and yes/no attributes, many of which are used for operational support and have no documentation except for the column names.
There are three conventional ways to deal with these attributes: discard all of the miscellaneous attributes, eliminating them from the dimensional design; incorporate the miscellaneous attributes into the fact table; or make each miscellaneous attribute a separate dimension. However, all of these options are less than ideal.
Discarding the data can be dangerous because the miscellaneous values, flags, and yes/no fields might contain valuable business data.
Including the miscellaneous attributes in the fact table could cause the fact table to swell to alarming proportions, especially if you have more than just a few miscellaneous attributes. The increased size of the fact table could cause serious performance problems because of the reduced number of records per physical I/O. Even if you tried to index these fields to minimize the performance problems, you still wouldn’t gain anything because so many of the miscellaneous fields contain flag values such as 0 and 1; Y and N; or open, pending, and closed.
And if you make each miscellaneous attribute a separate dimension, it will most likely result in a complicated dimensional designyou’re looking at a much more complicated star schema and associated cube.
Column names such as Completed, Packed, Shipped, Received, Delivered, and Returned (each with yes/no data values) are very common, and they do have business value. Instead of discarding flag fields and yes/no attributes, I suggest placing them all into a junk dimension that’s organized
The junk dimension shown in Figure 1 represents an order-fulfillment system; the column headers show some of the possible statuses an item that has been ordered can have. Row 1 indicates that the item ordered has been picked out of the warehouse, packed for shipment, shipped, delivered, received, returned for a refund, and restocked in the warehouse. Row 9 shows an item on order that’s waiting to begin the order-fulfillment process. The rows in between indicate items that are in various stages of the orderfulfillment process. This example is very simple because the process is so linear and sequential,
There are three conventional ways to deal with these attributes: discard all of the miscellaneous attributes, eliminating them from the dimensional design; incorporate the miscellaneous attributes into the fact table; or make each miscellaneous attribute a separate dimension. However, all of these options are less than ideal.
Discarding the data can be dangerous because the miscellaneous values, flags, and yes/no fields might contain valuable business data.
Including the miscellaneous attributes in the fact table could cause the fact table to swell to alarming proportions, especially if you have more than just a few miscellaneous attributes. The increased size of the fact table could cause serious performance problems because of the reduced number of records per physical I/O. Even if you tried to index these fields to minimize the performance problems, you still wouldn’t gain anything because so many of the miscellaneous fields contain flag values such as 0 and 1; Y and N; or open, pending, and closed.
And if you make each miscellaneous attribute a separate dimension, it will most likely result in a complicated dimensional designyou’re looking at a much more complicated star schema and associated cube.
Column names such as Completed, Packed, Shipped, Received, Delivered, and Returned (each with yes/no data values) are very common, and they do have business value. Instead of discarding flag fields and yes/no attributes, I suggest placing them all into a junk dimension that’s organized
The junk dimension shown in Figure 1 represents an order-fulfillment system; the column headers show some of the possible statuses an item that has been ordered can have. Row 1 indicates that the item ordered has been picked out of the warehouse, packed for shipment, shipped, delivered, received, returned for a refund, and restocked in the warehouse. Row 9 shows an item on order that’s waiting to begin the order-fulfillment process. The rows in between indicate items that are in various stages of the orderfulfillment process. This example is very simple because the process is so linear and sequential,
Thursday, May 29, 2008
Degenerate dimension
Degenerate dimension is not one that lacks moral structure or integrity. Instead, a degenerate dimension is a dimension that doesn’t exist as a table but is represented in the data warehouse.
Data warehouse dimensional design requires you to include control documents such as invoices, orders, and warranties. Each of these control documents has a control number such as the invoice number, the order number, or the serial number of the item under warranty.
Degenerate dimensions are simply control numbers that are stored in the fact table of a data warehouse. These control numbers look like keys, but they don’t act like keys; they have no associated dimension to join with. Control numbers provide a way to identify which line items in the fact table were generated as a part of the same order or invoice. Let’s take a look at how to map control numbers from the OLTP database to the fact table in the data warehouse and associate them with each line item
Data warehouse dimensional design requires you to include control documents such as invoices, orders, and warranties. Each of these control documents has a control number such as the invoice number, the order number, or the serial number of the item under warranty.
Degenerate dimensions are simply control numbers that are stored in the fact table of a data warehouse. These control numbers look like keys, but they don’t act like keys; they have no associated dimension to join with. Control numbers provide a way to identify which line items in the fact table were generated as a part of the same order or invoice. Let’s take a look at how to map control numbers from the OLTP database to the fact table in the data warehouse and associate them with each line item
Monday, May 5, 2008
Subscribe to:
Posts (Atom)