Business Intelligence with MicroStrategy Cookbook

Install the SQL Server 2012 Express LocalDB engine by running:

C:\install\SqlLocaLDB.MSI

Accept the agreement and click your way out through the Next button; no special attention other than clicking on Continue to allow the user account control permission to install. The setup will take one minute, if not seconds, and it's very simple.

Install SQL Server Native Client 11.0 by running:

C:\install\sqlncli.msi

Leave the default components selected for installation and click on Next until the end. As before, give the user account control permission to continue.

Install SQL Server 2012 Command Line Utilities by running:

C:\install\SqlCmdLnUtils.msi

Accept the agreement, allow permission if prompted, and click on Next until the end.

Create a directory named C:\datawarehouse; this will be the location of our database.

Open in Notepad the file instawdwdb.sql and modify lines 36 and 37 so that they look like:

:setvar SqlSamplesDatabasePath   "C:\datawarehouse\"
:setvar SqlSamplesSourceDataPath "C:\install\AdventureWorks 2008R2 Data Warehouse\"

Save the file and close Notepad.

Open a command prompt and enter the following command:

sqlcmd -S (localdb)\v11.0 –i "C:\install\AdventureWorks 2008R2 Data Warehouse\instawdwdb.sql"

Open the sqlcmd utility again:

sqlcmd -S (localdb)\v11.0

At the 1> prompt, write the following instructions each on its own line, followed by the Enter key:

use AdventureWorksDW2008R2
go
select count(1) from FactInternetSales
go
select count(1) from DimCustomer
go
exit

Run the command:

C:\install\dotNetFx40_Full_x86_x64.exe

Accept the license agreement and watch the blue progress bars filling up slowly. This can take a while; you may have a coffee if you need. When it's finished, run:

C:\install\NDP40-KB2544514-x86-x64.exe

One accept button (the license agreement) and two more blue progress bars to go. If you're prompted to reboot, please do so.

Open the Server Manager (Start | Administrative Tools | Server Manager) and scroll to the Roles Summary pane.

Click on the Add roles link, you will see the Add Roles Wizard window; click on Next.

Select Web Server (IIS) and click on Add Required Features in the pop-up message box; now click on Next and then on Next again.

In the Select Role Services page, carefully check the following if not selected already:

Click on Next, then on Install, and when finished click on Close.

You should have extracted the contents of the MSTR_93_GA_WindowsFullzip archive into C:\install\, so run the executable:

C:\install\MICROSTRATEGY.exe

In the welcome window, click on Install Software and then on Begin MicroStrategy Platform Installation. If a User Account Control dialog pops up, click on Allow.

The MicroStrategy-InstallShield Wizard appears asking you to choose a language for the setup, accept the default English and click on OK.

You will find yourself in front of yet another welcome page; politely click on Next and agree to the following warning message saying that the print spooler and remote registry service will be shut down during the setup (they will restart when you reboot the machine).

Now accept the license agreement and click on Next.

In the Customer Information window, fill your username, company, and paste the license number you received via e-mail.

Click on Next, which will bring you to the Choose Destination Location page, accept the defaults and click on Next again.

Here you have the features selection list; depending if you used the free license or the evaluation key you will be presented with more or less choices. Simply accept the defaults and click on Next (if you receive a warning about a missing PDF reader disregard it, for now we do not need this feature).

Click on Yes, the web server will be stopped and restarted.

Then click on Yes again, the setup will enable the web service extensions in IIS for us.

On the MicroStrategy Health Center Setting page, accept the defaults and click on Next.

On the Server Activation page, click on Next; fill in the Name, Location, and Use fields. Then click on Next.

On the following page you must select the first option: I am an employee of the licensed company., otherwise you won't be allowed to continue. All the fields in the Installer Information group are mandatory except State, so be patient and fill in them all.

Lastly, you may choose to request an activation code, which will be sent to your e-mail. Please do so unless you're behind a corporate firewall which in some cases may prevent the operation to complete.

Review the summary information and click on Install.

The full install may last anywhere between 5 and 40 minutes depending on the hard drive speed—time for a break.

At the end of the progress bar, read the Readme file if you want, and click on Yes, reboot the machine when prompted.

Click on Start | All Programs | MicroStrategy | License Manager.

In the main window, select the tab named License Administration; the first option Activate Server Installation should be already selected. Click on Next.

