Vertica Partner QuickStart for MicroStrategy: Pharma Sales

To read this document in PDF format, click here.

The Vertica Partner QuickStart for MicroStrategy is a sample pharmaceutical sales application based in iOS, and implemented as a set of MicroStrategy dashboards powered by the Vertica Analytic Database. The dashboards present a pharmaceutical sales application for mobile phones and tablets. The QuickStart demonstrates how companies can use Vertica and MicroStrategy to quickly explore, visualize, and gain insight into their data stored in Vertica.

The Pharma Sales QuickStart was developed by MicroStrategy and adapted by the Vertica Partner Engineering team to use Vertica.

You can download the Vertica Partner QuickStart for MicroStrategy from the following location:

https://www.vertica.com/quickstart/vertica-partner-quickstart-microstrategy/

About the Vertica QuickStarts

The Vertica QuickStarts are free, sample applications created using front-end products from Vertica technology partners. For an overview, watch this short video:

The QuickStarts are posted for download on the Vertica QuickStart Examples page.

Note The Vertica QuickStarts are freely available for demonstration and educational purposes. They are not governed by any license or support agreements and are not suitable for deployment in production environments.

About MicroStrategy

MicroStrategy offers a range of enterprise reporting, data discovery, and analytical tools. For details, visit the MicroStrategy website.

Requirements

The Pharma Sales QuickStart requires a Vertica database server, the Vertica ODBC client driver, SQL server 2012 or later, and MicroStrategy.

The QuickStart has been tested with MicroStrategy 10.5 and Vertica 8.0.

Installation and Setup

To install the software that is required for running the QuickStart, follow these steps:

Install MicroStrategy

If you do not have MicroStrategy, follow these steps to install a free trial:

  1. Go to the MicroStrategy website.
  2. Click Download 30-day free trial.
  3. Enter your information.
  4. Click Download Now.
  5. Launch the executable.
  6. Follow the prompts to install MicroStrategy.

Install the Vertica Database Server

The Vertica database server runs on Linux platforms. If you do not have Vertica installed, you can download the Community Edition free of charge:

  1. Navigate to Vertica Community Edition.
  2. Log in or click Register Now to create an account.
  3. Follow the on-screen instructions to download and install the Vertica Community Edition.

Install the Client Driver

MicroStrategy uses ODBC to connect to Vertica. Before you can connect MicroStrategy to Vertica, you must download and install the Vertica client package that includes the driver.

To install the Vertica client package:

  1. Go to the Vertica Client Drivers page.
  2. Download the Vertica client package that matches your operating system and the version of Vertica that you are using.
  3. Follow the instructions in the Vertica documentation to install the driver.

Note Vertica drivers are forward compatible. You can connect to the Vertica server using previous versions of the client. For more information, see Client Drivers and Server Version Compatibility in the Vertica documentation.

Install SQL Server

Follow the instructions to install SQL Server from the Microsoft website.

Download the QuickStart

  1. Navigate to vertica.com/quickstart
  2. Select Vertica Partner QuickStart for Pharma Sales.
  3. Log in or create an account.
  4. Click Download.
  5. Navigate to the folder where you downloaded the compressed file and extract the QuickStart. The QuickStart package contains the following folders:
    • PharmaSales_Vertica: Contains the script files to create and populate the required tables including:
      • Tables_DDL_Pharma.sql: A sql file that contains the DDL statements you need to create the data warehouse in Vertica.
      • PharmaSalesInserts.sh: A script file that populates the pharmaceutical sales application.
      • Table data files: .txt files that PharmaSalesInserts.sh needs to populate the tables.
    • PharmaSales: A compressed folder that contains:
      • TEMPMD.bak: A backup file for the metadata database based on SQL server 2012.
      • PharmaSales: A folder that contains the required dashboard files.

Create the Vertica_PharmaSales Database

The Vertica Partner QuickStart for MicroStrategy connects to a data warehouse. The data is located in .txt files. Follow these steps to create the database you will use for the QuickStart:

  1. Before you can load the data you need to create a database named Vertica_PharmaSales.
  2. Place the PharmaSales_Vertica folder in the database.
  3. Run the Table DDL sql file using the following command to create the tables in the database. :

    $ /opt/vertica/bin/vsql -p <port number> - U <dbadminname> -w <password> -A -f /path/Tables_DDL_Pharma.sql
  4. After you execute the DDL statement, confirm that the tables were created using the following command:
    grep -i "create" /path/Tables_DDL_Pharma.sql | sed 's/CREATE TABLE public.//g'

  5. Execute \d and compare the table names as shown below:

Load the Data

Follow these steps to load data from the .txt files into the Vertica_PharmaSales database tables:

  1. Cd to the Table_Data folder.
  2. Grant the execution permission to the PharmaSalesInserts.sh file.
  3. Execute the script file that matches the following:
    $ ./PharmaSalesInserts.sh
  4. Confirm that the table was loaded using /d in the vsql prompt.
  5. Verify that the number of rows loaded by the script matches the information below:
