Business Intelligence with MicroStrategy Cookbook: Over 90 practical, hands-on recipes to help you build your MicroStrategy business intelligence project, including more than a 100 screencasts with this book and ebook

At this point, you should have a testing machine ready-to-use with all the samples in place. In the previous chapter, we have learned how to install SQL Server and the MicroStrategy platform. In this chapter, we start creating the schema objects that represent the building blocks of our BI project.

Schema objects are the metadata components that are logically closer to the physical structure of the database; they are used to create more complex elements (applications or public objects) that will later form reports and documents. Facts and tables, for example, are schema objects. Also attributes, which we'll cover in Chapter 3, Schema Objects – Attributes, are schema objects.

The first schema objects that we need to identify are the tables holding our facts and dimensions.

The first step in a MicroStrategy BI application is creating an empty project. An Intelligence Server can host many projects. Projects can be copied from one Intelligence Server to another. Think of a project as a way to keep all the related objects together. A project must have at least one connection to get data from the data warehouse.

At the core of a data warehouse, there are facts and dimensions. They can be organized in star schemas, snowflake schemas; they can be more or less complex, and to some extent undocumented. This is not a book about dimensional modeling and discussing how a database layout works better than other is out of scope. Some common sense rules always apply: consistent column naming can be useful (user_id, iduser, user_code, key_user, yetanotheruserid, you name it…), constraints on tables help to pull the strings and find a way into unexplored databases. NOT NULL fields also come in handy.

I personally had my share of good and bad data. I always remember a development team manager who once asked me, "Why do you need primary keys, anyway?" I looked around and thought, "This must be a candid camera…"

If you are drowning in a very complex DB and want some relief, you can go to http://at5.us/Ch2U2 and read about the Directive 595, it may be a...

Like it or not, database tables change. Due to upgrades in source systems, mergers and acquisitions, or simply because of a shift in the project management, we suddenly find that an SKU or a customer ID is not there anymore. Let alone systems in continuous development where the database seems to have a life of its own and morphs every now and then into different shapes. We all know how difficult it is to go back and test every ETL script and every report to see if they still return reliable data.

Sometimes changes happen without notice, you come to the office on a Monday morning to see that the CFO has a different color…, then you realize that something went wrong.

There is no vaccine for this, other than carefully documenting everything and hope for the best. Let's see how to refresh our table structure information using the Warehouse Catalog window.

Logical tables are schema objects that are somehow similar to SQL views. They are not a representation of an existing table but a SELECT statement that returns a series of columns. There are cases in the course of a BI project when you need the data to be in a different shape than in the source tables, so you have two choices: modify the data warehouse or create logical tables.

Sometimes access to the data warehouse is simply out of the question, or it takes weeks, or the paperwork needed to approve the modification is just not worth it.

To be clear, whenever possible, I prefer changing the data warehouse over creating logical tables because there may be other applications that use that data and it's probably wise to have a common base for every app, but I resort to logical tables for cases like the one in this recipe when I need the data to be prefiltered for a specific purpose.

During the development of a BI project, sometimes we need to use values which do not necessarily come from tables; these can be constants such as Data refreshed at or Today. The logical tables come in very handy for this purpose.