In the following page, ensure that the radio button Generate Activation File and Request Activation Code is selected and click on Next and then on Next again, until you come to the page where you should enter all the details about your company and contacts.

The e-mail address is important to receive the activation code. Click on Next.

Select Yes, I want to request an Activation Code now and hit Next. You will shortly receive an e-mail message.

Click on Done to return to the beginning of the license administration process and start again with the Activate Server Installation radio button selected.

Click on Next, and this time select the option Server Activation using Activation Code.

In the textbox underneath, paste the code you received by e-mail and click on Next, and then click on OK, then on Done, and then on the Exit button.

Bring up the sqlcmd command utility once again with:

sqlcmd -S (localdb)\v11.0

And when the 1> prompt appears, type this (each command in its own line and followed by the Enter key):

create database metadata
go
exit

Click on Start | Administrative Tools | Data Sources (ODBC) and select the System DSN tab.

You will find some DSNs already created for you by the setup; we need to create two more (yes, you're right: one for the metadata and one for the data warehouse).

Click on the Add… button, scroll down the list to select SQL Server Native Client 11.0 and click on Finish.

In the Create a New Data Source to SQL Server window, type these values:

Click on Next and then on Next again.

Click the checkbox labeled Change the default database to and in the drop-down list underneath, select metadata. Leave all the remaining default values and click on Next and on Finish.

Click on Test Data Source… and look at the message: if it says TESTS COMPLETED SUCCESSFULLY!, you're good to go. Click on OK and then on OK again.

Repeat all the steps to create another DSN to the data warehouse, this time with the following values:

In Start | Run dialog, type services.msc.

This will open the Services console; scroll down to find MicroStrategy Intelligence Server, it is now executing with Local System credentials, double-click on the service.

Select the Log On tab and click on the This account radio button.

Then type your current username and password in the corresponding text fields below.

Now click on OK to close the dialog box.

A message will warn you that the user has been granted the right to log on as a service. Click on OK and then on OK again at the second warning telling you that you should restart the service. We'll do it in a moment.

Right-click on the MicroStrategy Intelligence Server service and select Restart from the context menu that appears.

You should already have the configuration wizard open in the background; if you closed it, you can find it in Start | All Programs | MicroStrategy. Click on Configuration Wizard to run it.

In the first page of the wizard, the first option should already be selected, so click on Next.

In this phase of the project, we're not interested in the history list or statistics, so we uncheck the corresponding checkboxes and leave just the first one checked. Then click on Next.

Now we are telling the wizard where to look for the metadata database, you should have a list of all the system DSNs present in the machine, select the metadata DSN that we created before, click on Next and on Finish.

The wizard starts creating and populating metadata tables. When the process is completed, click on Close to go back to the starting page of the wizard.

The second option Configure Intelligent Server should be automatically selected, click on Next and again select the metadata DSN from the drop-down list and click on Next.

When you're prompted for the username and password leave everything as default, the Administrator user still has no password, and click on Next.

Watch the wizard as it tries to connect to the recently created metadata and when the Server Definitions page appears just hit Next and Next again, as we will use all the default settings.

In the SSL Configuration page, leave the checkbox unchecked; click on Next and on Finish.

The Intelligence Server is stopping and restarting with the new configuration. Once the process terminates, click on Close and then on Exit.

Go to Start | All Programs | MicroStrategy | Desktop and click on the Desktop icon.

Double-click on the line named MicroStrategy Analytics Modules, which was created by the setup and you'll be prompted for the metadata credentials; remember? The user is Administrator with no password.

The first time you connect, a message will remind you that no project is there, click on OK. We'll create one in a moment.

When you enter the MicroStrategy Analytics Modules (we call this a project source) for the first time, you'll be watching a window like the one in the following screen capture:

Unfold the Administration folder and click on User Manger on the left pane. Then double-click on Everyone on the right pane. You will see there is only a user in this group.

Double-click on the Administrator user icon to open the User Editor.

Here, you can manage all the details of a user such as full name, login, password, or permission to see projects and which features the user has access to. There are two text fields for password with asterisks, type a new password here and confirm. Then close the editor by clicking on OK.

Exit the Desktop application and open it again. This time you will be prompted for credentials to enter the MicroStrategy Analytics Modules, type Administrator and the password you just created.