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

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

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

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

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

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

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

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

<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

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

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

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

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

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

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

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

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

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

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

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