• Data Warehousing,
• Business Intelligence,
• OLAP and cubes, and
• Data Mining.

1.1 Data Warehouse (DWH) technology and terms:

• You have consolidated data from multiple sources and databases in one DWH. Thats very good for business analysis.
• You have off-loaded, or seperated, "analysis" from your regular OLTP databases.
• In general, the DWH is better optimized for queries (e.g.: less joins needed)

• Additional resources must be available like cpu power and Storage, which could be very significant.
• Additional support and manpower is needed which can vary between "some light work", to a whole department involved with the DWH.
• In some cases: A DWH "lives" and it never seems to get quite finished.
  

• SSAS (SQL Server Analysis Services) is the subject of this note, so we will see more of this environment later on. It's mainly involved with socalled "cubes".
  
• SSIS (SQL Server Integration Services) enables you to perform ETL from various sources, into various destinations like a SQL Server based DWH.
• SSRS (SQL Server Reporting Services) enables you to create "Reports" from various datasources, of which a DWH could be an excellent source for reporting.

1.2 OLAP and Cubes:

SELECT {[dim_REGIONS].[City]} ON COLUMNS,
{[measures].[AmountSold]} ON ROWS
FROM [MyCube]

1.3 A few words on ETL:

ETL is centered around:

- "Extracting", meaning how to get data from a source system,
- "Transforming", meaning do we need to "massage" or modify data, and
- "Loading", meaning how to get data into the target database.

Sources of data can be anything.

Usually, your DWH is build on an Oracle database, or SQL Server database,
or any other professional type of RDBMS. So that's usually the target type of database.

The sources however, can be of multiple types, like tabledata from other database systems, or flat files,
XML files, Office documents etc..

Many third-party tools (BI tools) exists, which can materialize this sort of jobs, and often you are able to
schedule them too (from e.g. the native job-agent belonging to the RDBMS, or the OS, or other sort of scheduling).

But the RDBMS itself usually has lots of facilities too, for example "prompt tools", but GUI like too.
This is certainly true for, for example, Oracle and SQL Server.
If you really would go (or maybe you are already) "in-depth" in those Oracle tools, the parallel options
are absolutely great.

For SQL Server, you might explore the native tools:

-bcp (OS prompt tool).
-TSQL scripts
-BULK INSERT tsql statement
-Import/Export Wizard
-SSIS

For Oracle, you might explore the native tools:

-sqlloader (OS prompt tool)
-impdp/expdp (OS prompt tools)
  . transportable tablespace using impdp/expdp
  . export or import of a full schema using impdp/expdp
- external table facility
- DBMS packages (plsql)
- OEM/Enterprise Manager
- further plsql options

2. A few notes before using Visual Studio or BIDS for building a Cube.

2.1 Create a fact- and dimension tables and load some data.

Our goal is to build a cube. It will be based on a simple starschema. So, we need some actual tables with some data.
Thus we need to do that first. In this section, you are encourage to load and execute a script that will create
a fact table and three dimension tables (just like was shown in figure 3).

I hope you have some SQL Server test system lying around somewhere. In the instance, create a database called "TEST" (or use some other name).

From the Management Console, start a Query Window, and make this TEST database your current database.

Copy and paste the following script in your Query Window, and execute it.
The script will only create the tables, and load some sample data in them.

-- 1. CREATE TABLES:
-- -----------------

create table dimREGIONS
(
RegionID int not null PRIMARY KEY,
City varchar(64) not null,
StateProvence varchar(64) not null,
Country varchar(64) not null
)
GO

create table dimPERIODS
(
PeriodID int not null PRIMARY KEY,
Year smallint not null,
Quarter smallint not null,
Month varchar(32) not null,
)
GO

create table dimPRODUCTS
(
ProductID int not null PRIMARY KEY,
ProductName varchar(64) not null,
Catagory varchar(64) not null,
Description varchar(64) null
)
GO

create table factSALES
(
ProductID int FOREIGN KEY references dimPRODUCTS(ProductID),
PeriodID int FOREIGN KEY references dimPERIODS(PeriodID),
RegionID int FOREIGN KEY references dimREGIONS(RegionID),
AmountSold int,
Costs decimal(7,2),
Revenues decimal(7,2)
)
GO

-- 2. LOAD TABLES:
-- ---------------

