MicroStrategy Connectivity

1
MicroStrategy Connectivity

• MIS 497

2
Project Source

• A Project Source is an alias in which
  connectivity information for all the projects in
  that Project Source is stored.

2 Tier Project Source Specifies a DSN and login
for a direct connection to metadata. 3
Tier Project Source identifies the MSTR I-Server
machine to which the client will connect.
3
Project Source (continued)

• A Project Source (PS) houses the information
  needed to connect to a Metadata at start-up
• A PS can contain several projects, each of which
  can potentially connect to a different DW.
  However, there can be only one metadata per PS,
  so all projects within a PS share the same
  metadata
• PS can be created in either 2-tier (direct
  connection to the metadata) or in 3-tier (through
  Intelligence Server) mode.
• Well have both modes of connection for different
  phases of the projects in the class.

4
Start-up Connectivity Logical Layout
2 - Tier
Direct ODBC
Project Source
MD DSN login
Intelligence Server
MD Database
3 - Tier
5
Start-up Connectivity Logical Layout

• 2 Tier (Direct)
• A local ODBC DSN must be specified along with the
  login.
• This information is stored in the registry of the
  local MSTR Desktop machine.
• ODBC PWD is stored encrypted in the registry of
  the local machine.

6
Start-up Connectivity Logical Layout

• 3 Tier (Intelligence Server)
• No ODBC connection from the client to the DB
• MSTR Desktop client will connect to I-Server
  definition, so the name of I-Server and RPC
  connection to it are necessary.
• All connection information and MD DSN are located
  on the Server machine.

7
Database Instance

• A Database Instance is a logical representation
  of a connection to a physical database.

8
Database Instance

• Each PS is associated with one MD DB Instance and
  at least one Warehouse DB Instance.
• Database Instance stores the following
  information
• Database Connection
• Type of DB (Oracle, SQL Server, Informix, etc.)
• Maximum number of connections allowed for the DB
  Instance
• DB specific optimizations (I.e. VLDB properties)
• Warehouse table prefixes (if required)
• Location for intermediate table storage
• Prioritization scheme to be used when submitting
  jobs to the DB

9
Database Connection

• A Database Connection is comprised of a DSN and
  other information necessary for connection to a
  database.
• Each Database Instance can potentially point to
  multiple Database Connections.

10
DSN and Database Login

• The DSNs listed represent the local machines
  system ODBC data sources and DSNs from other
  sources.
• A Database Login stores the UID and PWD that will
  be used to access the database or the NT network
  login

11
DSN and Database Login

• DSNs listed will list local machine DSNs and all
  other DSNs recorded in Metadata.
• In a 3 tier environment, DSN may or may not
  actually be a DSN on a local machine.
• Theres unlimited number of Database Logins that
  can be created, this functionality is needed for
  advanced administration and monitoring by DBAs
  and thus wont be discussed in this class.

12
The Big Picture
Project Source
MSTR Intelligence Server
Project
WH DB Instance
MD DB Instance
Connection Mapping
WH DB Connection
MD DB Connection
MD Database
WH DSN
MD DSN
WH Login WH UID WH PWD
MD Login MD UID MD PWD
WH Database
13
Workshop
14
Goals

• Create VMALL Warehouse Instance

Prerequisites

• Completion of Database Connectivity workshop

15
Workshop

1. Open MicroStrategy Desktop
2. Go to Tools? Project Source Manager
3. Click Add

16
Workshop

• 4. Enter your group name as the project source
  name, select your metadata DSN and enter
  appropriate login information
• 5. Click OK twice, your Project Source is created!

17
Workshop

• 6. Now connect to your Project Source, your
  default login is Administrator, the password is
  blank.
• 7. Now well create Database Instance for our
  Data Warehouse
• 8. Go to Administration?Database Instance
  Manager?Right Click select New? Database Instance

18
Workshop

• 9. Name your DB Instance VMALL Warehouse, choose
  your database type (SQL Server 7.0) and create a
  new DB Connection

19
Workshop

• 10. Select your Warehouse DSN in the list of all
  DSNs, choose Administrator as your default login.
• 11. Click OK twice, your Warehouse connection is
  created!

20
Next steps

• Well go over Data Modeling and create our
  project!

21
Resources

• MicroStrategy Administrator Guide will have
  more information on connectivity and advanced
  topics not covered in the class.