DrugMarkets 37
DrugPhase 146
DrugPrices 3696
F_Daily All 1136802
F_DaillyAll_bkp 1136802
F_DailyCompetition 30816
F_DailyGower 1105986
F_DailyTouchPoints 276811
F_DailyTouchPoints_bak 276811
LU_AccountExecutive 295
LU_Accounts 198
LU_Companies 6
LU_Dates 2557
LU_DatesHolidays 70
LU_DrugPhase 3
LU_Drugs 132
LU_Geography 51
LU_InsCompanies 5
LU-InsPlans 22
LU_Practitioners 6139
LU_PractitionersPhoto 108
LU_PractitionersSchool 167
LU_PractitionersSpeciality 71
LU-Touchpoint_Type 4
PractitionerSpecs 6757
TR_Comments 9
Task_transaction 1499
Touchpoint_Log 3171

Restore the Metadata Database on SQL Server

To restore the metadata database on SQL Server 2012, use the TEMPMD.bak file:

  1. Open SQL server and Authenticate yourself.
  2. Right-click Databases and choose Restore Database.

Create a DSN for the Vertica_Pharmasales Database

Create a DSN for your Vertica database on the machine that hosts MicroStrategy. You can use any port. For more information, see Setting up an ODBC DSN in the Vertica documentation.

Configure MicroStrategy

Follow these steps to configure MicroStrategy to connect to the pharmaceutical sales application:

  1. Click the Windows Start icon.
  2. Under MicroStrategy Tools, select Configuration Wizard.
  3. Select Configure Intelligence Server.
  4. Click Next.
  5. The Configuration Wizard appears. In the wizard, enter your DSN, User Name, password, host, and port.
  6. Click Next.
  7. Under Available Projects, check Pharma Sales Enablement.
  8. Click Next.
  9. Click Finish.

Configure the Pharma Sales Enablement Project

You must configure the Pharma Sales Enablement project before you can run the dashboards:

  1. Open the MicroStrategy Developer client and connect to the server where you added the project.
  2. Select Administration > Configuration Manager > Database Instances.
  3. Create the database instance for Vertica using the ODBC connection by naming the database.
  4. Click OK.
  5. In the Database Connections wizard, select Vertica_Pharmasales and choose your database login name.
  6. Click OK.
  7. In the Database Logins wizard, enter your login ID and password.
  8. Click OK.
  9. Select your database connection type.
  10. Click OK.
  11. Restart the server.
  12. Right-click the Pharma Sales Enablement project and select Project Configuration > Database Instances.
  13. Select the database instance you created.
  14. In the Project Configuration Window, click OK.
  15. Open the Pharma Sales Enablement project.
  16. Select Schema > Warehouse Catalog. Configure the Read settings if asked.
  17. Remove the where clause from the upper panel,
  18. Click Save.
  19. Under the Schema tab, select Update Schema and click Update.
  20. Close the project and restart the server.
  21. Open the Pharma Sales Enablement project.
  22. Right-click the project and select Project Configuration > Governing Rules > Result Sets.
  23. In the configuration window, set the value of All other reports to -1.
  24. Click OK.

