Using MicroStrategy with Excel

1
Using MicroStrategy with Excel

• Integrating data from multiple accounts

2
Introducing the MicroStrategy Office add-in

• MicroStrategy provides an Office plug-in that can
  be used to bring data from BI reports into Excel,
  Word or PowerPoint
• Data can be pulled from multiple projects, even
  Actions and Platform
• Grids can be pivoted in many different ways
• Workbooks can be set to refresh when opened
• These slides detail
• Installing the add-in
• Configuring the add-in
• Testing and ensuring it works
• Design considerations
• Using the add-in is straightforward, but it
  requires
• Planning the layout
• Creating the base reports
• Adding additional computations or other non-BI
  data
• Setting up the refresh options

Currently, we do not have licenses for
MicroStrategy Office on Actions. Only 5 licenses
are available on Platform. If you would like to
use these features, please request a license
through the Product Managers.
3
Installing the add-in

• Access MicroStrategy Web and look for the
  Install MicroStrategy Office link at the top
• http//bi-web01.prodsv4.adchemy.colo8080/mstr/ser
  vlet/mstrWeb
• Click on the link and follow instructions

4
Configure MicroStrategy Office add-in

• The instructions that follow are for Excel 2007,
  but it should be the same in other versions
• Start Excel and click on the Add-Ins tab
• Look for the MicroStrategy item and drop-down the
  box
• Click on Options
• Click on Server on the options panel and set up
  the MicroStrategy URLs
• Web Service URL
• http//bi-web01.prodsv4.adchemy.colo8080/MicroStr
  ategyWS/services/MSTRWSJ
• Web URL
• http//bi-web01.prodsv4.adchemy.colo8080/mstr/ser
  vlet/mstrWeb
• Other useful options will be listed later in this
  presentation
• For now, that is all

5
Before using this add-in

• BASIC STEPS
• Plan the layout
• Have an idea of what you would like to see in the
  Excel workbook
• What is going to go in worksheets
• What will be in each worksheet
• Prepare the reports in MicroStrategy Web
• Start with the Ad-Hoc reports or other base
  reports and add the metrics
• Save the prepared reports into the My Reports
  folders
• When saving, ensure that prompt answers are saved
• Pull in the reports into your planned layout in
  Excel
• Set the report refresh options
• The following slides walk through the process
  using examples

6
Preparing the reports in MicroStrategy Web

• Log on to MicroStrategy Web and customize the
  Ad-Hoc or other report to your liking as many
  different combinations as you need
• Save in My Reports
• For the following examples, two simple reports
  were created using the ALPS Ad-Hoc Report and
  saved in My Reports
• ALPS conversion last 7 days
• ALPS vc visits for today
• This was done in Accounts 92 (Lending Tree) and
  56 (Adaptive) so you can see how to pull data
  from multiple accounts and reports onto one Excel
  workbook
• ALWAYS SAVE THE REPORT WITH PROMPTS INTO My
  Reports AS SHOWN

Merge headers in the report before saving it
This is how the reports look like on
MicroStrategy Web
7
Pulling the reports from Excel

• In Excel, open MicroStrategy Office from the
  add-ins
• Select Platform PROD from the left
• Enter login and hit Get Projects
• Select your account and hit OK

• Double-click the ALPS conversion last 7 days
  report and it will be added to the Excel

Thats it !!! Add as many reports as you like to
the Excel from different projects, even your own
metrics
8
Important things to remember

• The add-in cannot detect changes to the cells
  so do not add your own content within the cell
  area of the MicroStrategy report
• If the number of columns is fixed, add your
  metrics to the left or right
• If the number of rows is fixed, add metrics to
  the top or bottom
• If both vary, then add your metrics to a
  different worksheet
• Not following this can be frustrating, since the
  add-in may overwrite your formatting and
  computations when new data is retrieved
• Always create the reports that you need in
  MicroStrategy Web before pulling them into Excel
• This allows you to save prompt answers so you
  will not be prompted again
• Not doing this will make the Excel workbook
  prompt you every time you open it can be very
  frustrating
• Remember to merge headers in the MicroStrategy
  report before adding to Excel

Lets look at other important operations that are
useful when using MicroStrategy within Excel
9
Listing the reports that have been added to Excel

• You can see the list of reports added to your
  workbook by going to the Reports menu in the
  add-in
• Right click on the report and you can
• Refresh report (or the entire workbook)
• Re-prompt report (or the entire workbook)
• If you re-prompt a report from Excel, you cannot
  save prompt answers
• Always adjust prompt answers from MicroStrategy
  Web

10
Editing properties of the report added to Excel

• Right click on the report to expose Properties
• You can change where the report is added to on
  the workbook
• Change the display from graph to grid etc.
• There are tons of other controls

11
Setting refresh options

• Adjust refresh settings on the options panel (not
  report properties)
• You can set your Excel to refresh data
  automatically when opened

12
Set Prompt Options

• It is important to turn on the Always use
  default answer option in the Prompt options
  panel otherwise you will get annoying prompts!!!

13
Adding your own metrics to the workbook

• The workbook below shows data from two accounts
  added to the same worksheet with decorations and
  custom computations
• Remember to add your own metrics outside of the
  MicroStrategy grid area
• Otherwise, it might get overwritten when workbook
  is refreshed
• This is because the add-in does not check the
  cell space before refreshing data

14
You can add reports from multiple projects

• Data from Actions and Platform accounts can be
  displayed together
• You can connect to a different project by hitting
  this icon
• It is in the MicroStrategy Office menu of the
  add-in

15
Adding data to multiple worksheets

• If your report on MicroStrategy Web had an
  attribute in the Page-by section, then you can
  choose to add data from each value to a separate
  worksheet
• For example, the VC is moved to Page-By in our
  second report (ALPS vc visits for today)
• When you add this report to Excel, you will be
  asked to choose the page-by sections (different
  VCs in this case) that you want included you
  can select multiple values
• Each set will go into a separate worksheet

16
Pivoting the data

• You can customize how data is pivoted in the
  workbook
• Set the report display type to Pivot Table in
  the Report properties panel
• You can access it from the add-in menu -gt
  Reports -gt right click on the report
• You can also setup defaults in the options panel

17
Pivot preferences

• Pivot preferences are driven by GUI you can set
  things up to look the way you want
• Once the report type is set, go to the report and
  click on it that should open up the pivot
  properties sidebar

18
Example of pivot grid

• There are many ways to arrange the data
• It also provides auto-filter like drop-downs so
  you can control what is displayed

19
Summary

• The MicroStrategy add-in is very powerful and can
  pull in any data from BI into Office products
• While it is easy to use, it is also static in
  the sense that none of the MicroStrategy
  platforms powerful OLAP capabilities can be used
  such as drill down, etc.
• Always plan your layout ahead of time since the
  add-in might overlay custom metrics if the number
  of rows or columns changes during refresh
• Always prepare the reports that will go into the
  workbook from MicroStrategy Web so the prompts
  can be answered and saved
• It will be frustrating if you dont do that since
  you will be prompted every time you open the
  workbook
• Alternately, you can ask the MicroStrategy admin
  to Allow Personal Answers in the prompts but
  this still does not prevent the prompting
• Be careful about the amount of data you are
  pulling
• Remember that data is moving from the database
  across network servers and the local network to
  your desktop/laptop
• This can BE VERY EXPENSIVE if your Excel pulls
  hundreds of thousands of rows
• Be judicious so you can have a responsive
  workbook !!!