Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Microsoft Business Intelligence and Data Warehousing

Garrett Edmondson, Independent Consultant MCITP & MCTS: 10+ years experience with BI stack.

Installing SSAS 2012

I don’t usually write posts like this but there have been a few substantial changes to how SSAS 2012 is installed as compared to the previous versions.  The changes also underscore the changes that have occurred to SSAS with the introduction of the Tabular Model.

I am not going to… Read more

Enterprise Data Warehouse Architecture Options

Enterprise Data Warehouse Architecture Options

Of course, your architecture depends on your business requirements along with technical, historical, and political factors. While business requirements are usually business-specific, we can start with a list of broad requirements, or architecture goals, that most organizations would support:

1. Excellent query performance for users Read more

0 comments, 1,432 reads

Posted in Microsoft Business Intelligence and Data Warehousing on 25 April 2012

Massively Parallel Processing and the Parallel Data Warehouse

Data growth is related to Moore’s law. As computers get faster and more powerful, we are using them to process more data in more complex applications. Traditional sources such as ERP systems are processing more transactions as our organizations grow. Newer sources such as web browsing activity, mobile devices, and… Read more

0 comments, 2,967 reads

Posted in Microsoft Business Intelligence and Data Warehousing on 15 April 2012

The Kimball Approach

The Kimball Approach

There are a lot of misconceptions about dimensional modeling and the Kimball approach to building a DW/BI system. It’s worth reading this section even if you are already familiar with the Kimball approach; you might be surprised at what you learn. The Kimball approach to creating an… Read more

4 comments, 8,583 reads

Posted in Microsoft Business Intelligence and Data Warehousing on 8 April 2012

Parallel Data Warehouse – Slowly Changing Dimension

Most of my day-to-day work is currently centered around Microsoft’s Massively Parallel Processing Appliance call PDW (Parallel Data Warehouse).  Many of the same techniques common to SMP (Symmetric Multiprocessing)systems such as Kimball method Slowly Changing Dimension (SCD) are still very important.

Identifing the deltas between an incoming dataset… Read more

Building the Enterprise DW/BI System with SQL Server PDW

Building the Enterprise DW/BI System with SQL Server PDW

Most readers considering a Parallel Data Warehouse already have a data warehouse in place and are looking for ways to help handle growing data and performance demands. Many of these next-generation, large-scale data warehouse/business intelligence systems are evolving from existing DW/BI… Read more

1 comments, 1,645 reads

Posted in Microsoft Business Intelligence and Data Warehousing on 25 March 2012

Columnstore Indexs

Columnstore Indexes for Fast DW
The SQL Server 11.0 release (2012) introduces a new data warehouse query acceleration feature based on a new type of index called the columnstore. This new index, combined with enhanced query optimization and execution features, improves data warehouse query performance by hundreds to thousands of… Read more

0 comments, 1,699 reads

Posted in Microsoft Business Intelligence and Data Warehousing on 15 March 2012

SSAS Tabular Model – Query Modes

One of the most compelling features of the new SSAS Tabular Model is the ability to set the model’s Query Mode to In-memory (aka Vertipaq), DirectQuery or both!

QueryMode can be set to one of the following:

  • DirectQuery – This setting specifies all queries to the model should use…

Read more

4 comments, 1,153 reads

Posted in Microsoft Business Intelligence and Data Warehousing on 9 March 2012

MDX SSRS Parameter Values

<rant> One of the most frustrating things about working with the Microsoft BI Stack is after you go through all of the hard work of developing an ETL, Relational Data Warehouse and Cube you are confronted with creating MDX SSRS reports.  Developing SQL SSRS reports can be frustrating but add… Read more

1 comments, 4,722 reads

Posted in Microsoft Business Intelligence and Data Warehousing on 2 March 2012

Cartesian/Cross Join

I was once asked in a job interview if I knew what a Cartesian/Cross Join was.

A more interesting question is: How can a Cross Join be useful?

There are actually many good reasons to use a Cross Join. Outlined below are a couple the most common scenarios that I… Read more

2 comments, 1,186 reads