-- load dimREGIONS

insert into dimREGIONS values (1,'Amsterdam','NH','Netherlands')
insert into dimREGIONS values (2,'Alkmaar','NH','Netherlands')
insert into dimREGIONS values (3,'Rotterdam','ZH','Netherlands')
insert into dimREGIONS values (4,'Utrecht','UT','Netherlands')
insert into dimREGIONS values (5,'New York','NY','USA')
insert into dimREGIONS values (6,'Washington','DC','USA')
GO

-- load dimPERIODS

insert into dimPERIODS values (1,2008,1,'januari')
insert into dimPERIODS values (2,2008,1,'februari')
insert into dimPERIODS values (3,2008,1,'march')
insert into dimPERIODS values (4,2008,2,'april')
insert into dimPERIODS values (5,2008,2,'may')
insert into dimPERIODS values (6,2008,2,'june')
GO

-- load dimPRODUCTS

insert into dimPRODUCTS values (1,'Mountainbike','Bikes','Specifically designed for offroad')
insert into dimPRODUCTS values (2,'Citybike','Bikes','Specifically designed for use in cities')
insert into dimPRODUCTS values (3,'Racingbike','Bikes','Specifically designed for speed')
insert into dimPRODUCTS values (4,'Rollerskates','Skates','Specifically designed to break legs')
GO

-- load factSALES

insert into factSALES values (1,1,1,55,200.80,3000)
insert into factSALES values (1,3,1,7,99,5000)
insert into factSALES values (2,2,3,9,150,2000)
insert into factSALES values (2,5,5,95,8000,16000)
insert into factSALES values (2,2,3,23,1900,6000)
insert into factSALES values (2,6,4,28,1150,2000)
insert into factSALES values (4,3,1,12,900,5000)
insert into factSALES values (1,2,4,120,9000,50000)
insert into factSALES values (3,3,1,2,100,1000)
insert into factSALES values (4,2,4,70,9000,50000)
insert into factSALES values (2,1,5,95,1100,33000)
insert into factSALES values (2,2,5,65,900,14000)
insert into factSALES values (2,3,5,75,1900,15000)
insert into factSALES values (3,3,5,29,450,4700)
insert into factSALES values (3,1,5,11,650,2750)
insert into factSALES values (4,6,1,135,1150,4750)
insert into factSALES values (4,5,1,119,1050,4210)
insert into factSALES values (3,2,1,22,150,1250)
GO

Exercise:

As an exercise, you probably want to view a nice graphical datamodel of your DWH.
Well, that's certainly possible.
In the Management Console, click the "+" symbol that you see in front of the TEST database.
This container will fold open, and you probably will see the "Database Diagram" as the first sub-item.
Right-click the "Database Diagram" and choose "New Database Diagram". It is easy to follow the steps that will follow.

The result will be a nice graphical "Entity Relationship Diagram" of your four tables.

Now that we have a DWH with tables and data, we can play, and we will create a cube in Chapter 3.

2.2 Relation between Visual Studio (or "Business Intelligence Designer Studio"), and SSAS.

On the one hand, we have a SQL Server database, which is our DWH (the fact table and dimension tables).

A (multidimensional) cube is something like an external entity, actually considered to be "loose" from your DWH.
Connections to the DWH exists only when you populate it with data (there exists some exceptions).
Once loaded, the cube is again disconnected from the DWH, until the next refresh (the next re-population).

You might have two questions now.

⇒ How does one create a cube from the tables in a DWH?

⇒ Where is a cube stored?

You create a cube using "Visual Studio", or "Business Intelligence Designer Studio" (BIDS),
which both are pretty much the same thing.

Once you have build the cube, then you "deploy" it, meaning that metadata gets registered with the SSAS service.
You probably know, that with SQL Server Management Studio, you not only can connect to a Database Engine, but to SSAS too.
If a cube was indeed deployed using BIDS or Visual Studio, then in SQL Server Management Studio (and connected to SSAS),
you can see all properties of the cube, like which fact table and dimensions it contains, and many other properties.
Also, when connected to SSAS, you can also lauch MDX queries using a Query Window.
But a (molap) cube will not be stored in a SQL Server database.

