SQL Server Analysis Services (SSAS) Tutorial by MSSQLTips.com

Guys,

SQL Server Analysis Services (SSAS) is the technology from the Microsoft Business Intelligence stack, to develop Online Analytical Processing (OLAP) solutions. In other terms, we can use SSAS to create cubes using data from data marts / data warehouse for deeper and faster data analysis.

Cubes are multi-dimensional data sources which have dimensions and facts (also known as measures) as its basic constituents. From a relational perspective dimensions can be thought of as master tables and facts can be thought of as measureable details. These details are generally stored in a pre-aggregated proprietary format and users can analyze huge amounts of data and slice this data by dimensions very easily. Multi-dimensional expression (MDX) is the query language used to query a cube, similar to the way T-SQL is used to query a table in SQL Server.

Simple examples of dimensions can be product / geography / time / customer, and similar simple examples of facts can be orders / sales. A typical analysis could be to analyze sales in Asia-pacific geography during the past 5 years. Think of this data as a pivot table where geography is the column-axis and years is the row axis, and sales can be seen as the values. Geography can also have its own hierarchy like Country->City->State. Time can also have its own hierarchy like Year->Semester->Quarter. Sales could then be analyzed using any of these hierarchies for effective data analysis.

A typical higher level cube development process using SSAS involves the following steps:

1) Reading data from a dimensional model
2) Configuring a schema in BIDS (Business Intelligence Development Studio)
3) Creating dimensions, measures and cubes from this schema
4) Fine tuning the cube as per the requirements
5) Deploying the cube

This tutorial will step through a number of topics that we need to understand in order to successfully create a basic cube. Gigh level outline is as follows: 

• Design and develop a star-schema
• Create dimensions, hierarchies, and cubes
• Process and deploy a cube
• Develop calculated measures and named sets using MDX
• Browse the cube data using Excel as the client tool

In this tutorial, we will also try to develop an understanding of OLAP development from the eyes of an OLTP practitioner.

Click here to read tutorial.


Happy Learning

Sudhir 

SQL Server Integration Services Tutorial by MSSQLTIPS.com

Guys,

SQL Server Integration Services (SSIS) is a tool that we use to perform ETL operations; i.e. Extract, Transform and Load data. While ETL processing is common in data warehousing (DW) applications, SSIS can do much more than that e.g. when we create a Maintenance Plan using SQL Server Management Studio (SSMS) an SSIS package is created, when we export or import data, an SSIS package is created.

At a high level, SSIS provides the ability to:

• Retrieve data from just about any source
• Perform various transformations on the data; e.g. convert from one type to another, convert to uppercase or lowercase, perform calculations, etc.
• Load data into just about any source
• Define a workflow  

In this tutorial we will step through a number of topics that we need to understand in order to successfully build an SSIS package. Our high level outline is as follows:

• Creating SSIS packages with SQL Server Management Studio (SSMS)
• Business Intelligence Development Studio (BIDS)
• Creating a simple SSIS package in BIDS
• Deploying SSIS packages
• Executing SSIS packages

Click here to read tutorial.

 

Happy learning

 

Sudhir

SQL Server Reporting Services Tutorial by MSSQLTips.com

Guys,

SQL Server Reporting Services 2008 (SSRS) is a feature included in the SQL Server 2008 product. We use SSRS to design, develop, test, and deploy reports. SSRS leverages Business Intelligence Development Studio (BIDS) developer tool for all aspects of authoring and deploying reports.

In this tutorial we will step through a number of topics that we need to understand to successfully build a report. The tutorial will run through following topics:

• Reporting Services Components
• Install Reporting Services
• Business Intelligence Development Studio (BIDS)
• Install Sample Database
• Create a Simple Report with the Wizard
• Create a Simple Report with the Report Designer
• Deploy Reports
• Configure Report Manager Security 

Click here to read tutorial.

Happy Learning

Sudhir

SSWUG Free Expo Event: Understanding SQL Server Analysis Services

Guys,

SSWUG.ORG’s virtual expo will review various aspects of SQL Server Analysis Services (SSAS), which enables the server to be used for analytical processing and data mining. Through in-depth sessions with four of the leading experts in the information technology (IT) and business intelligence (BI) fields, we will see many demonstrations and examples on designing, creating and managing data from multiple sources. By the end of our event, we should have the tools and understanding needed to bring added functionality, automation and insight into your data. 

Please register now to learn from four acclaimed SSAS experts for free on Friday, May 20! Click here to Register.

Friday, May 20, 2011
9 a.m. - 1 p.m. PDT

Sessions will cover the following topics:

 

• Common Mistakes with SSAS Cube Designs  Sudhir
• SSAS Partitioning and Aggregation Strategies 
• Properly Using the SSAS Query Cache 
• Overcoming SSAS Implementation Issues 
• Building a Scalable SSAS Solution 
• Best Practices for Performance and Tuning Techniques

 

Introduction to MDX video & slides by Business Intelligence architect and MDX expert Bill Pearson

Guys,

To explore the basic functions of MDX and view many practical examples on using the query language in SSWUG.ORG’s "Introduction to MDX" webcast series. In three, in-depth sessions, Business Intelligence architect and MDX expert Bill Pearson will focus on the basic components of MDX, as well as provide information on crafting simple MDX expressions and queries that generate result sets. By the end of this three-part series, we should have the information needed to meet many real-world business needs.

The first session will concentrate on crafting simple MDX expressions and queries whose purposes, for the most part, are to return a set of data. Bill will review the structure of a cube, using the sample "Adventure Works" cube as an example and is available to anyone installing SQL Server Analysis Services 2008 R2. He will also outline the components of simple MDX syntax, explain how to begin writing basic expressions and queries, expose basic member functions and introduce filters (or “slicers”). Finally, we will have the chance to explore core MDX functionality, including calculated members and named sets.

Click here to register for the video and presentation slides.

All the best & happy learning.

Sudhir