Archive for April, 2012

SQL Server 2012 – BI Semantic Model, Multidimensional vs Tabular

The landscape of Business Intelligence changes with SQL Server 2012. Microsoft has introduced a new “model” – the BI Semantic Model.
Don’t get too hung up on this word though, the BI Semantic Model is really just an umbrella terminology that says as long as a source follows the BI Semantic Model, it will be supported in the tools. This is the eventual goal anyway; for now there is still a discrepancy which tools support what.

The multidimensional model is the traditional OLAP model. Data source is a cube, that is processed from a data warehouse that usually follows a star schema. Complex calculations and queries using MDX can be done against it. It also allows write-backs.

The new tabular model is not really a “new model” because it is a relational model. Yes the same model we’ve grown accustomed to using when we query our transactional OLTP databases. The difference is, SSAS Tabular is an in-memory database. This is fueled by the xVelocity engine (previously known as Vertipaq), and it also leverages columnstore indexes. All calculations are in memory, and this makes tabular models really fast.

From a UI perspective, if you’re curious, this is what the SSAS engines look like. Notice how the node icons are different:

Here’s a small comparison of Multidimensional vs Tabular:

Fun times ahead!

Additional Resources:
Dustin Ryan’s Creating your First Tabular Model
Simran Jindal’s What is the Business Intelligence Semantic Model Really?
Joshua Fennessy’s MultiDimensional or Tabular – Which Model to Use?

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

SQL Server 2012 x-Terms

Just finishing up my presentation for this Saturday’s Vancouver Techfest, and was putting a slide for some of the x-terms in SQL Server 2012. Just thought I’d share it:

xVelocity In Memory Analytics Engine

In SQL Server 2008 R2 – this was introduced as Vertipaq for PowerPivot for Excel (see James Beresford blog about Vertipaq)
In SQL Server 2012 this technology was integrated into Analysis Services.
In SQL Server 2012, it was also rebranded into xVelocity. The reasoning is that this is going to become part of a bigger xVelocity family for next generation performance improvements. Simran Jindal has a great diagram that illustrates this family. Thanks Simran, very helpful!

A one liner summary of xVelocity?

This is SQL Server going fast – sometimes really really fast (think 100X faster)!

MDX

- Multidimensional Expressions
- Calculation and Query Language for multidimensional cubes
- Check out additional examples from Chris Webb’s blog or the MSDN MDX Reference

DAX

- Data Analysis Expressions
- Calculation language for PowerPivot and SSAS Tabular (for now)
- Designed to “feel familiar” to those who know Excel formulas and functions
- Check out some common DAX expressions from BIDN.com, or the DAX Technet Wiki Article, or the MSDN DAX Online Function Reference

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Admiration for Pragmatic Works Foundation

Here’s a quote from the Pragmatic Works Foundation site:

“The Pragmatic Works Foundation is a non-profit organization created to find passionate people who are interested in joining the technology field but cannot make the financial investments needed for training and hardware. The goal is to provide candidates the opportunity to learn new technologies from industry experts. In addition to offering free training, we work with our onsite recruiter to help place candidates who complete training with a new career in technology. We all respect and honor those who have served for our country, that’s why we are devoted to helping veterans that are interested in transitioning to a new IT career.”

Pragmatic Works Foundation is like the SQL Server Freshman Scholarship. I see it is as such a noble cause; looking for people who may want to switch up their careers, but just don’t have the resources to hit the ground running. Don’t get me wrong – there are a lot of great free resources out there that can help – the SQL Server User Groups, the SQLSaturdays, the Virtual Chapters and 24HOP. But for someone who might be completely new to technology, it might be hard and intimidating to join these events without someone holding your hand, or just guiding you.

I think this is the great complement that Pragmatic Works Foundation offers. They find the qualified candidates, provide the training in a small, close, study environment, and they sponsor all the resources – yes, including brand new laptops and books. How great is that?

No Pragmatic Works didn’t pay me to write this post, but I really just wanted to give them a big kudos for this great project. This is a great testament to how great the SQL Server Community is. I can’t say enough about how happy I am being part of this #SQLfamily community.

Personally, this re-affirms some of the things I want to do and save up for. At some point, I’d like to be able to do something similar. Give something back in such a fashion. I’ve received help and encouragement when I was just starting, and up until now, and I’d like pay it forward. Until then, I will continue doing my baby steps. One student at a time. One SQLSaturday at a time.

VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Creating a SQL Server 2012 BI development environment (VHD)

KIWI

Microsoft and Richard Davis have put together a great how-to document for creating a SQL Server 2012 VHD for development and experimental purposes.
How to Build a SQL Server 2012 Hyper-V Virtual Machine (KIWI build)

So if you are thinking of exploring the new SQL Server 2012 capabilities, including the new BI capabilities (PowerView! xVelocity!), head on to his site. It’s going to be a long process, but at least Richard has given us a comprehensive list of to-do’s.

The only thing I would deviate from in this list is creating separate VMs for the DC, SharePoint 2010, and potentially putting SQL Server onto its own VM too, and creating a private network between the 3 VMs. Will give you more of a “real world” production feel, although this will for sure tax out your host.

SQL Server BareMetal

Dandy Weyn also has a blog post, and this time taking a different approach. This is a great approach for people who often tear down, build up dev environments, especially for Hands On Labs. Dandy’s approach is using unattended scripts for all installations. All the software must follow a specific hierarchy, or you are welcome to change it as long as you update the corresponding scripts.

Check it out!
SQL Server BareMetal Workshop series – part 1: How to build an installation VHD file that includes all software needed to start with SQL Server 2012 Database and Business Intelligence

SQL Server BareMetal Hands On Labs

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
`