Posted in Microsoft Business Intelligence and Data Warehousing on 19 February 2012

CHECKSUM vs HashBytes

Hashing can be useful in Data Warehousing  as well  It can give you the ability to break large problems into smaller more manageable sizes  or scale out your ETL process ;) PDW even uses a similar method to distribute data internally among all the compute nodes.

In reality there is… Read more

3 comments, 4,510 reads

Posted in Microsoft Business Intelligence and Data Warehousing on 1 February 2012

Tabular Model

One of the most exciting features in SQL Server 2012 is the introduction of the Tabular Model. The Tabular model along with the multidimensional model comprise the Business Intelligence Symantec Model (BISM).

To be honest when the Tabular Model and the BISM where introduced along with DAX I… Read more

0 comments, 1,807 reads

Posted in Microsoft Business Intelligence and Data Warehousing on 31 January 2012

Master Data Services 2012

As I mentioned in a pervious post Master Data Services for SQL 2012 is a huge improvement over the SQL Server 2008 R2 version. The biggest difference is the ability for users to manage entities directly in Excel!

This is important because now Data Stewards can perform most of their… Read more

1 comments, 2,184 reads

Posted in Microsoft Business Intelligence and Data Warehousing on 19 January 2012

SSIS ETL Frameworks

In my years as a BI Professional I have seen many ETL Frameworks. They range from homegrown internal solution to out-of-the-box solutions developed by small and large consulting companies and other various providers.

There are many similarities and differences between, but they are all designed to address two general requirements: Read more

2 comments, 3,546 reads

Posted in Microsoft Business Intelligence and Data Warehousing on 4 January 2012

Learn SQL 2012

Microsoft is working on the next round of certification exams for SQL Server 2012. So now is the best time to learn about 2012. The best resources to use for preparing for the exams should all come MSDN.

The first is product documentation for 2012

Additionally, expect to see… Read more

0 comments, 981 reads

Posted in Microsoft Business Intelligence and Data Warehousing on 28 December 2011

ETL from Oracle to SQL Server

I was recently asked to provide some performance tuning recommendations for an ETL process that extracts data from Oracle using Informatica (Unix) and loads it into some SQL Server Staging tables.

Initially the ETL process was taking  20+ hrs to complete. After some preliminary analysis it was determined that the… Read more

1 comments, 1,906 reads

Posted in Microsoft Business Intelligence and Data Warehousing on 21 December 2011

Data Mining

Data mining techniques can be used in virtually all business applications, answering various types of businesses questions. In truth, given the software available today, all you need is the motivation and the know-how. In general, data mining can be applied whenever something could be known, but is not. The following… Read more

1 comments, 879 reads

Posted in Microsoft Business Intelligence and Data Warehousing on 14 December 2011

SQL Server 2008R2 BI Presenations

I recently gave a couple of presentations about the new Business Intelligence features in SQL Server 2008 R2. Here are the presentations and their PowerPoint slides.

Business Intelligence with SQL Server 2008R2 Overview

Garrett Edmondson, SQL 2008 BI MCTP MCITP

Session Level: 100
- Beginner

Audience: All
users of… Read more

0 comments, 750 reads

Posted in Microsoft Business Intelligence and Data Warehousing on 11 November 2011

Dimensional Modeling Financial Data in SSAS pt2

Unary Operator and Aggregating over time

In my last post I discussed how to arrange all of the GLs into a Parent Child Hierarchy. I also mentioned that I would address the issue of having the child GLs sum to their parent through addition or subtraction.

Using Unary Operators as… Read more

2 comments, 1,144 reads

Posted in Microsoft Business Intelligence and Data Warehousing on 3 November 2011

Dimensional Modeling Financial Data in SSAS

Part 1 – Parent Child Hierarchies and Pseudo GL Accounts

This post is born out of some real life  lessons that I learned while putting together a Financial Data Warehouse based on General Ledger (GL) data i.e. all the GL’s in the foundational financial statements from Net Income and  Balance… Read more

10 comments, 2,021 reads

Posted in Microsoft Business Intelligence and Data Warehousing on 26 October 2011

Older posts