Free January Training Webinar Series

Pragmatic Works, SQL Server Central and Microsoft would like to invite you to a another month of Free SQL Server Training delivered by the people that write the books! January Speakers and Topics Register Here!

Speaker: Brian Knight Topic: Introduction to PowerPivot Date: January 3, 2012, 11:00AM EDT In this session, you’ll learn how to use PowerPivot to create self-service analytics in your organization. You’ll see how Office 2010 and SQL Server 2008 R2 will help your users answer their own questions and bring together datasets that may be heterogeneous like a SQL Server table and data feed from a website. Then, you’ll see how to take that data and create a striking report for your end users and collaborate with the data in SharePoint.

Speaker: Mark Stacey Topic: Analysis Services for the DBA Date: January 5, 2012, 11:00AM EDT Many DBAs have heard the term Analysis Services, but don't really know what it's for or who would use it. In this session, Mark will explain the use cases for Analysis Services, illustrate how it improves performance for large data sets and eases report writing, and will go through a practical example of building a first Analysis Services solution, and managing Analysis Services in a production environment.

Speaker: Arie Jones Topic: Master Data Services in SQL Server 2012 Date: January 10, 2012, 11:00AM EDT Most DBAs at one time or another have been taught the importance of master data management(MDM), which is comprised of a set of processes and tools in order to keep your non-transactional data in a consistent state. However, with today’s fast paced environment and tightening budgets most DBAs lack the resources to properly implement it. With the upcoming release of SQL Server 2012, Microsoft has taken their second swipe at providing a tool known as Master Data Services(MDS) to aid the DBA in this endeavor. In this session, you will learn the how Master Data Services is implemented in SQL Server 2012, setting up models, working through the Excel plug-in, and utilizing DQS and business rules to help with that dirty data. This session will definitely be an eye opener for some that were on the fence with the first release and those that are coming into the platform with a fresh set of eyes!

Speaker: K. Brian Kelley Topic: Using Powershell to Check Your SQL Server Security Date: January 12, 2012, 11:00AM EDT Monitoring the security on your SQL Servers can be a tedious chore. Tune in to learn how to use Powershell to help automate this work. We'll look at how to use scripts to capture basic details, to do comparisons from scan to scan, as well as how to automate remediation of issues discovered.

Speaker: Hope Foley Topic: Super-size Your SSIS Breakfast Sandwich: Performance Tuning 2012 SSIS Data Loads Date: January 17, 2012, 11:00AM EDT Importing data is as common to SQL Server as bacon is to a savory breakfast sandwich. As most of us love to eat those breakfast sandwiches, SQL Server Integration Services (SSIS) loves to eat data in an Import/Export kind of way. Although a green chain of boxes in SSIS can be the ruling “Happy Dance” of any DBA or SQL Developer, we must all still consider the performance of those tasks. In this session, we will go through performance tuning in SSIS 2012 after a successful SSIS package has been developed. When we are finished with this session, not only will you still have the mystic of green colors of successful execution of your SSIS import and export tasks, but the turbo boosters will be turned on as well.

Speaker: Mark Brown Topic: Beginning Development for Business – Part 4: Running in the Halls Date: January 24, 2012, 11:00AM EDT In this session we look closer at what a databases are and what roles they play in business. We will talk about database design and how it can differ depending on its primary use. We will look at how to create a database by creating the database for our application. We will look at the tools used to create and manage databases. We will go over multiple methods of using the database from within an application.

Speaker: Eric Hanson Topic: Performance Tuning with Column Store Indexes Date: January 26, 2012, 11:00AM EDT SQL Server columnstore indexes and the vector-based batch mode of query execution are new in the SQL Server 2012 release, and can speed up data warehouse queries by 100X or more. In their sweet spot, columnstores give jaw-dropping performance, but there are limitations in the initial release where performance may not improve as much as the technology allows, unless you tune your application. This talk presents how to work around the main limitations in the columnstore feature to get consistently great performance. We’ll address how to compensate for query performance limitations for outer joins and union operations, data type restrictions, and the fact that columnstore indexes make the data read-only (except for the ability to switch in partitions). We’ll also discuss how to capitalize on the new segment elimination feature that can skip million-row chunks of a table with a constant-time check.

Speaker: Mike Davis Topic: Creating KPI's, Actions, and Calculations in SSAS Cubes Date: January 31, 2012, 11:00AM EDT Mike demonstrates different methods for KPI's, actions, and common calculations. After this session you will have a good understanding of how to build all of these functions in your SSAS Cube. Mike will show dynamic KPIs from a table, opening a URL with an Action, and some common MDX calculations.

Register Here!

Download Whitepaper - Migrating DTS Packages to Integration Services

A SQL Server Technical Article by Brian Knight & Dustin Ryan

SQL Server Integration Services (SSIS) brings a revolutionary concept of enterprise-class ETL to the masses. The engine is robust enough to handle hundreds of millions of rows with ease, but is simple enough to let both developers and DBAs engineer an ETL process. Learn about the benefits of migrating your SQL Server 2000 Data Transformation Services (DTS) packages to Integration Services by using two proven methods. Download this whitepaper by Brian Knight and see how you can run and manage your current DTS packages inside of the SQL Server 2005 and 2008 management tools.

Click Here to download

DATA WAREHOUSE ARCHITECTURE