About the actual physical storage of a cube, it depends a bit on the "OLAP" model used (MOLAP,ROLAP etc..)
when cube is designed. For example in the MOLAP case, it will be stored on disk, perhaps in multiple "partitions".
So, here the cube is not stored in the database. It will often use a "proprierty" storage structure on disk(s).

However, there are several OLAP models in use, for example:

- MOLAP is short for "Multidimensional" OLAP, and it's the model which is traditionally used for what
we usually understand to be a (multidimensional) "cube". This is the model we study in this note.

- ROLAP is short for "Relational" OLAP. It may come to you as a surprise, but the starschema's already shown
in this document (and created by the script of 2.1), actually are (simple) ROLAP implementations.
So, with ROLAP, we formally do not have an external "cube", although some Vendors like Microsoft let's you
create a cube, while the "relational" part of ROLAP refers to the storage/caching model (which uses the DWH).
So with ROLAP, we do not have such a multidimensional cube, as we do have with MOLAP.

- HOLAP. Some hybrids forms exists as well.

In this note, we will use MOLAP, which also means that we create and study Multidimensional cubes.
In such a case, it's not stored in a SQL Server database, except for some metadata.
The storage model is filebased with a proprierty structure. If it is anticipated that a cube would become very large,
you could define "measure groups" which might be stored on different disks.

3. Building and Deploying a cube.

This section will show you a few screendumps of using Visual Studio (or BIDS) on how to build and deploy a cube.
It's not very hard to do, once you have a starschema which forms the basis for a (molap) cube.

Remember, you design a cube using a client tool like Visual Studio (or BIDS), but once you deploy the cube,
only then you actually will register the metadata with the SSAS service.
From then on, you can "see" it too from the "SQL Server Management Console" (SSMS), if you are connected to SSAS.
Then, you could use it from Sharepoint, or use Excel, or use MDX queries from the Management Console etc.. etc..

So, deploying a cube, is like "making it alive for access" (under the control of SSAS).
You still need to populate the cube, but access control goes through SSAS.

The next step then would be to calculate or "populate" the cube, at which process the cube gets loaded with data and
parent and child aggregates are calculated (see chapter 1).
The population of the cube is a seperate step, which can be repeated later at any time.

In most cases, the cube is only "connected" to the DWH, when it gets populated. At all other times, it is just disconnected.


Step 1: Creating a "Multidimensional" project in Visual Studio:

If using SQL Server 2005/2008, you may find the "Business Intelligence Designer Studio" in your Start Menu.
With SQL Server 2012, it's possible that you only see "Visual Studio", but both tools are actually quite the same thing.

Start up Visual Studio, click File, click New, choose "Project".
Since we want a molap cube, in the following dialog box, choose "Analysis Service Multidimensional and DataMining".

Fig 4.


Note that I store the project in "c:\dwh". You might choose a similar location.


Step 2: Creating a "Datasource":

After step1, Visual Studio will show you several "sections" on your screen. Note the "Solution Explorer" (per default on the right side).
First we will create a "Datasource" which is not much more than creating a "connect string" to the DWH.
This is logical: your project "needs to know" to which SQL Server instance, and to which database, it should connect.

So rightclick the "Datasources" container, and choose "New Datasource"

Fig 5.


Next, the "New Datasource Wizard" will popup. Essentially you will next see a couple of Dialogboxes where you
have to select a Server instance, and a database, and which credentials for the connection should be used.
I skip those dialogboxes, because it's likely you have seen similar dialogboxes before. It's quite simple anyway.
Give the Datasource a name like "Test" (or some other name you like).

However, there might be one small optional "hurdle" to take, which is the "select impersonation information".
Here you can choose from several credentials which your project will use when you deploy it.
If, at the final step, the deploy goes wrong, it could well be due to your choice of the account used (insufficient permissions).

Step 3: Creating a "Datasource View":

Once the "Datasource" has be set, now we can choose the objects (fact and dimensions) which will form the basis of our cube.
For this, rightclick "Datasource Views" and choose "New Datasource View".
The "New Datasource View Wizard" will startup. First, select a "Datasource". This will be the Datasource as was created in Step 2.

The next screen allows you to select the tables which form the basis of our cube, as shown in figure 6.

Fig 6.


Select all relevant objects (in our case: dimREGIONS, dimPERIODS, dimPRODUCTS, and factSALES).

Next, provide a name for your "Datasource View", like "Test" and click "finish".