Edit Project Reports

  1. Open the Pharma Sales Enablement Project.
  2. Right-click the project and select Public Objects > Reports > Mobile Apps > iOS.
  3. The Phone and Tablet folders appear. Edit the following reports:
    1. Phone > Supporting Documents > Phone_TASk_FFSQL
    2. Phone > Support Documents > Touchpoint FFSQLPhone
    3. Tablet > Supporting Objects > Datasets > Practitioners_FFSQL
    4. Tablet > Supporting Objects > Datasets > Touchpoint_FFSQL
    5. Tablet > Supporting Objects > Datasets >TASK_FFSQL

  4. Select each report and right-click it.

  5. Choose Data > Freeform SQL Definition.
  6. Change the default database instance to your Vertica Database.
  7. Rewrite the Freeform SQL for each of the reports using the following code:
    1. Phone_Task_FFSQL:
      SELECT t.Task_TransactionID, t.DoctorID, p.Practitioner_DESC, t.DueDate, t.Assignment, 
					t.BrandID, d.Drug_Desc, RTRIM(t.Signature)
      FROM Task_Transaction AS t
      LEFT OUTER JOIN LU_Practitioners AS p
      ON t.DoctorID=p.Practitioner_ID
      LEFT OUTER JOIN LU_Drugs AS d
      ON t.BrandID=d.Drug_ID
      WHERE Device_ID LIKE '[?58753AB549FDEEFA26A60F84B3E10FD8[?' OR Device_ID LIKE '1'
    2.  TouchpointFFSQLPhone:
      SELECT 
      DATE (t.TP_Date) AS DATE
      ,t.Prescriber_ID
      ,t.Touchpoint_Type
      ,d.Drug_ID
      ,d.Drug_Desc
      ,t.Quantity
      ,t.Comments
      ,t.Transaction_ID
      ,c.Touchpoint_Type_DESC
      ,RTRIM(t.Signature)
      FROM Touchpoint_Log t
      LEFT OUTER JOIN
      LU_Practitioners p
      ON		t.Prescriber_ID= p.Practitioner_ID
      LEFT OUTER JOIN
      LU_Drugs d
      ON		t.Brand_ID=d.Drug_ID
      LEFT OUTER JOIN
      LU_Touchpoint_Type as c
      ON                           t.Touchpoint_Type=c.Touchpoint_Type
      where t.Device_ID LIKE '[?58753AB549FDEEFA26A60F84B3E10FD8[?' OR t.Device_ID LIKE '1'
      ORDER BY  t.Transaction_ID DESC

    3. Practitioners_FFSQL:
      SELECT Practitioner_ID,Practitioner_DESC FROM LU_Practitioners ORDER BY 
			Practitioner_ID DESC limit 20
    4. Touchpoint_FFSQL:
      SELECT 
      DATE(t.TP_Date) AS DATE
      ,t.Prescriber_ID
      ,p.Practitioner_DESC
      ,t.Touchpoint_Type
      ,d.Drug_ID
      ,d.Drug_Desc
      ,t.Quantity
      ,t.Comments
      ,t.Transaction_ID
      ,c.Touchpoint_Type_DESC
      ,RTRIM(t.Signature)
      ,SYSDATE
      FROM Touchpoint_Log t
      LEFT OUTER JOIN
      LU_Practitioners p
      ON		t.Prescriber_ID= p.Practitioner_ID
      LEFT OUTER JOIN
      LU_Drugs d
      ON		t.Brand_ID=d.Drug_ID
      LEFT OUTER JOIN
      LU_Touchpoint_Type as c
      ON                           t.Touchpoint_Type=c.Touchpoint_Type
      where t.Device_ID LIKE '' OR t.Device_ID LIKE '1'
      ORDER BY  t.Transaction_ID DESC

    5. TASK_FFSQL
      SELECT SELECT t.Task_TransactionID, t.DoctorID, p.Practitioner_DESC, t.DueDate, t.Assignment, t.BrandID, d.Drug_Desc, RTRIM(t.Signature)

      FROM Task_Transaction as t

      LEFT OUTER JOIN LU_Practitioners as p

      ON     t.DoctorID=p.Practitioner_ID

      LEFT OUTER JOIN LU_Drugs as d

      ON t.BrandID=d.Drug_ID

      WHERE Device_ID LIKE '[?58753AB549FDEEFA26A60F84B3E10FD8[?' OR Device_ID LIKE '1'
  8. Click OK.
  9. Click Save and Close.

Run the Dashboards on MicroStrategy Web

  1. Start the MicroStrategy Intelligence Server.
  2. In MicroStrategy Tools, open the Web Administrator client.
  3. Run the dashboard Home Screen. You will see the following:

Run the Dashboards on MicroStrategy Mobile

To use the application on a mobile device, you must configure the mobile server:

  1. Install the MicroStrategy Mobile application on your mobile device.
  2. From the Web Server, connect the intelligence server to the location where the pharmaceutical app is hosted.
  3. Click Mobile Configuration > Define New Configuration.
  4. In the Device dropdown, choose your mobile device option. This document uses an iPhone. The device must have the Microstrategy mobile client. If the intelligence server requires a VPN to connect, the device must be connected to that VPN.
  5. Under the iPhone Settings tab, add your configuration name and set the network timeout to 600.
  6. Under the Connectivity Setting tab, select Windows as the Authentication mode and add your Windows credentials.
  7. Click Configure New Mobile Server.
  8. Add the Mobile Server IP.
  9. Uncheck Use default authentication.
  10. Under Default Project Authentication, set Windows as the Authentication mode and add your MicroStrategy credentials.
  11. Click Configure New Project.
  12. From the Project Name dropdown, select Pharma Sales Enablement.
  13. Confirm the Authentication mode is set to Standard.
  14. In the Home Screen tab, select Display the contents of a folder.
  15. Add your MicroStrategy credentials and click login.
  16. Click the Mobile Apps folder.
  17. Click Current Folder.
  18. Click Save. The saved configuration will appear in the Mobile Configuration list.
  19. From the list, select the green circle icon.
  20. Click Generate URL.
  21. Copy the URL and paste it in a web browser.
  22. When prompted to open the page in MicroStrategy, click Open.
  23. Add your Windows credentials to open the project:
  24. Click the iOS folder.
  25. The phone and tablet folders appear. Select the folder you wish to explore.

QuickStart Example Dashboards

The QuickStart dashboards present sample pharmaceutical sales data that a pharmaceutical company might use to track sales over time.The company could use this information to understand which products perform better than others to better serve their customers.

Brand Performance Dashboard

The Brand Performance dashboard gives an overview of how different brands perform in comparison to competitors:

Prescribers Dashboard

The Prescribers dashboard provides an overview of different prescribers and their sales over time:

Prescriber Details Dashboard

The Prescriber Details dashboard drills down into further detail about prescriber information:

Known Limitations

  • Some of the dashboards do not automatically resize to fit on mobile devices.
  • The touchpoint logger does not work for submission in the phone or tablet module. This does not affect the dashboard displays.
  • The Prescriber Survey dashboard does not work for submission.

For More Information