The data warehouse is normal designed as three tier architecture.

• The lower most tier is a relational database where the data from various servers are populated after several data cleansing, transformations which are done by the ETL tools. In the relational database the data is prepared in such a way that all the relevant information can be easily populated in the data warehouse. This lower tier database is also referred as a staging database.
• The middle tier is the multidimensional OLAP cube where the data is populated in the form of facts and measures from the relational database. The data from the lower layer to middle layer does not undergo much transformation. The data is only organized into formulated dimensions and measures.
• The top most tier is the front end for the analyst and the executives who would view the data from the cube. It contains tools which aid in analysis and data mining.

OLAP operations

ROLL UP
The roll up operation aggregates the data in an increasing order in the conceptual hierarchy from a lower level to higher level. In the example mentioned before the data is aggregated by week and then the data is aggregated according to its higher level in hierarchy month and so on.

DRILL DOWN
Drill down is the opposite of roll up. The data is aggregated in decreasing order in the conceptual hierarchy from a higher level to lower level. In the time dimension the data is grouped by ‘ week’ and then the next grouping is in the lower level ‘day’.

SLICE AND DICE
Slice operation is choosing one particular dimension in the n dimensional cube which results in a sub cube where as dice operation is done by choosing two or more dimensions in the cube to result in a sub cube.

PIVOT
This is also referred as rotate where the axis is rotated to provide a different presentation of the data in the n dimensional cube.

Download Whitepaper - Managing and Configuring SSIS

A SQL Server Technical Article by Brian Knight, Devin Knight, & Mike Davis

Administering SSIS can be a challenging task for a DBA/Developer who is not familiar with the SSIS environment and some of the new hurdles it can present. This whitepaper discusses how to configure packages so that you can have zero-touch deployments. We'll take a dive-deep look into a notification and logging framework that sends alerts if a problem occurs or if capacity is running out. Also, how to deploy and schedule SSIS packages will be covered.

Click Here to Download.

Working with SSIS Expressions Whitepaper by Brian Knight!

Whether you’ve been involved with SQL Server Integration Services (SSIS) for years or are just learning SSIS, you’ve probably found the learning curve of the SSIS expression language quite challenging. The expression language is a core component of SSIS that helps users make any component in an SSIS packages dynamic and also helps the data flow make workflow decisions. This whitepaper walks you through a crash course in the SSIS expression language across all components of SSIS. After you complete this whitepaper, you’ll know how to use expressions in the control flow, data flow and in connections to make your package change at runtime and maximize the full power of SSIS.

Download the Whitepaper Now!

What you'll learn: Learn the Language: Mathematical Expressions Functions, String Expression Functions, Date and Time Express Functions Task Expressions Precedence Contraint Expressions Connection Manager Expressions And much more! Download the Whitepaper Now!

What is Fact Table?

Fact Table
The centralized table in a star schema is called as FACT table. A fact table typically has two types of columns: those that contain facts and those that are foreign keys to dimension tables. The primary key of a fact table is usually a composite key that is made up of all of its foreign keys.


Measure Types

• Additive - Measures that can be added across all dimensions.
• Non Additive - Measures that cannot be added across all dimensions.
• Semi Additive - Measures that can be added across few dimensions and not with others.

A fact table might contain either detail level facts or facts that have been aggregated (fact tables that contain aggregated facts are often instead called summary tables).


In the real world, it is possible to have a fact table that contains no measures or facts. These tables are called as Factless Fact tables.


Steps in designing Fact Table

• Identify a business process for analysis.
• Identify measures or facts.
• Identify dimensions for facts.
• List the columns that describe each dimension.
• Determine the lowest level of summary in a fact table.

What is Dimensional Data Model?

Dimensional data model is most often used in data warehousing systems. This is different from the 3rd normal form, commonly used for transactional (OLTP) type systems. As you can imagine, the same data would then be stored differently in a dimensional model than in a 3rd normal form model.

To understand dimensional data modeling, let's define some of the terms commonly used in this type of modelling:

Dimension: A category of information. For example, the time dimension.

Attribute: A unique level within a dimension. For example, Month is an attribute in the Time Dimension.

Hierarchy: The specification of levels that represents relationship between different attributes within a dimension. For example, one possible hierarchy in the Time dimension is Year → Quarter → Month → Day.

Fact Table: A fact table is a table that contains the measures of interest. For example, sales amount would be such a measure. This measure is stored in the fact table with the appropriate granularity. For example, it can be sales amount by store by day. In this case, the fact table would contain three columns: A date column, a store column, and a sales amount column.

Lookup Table: The lookup table provides the detailed information about the attributes. For example, the lookup table for the Quarter attribute would include a list of all of the quarters available in the data warehouse. Each row (each quarter) may have several fields, one for the unique ID that identifies the quarter, and one or more additional fields that specifies how that particular quarter is represented on a report (for example, first quarter of 2001 may be represented as "Q1 2001" or "2001 Q1").

A dimensional model includes fact tables and lookup tables. Fact tables connect to one or more lookup tables, but fact tables do not have direct relationships to one another. Dimensions and hierarchies are represented by lookup tables. Attributes are the non-key columns in the lookup tables.

In designing data models for data warehouses / data marts, the most commonly used schema types are Star Schema and Snowflake Schema.