Your Visual Studio screen will alter into a screen similar to figure 7.

Note:
Note that your "Datasource" has the ".ds" extension (in my case "TEST.ds") and the "Datasource View" uses
the ".dsv" extension (in my case "TEST.dsv").
If you browse through the "c:\dwh" folder, you will also see the associated physical files.

Fig 7.


Step 4: Creating the Cube:

Allright, we are all set to create the cube. We have a Datasource to locate the Server and database. And we have a Datasource View
which points to the objects (tables) which form the basis for our cube.

In the Solution Explorer, rightclick the Cubes container, and select "New Cube".
The "New Cube Wizard" will start, which will guide you through the design process.

The "New Cube Wizard" will first present you the "Select Creation Method" dialogbox, where you can choose from
a number of options, like "Create an empty cube", or "Use existing tables".
We already have tables which form the basis for our cube, so please select the "Use existing tables" option.

Next, the "Select Measure Group Tables" dialogbox pops up. You know what "measures" are (see Chapter one), so what
is actually meant, is that you select the "fact table" here. So, here we select the "factSALES" object.

Fig 8.


Now the "factSALES tables" indeed contains the "measures", but maybe you want all measures to be in the cube,
or just a selection of certain measures. That's why the next dialogbox enables you to pick all measures, or just a selection.
Here, ofcourse we choose all measures. See figure 9.

Fig 9.


Next, we can choose the dimensions we need in our cube. Here too, we select all our dimensions. See figure 10.

Fig 10.


The last dialogbox, let's you choose a cube name. Here I choose the name "Test", but you can choose whatever you like.

Fig 11.


If you click "finish", your are done designing your cube.


Step 5: Deploying the Cube:

Up to now, we have designed the cube, and stored "the source code" in some folder (in my case, it's the folder "c:\dwh").
When you deploy the cube, you register it at the SSAS service. So, the deploying is a neccessary step to take.

First we "build" the cube, hereby creating some binaries, XML files, and a setup script for registering the cube to SSAS.
Then we "deploy" the cube, which actually registers it with SSAS.

From the main menu in Visual Studio, click the "build" option and first choose "Build Solution".

In the output section in Visual Studio, you see output similar to:

------ Build started: Project: MultidimensionalProject1, Configuration: Development ------
Started Building Analysis Services project: Incremental ....
Database [MultidimensionalProject1] : The database has no Time dimension. Consider creating one.
Build complete -- 0 errors, 1 warnings
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

There are 0 errors, but there is 1 warning. The warning is due to the fact that the tables we have used have indeed
no actual field with a Datetime datatype. This is indeed true, so we ignore that warning. Anyway, the build was successfull.

From the main menu in Visual Studio, click the "build" option again, but this time choose "Deploy Solution".

In the output section in Visual Studio, you see output similar to:

------ Deploy started: Project: MultidimensionalProject1, Configuration: Development ------
Performing an incremental deployment of the 'MultidimensionalProject1' database to the 'localhost' server.
Generating deployment script...
Add Database MultidimensionalProject1
Process Database MultidimensionalProject1
Done
Sending deployment script to the server...
Done
Deploy complete -- 0 errors, 0 warnings
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

Yes, de deployment was successfull!

To actual "fill" the cube with aggregates and data, we need to "process" or "populate" te cube.
Ïn Visual Studio, you richtclick your cube and choose "Process".
However, if it is anticipated that the data volume is very large, this could take some time, and diskspace.

It's quite a seperate science on how you best handle and tune large cubes. Many optimization tips are available,
and if you (are going to) work with large cubes, you still have a lot of research to do.
One thing is already clear: the better optimized the underlying fact and dim tables are, the better performance will be.

Other ways to deploy a cube:

From the Windows Start Menu, you might take a look at the items found under the "Microsoft SQL Server 20xx" -> "Analysis Server".
Here, the Deployment Wizard" can be found.
You can run the Wizard interactively (in a graphical way), or you can use it from the command line.
You might want to investigate what the Wizard can offer you.


Note:

It must further be clear that this simple note does not say anything sensible about tuning cubes.
Indeed, this note is no more than a general and simple overview.

Once "populating" or "processing" has completed, you might view your cube data using Excel 2010, or you might try some MDX queries.

Well.., that's it !