Blog ala Vidar

Today a couple of us in Ølen had the pleasure of attending a session with Jan Ove Halvorsen with SSAS (SQL Server Analysis Services) as topic. This was an introduction to SSAS, although most of it was heavy stuff. I’ve played with SSAS before, but have had problems understanding some of the basics. This might help you on the way.

What is SSAS?

SSAS is part of the OLAP BI (Business Intelligence) part of SQL Server.  It’s also a part of the SQL Server licensing. This means that if you own a SQL Server license, you also own a SSAS license. It is recommended to install it on a server beside SQL Server, because it’s heavy on RAM usage. To connect to it you either use Visual Studio, Management Studio or an API. You can also connect to it using ADOMD.NET.

SSAS Modes

There are three main modes in SSAS:
MOLAP – Multidimensional OLAP – Data, aggregations and structures are stored in OLAP
HOLAP – Hybrid OLAP – Mix of MOLAP and ROLAP
ROLAP – Relational OLAP – Uses data from SQL Server but stores some structural data in OLAP

This means that you don’t need to run non-live data. For instance you can run MOLAP on monthly updates with a SQL Server Agent job running every month (or similar), storing everything in SSAS. This makes it VERY fast to query. If you need more live data, you can run ROLAP, so you get live data all the time. This will not be as fast, and it will also give a load on the SQL Server each time a query is executed.

With the 2005 version there came a new feature to SSAS, enabling it to “subscribe” to updates on the SQL Server. So, if you’re running ROLAP, it will generate the cube, and just use that local cube till there’s updates in the underlying data (on the SQL Server). When there’s an update SSAS gets a notification that there are changes, and it will rebuild the cube. Either the whole cube, or just parts of it.

OLAP Cubes and Dimensions

In SSAS you’re browsing cubes. A cube is a “collection” of dimensions. A dimension is a set of data, in many cases a one to one relation to a table or similar in SQL Server. This might for example be “Products”, “Cities”, “Countries” or similar. You also must have a time dimension. This means that all your data are aggregated so that every possible join of these (Products, Cities, Countries and Time) are stored as one “table”. This is what’s making this so fast to query, because everything’s “prejoined”.

Hierarchy

You can create hierarchies in SSAS. The most common is the Year –> Month –> Day hierarchy. This enables you to group by year, and just by clicking expand, you’ll get group by month in your client without having to get new data from the server.

Creating cubes

There are several ways of creating cubes, dimensions and other stuff. You can either use Visual Studio, Management Studio, or you can use a query language. This can be done using MDX (MultiDimensional eXpressions) which looks a lot like Transact SQL, but is also VERY different. An example could be:

SELECT
  { [Measures].[Store Sales] } ON COLUMNS,
  { [Date].[2002], [Date].[2003] } ON ROWS
	FROM Sales
	WHERE ( [Store].[USA].[CA] )

In this example Sales is the name of the cube. Store is one of their dimensions.