Exploring the Treasures of MicroStrategy Metadata MicroStrategy World 2015 Track 5, Session 2 January 27 at 2:15 PM – 3:15 PM Room: La Tache © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved Business Problem You are a BI Manager in charge of an array of repor3ng teams and your responsibili3es range from informa3on accuracy to solu3on performance. On a given day you could have 200-‐1000 users leveraging your team’s reports. How do you remain informed about risks to your BI environment including database changes and poor system performance to ensure you’re holding to your agreed upon Service Levels? §  How many objects does my team need to maintain across all of our projects and what are the rela3onships between those objects? §  What tools do you have available to analyze the impacts of a database issue that compromises the integrity of your reports? §  If your database administrators are proposing a change to a database table or governance rules, what kind of impact will that have on your user community or repor3ng environment? §  What is the risk exposure of changing the calcula3on of a metric? §  How many of your users are experiencing errors in the reports they’re trying to run? © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 2 MicroStrategy Solution §  Thankfully MicroStrategy is your BI tool of choice and you have Enterprise Manager and Command Manager at your ﬁnger3ps. §  This session will walk you through a process that leverages these tools to inventory and link the following key objects by Project: §  Tables ( ) §  AVributes ( ) §  Facts ( ) §  Metrics ( ) §  Reports ( ) §  Documents ( ) §  Not only does the process inventory the objects above, it also combines Report/ Document performance from Enterprise Manager to analyze objects your end users rely upon. © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 3 Live Demonstration APPLYING TODAY’S SESSION © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 4 Let’s grab some Metadata! What steps are taken to pull this information together? THE OBJECT CRAWLER © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 5 Object Types and What is Inventoried §  Tables §  Sourcing: MicroStrategy tables and how they are linked to database tables. §  Branches: AVributes and Facts as mapped to the table. §  AVributes §  Sourcing: Outlines the Form, Expression, Lookup Table, and Source Tables of the aVribute. §  Branches: AVribute Branches (Children) of the AVribute. §  Facts §  Sourcing: Expression, Source Table and whether the source table is the mapped table. §  Branches: None © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 6 Object Types and What is Inventoried (cont.) §  Metrics §  Sourcing: Formula/Expression and Condi3onality of the Metric. §  Branches: AVributes, Facts, Metrics, and AVribute Dims – or Levels of the metric. §  Reports §  Sourcing: No addi3onal sourcing is performed. §  Branches: AVributes and Metrics used to create the report. §  Documents §  Sourcing: No addi3onal sourcing is performed. §  Branches: Report(s) used to create the document. © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 7 Crawler Sequencing © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 8 Inventorying Objects – Crawler Scripts NOTE: If Enterprise Manager is not available, below are the 3 Command Manager scripts needed to list & link all objects. If EM is available, you will only need to run script 3. §  Each object (AVribute, Fact, Table, etc.) has an array of Command Manager scripts available to execute but only 3 main scripts are needed to complete the crawls. 1.  LIST ALL [Object]S FOR PROJECT “[Object Project]”; §  This will list out all aVributes in a given Project. Take this lis3ng and dynamically populate a script for step 2. 2.  LIST ID FOR [Object] “[Object Name]” IN “[Object\Path]” FOR PROJECT “[Object Project]”; §  This will provide the GUID of each object. The GUID is a unique iden3ﬁer for an object within a project. It will be important to merge this back with your AVribute List for iden3ﬁca3on & linkage with addi3onal command manager scripts. The GUIDs will populate based on the line in the script being executed. You can merge the Lis3ng & GUID ﬁles together by row to get the ﬁnal linked objects. 3.  LIST ALL PROPERTIES FOR [Object] “[Object Name]” IN “[Object\Path]” FOR PROJECT “[Object Project]”; §  Each Object Property script will return diﬀerent property informa3on for the object so be sure to analyze the XML ﬁle carefully and extract out the informa3on needed to trace the lineage. © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 9 Object Inventory Options §  Path 1 -‐> Command Manager Only §  This method contains addi3onal ETL steps than Path 2. §  Typically used in Development or UAT environments where Enterprise Manager is not available. §  Works well for documen3ng a project prior to deployment or conﬁrming the solu3on meets requirements. §  Great tool to use if database changes are required during development and you need to understand which development project(s) are impacted. §  Path 2 -‐> Enterprise Manager & Command Manager Preferred §  Cleaner inventory lineage process §  Using a combina3on of both Enterprise Manager & Command Manager, you are able to eliminate the ﬁrst 2 scripts in Path 1 where the LIST ALL objects and object IDs are executed as this is already housed in the appropriate Enterprise Manager AVribute. §  ExcepGon: Table Inventory – you will also need the Warehouse Catalog Table for each Logical Table in MicroStrategy to link back to the source table names, so the ﬁrst 2 scripts are needed for tables only. © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 10 Path 1 -> Command Manager Only © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 11 Path 2 -> Enterprise Manager & Command Manager © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 12 Possible Data Model © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 13 Conclusion §  MicroStrategy’s Enterprise Manager and Command Manager provide the ability to trace informa3on from database table to document. §  BI Teams can leverage the informa3on collected from these tools and combine them to create an extensive metadata inventory tool. §  Leveraging a process like the Object Inventory, BI Managers, Architects, and Analysts can be empowered with an extensive bounty of object lineage that can be used to inspect, maintain, and alert users who rely on informa3on in their reports. §  Combining the informa3on outlined here is only the ﬁrst step towards a truly intelligent and integrated solu3on. §  Connec3ng this informa3on to other sources (i.e. Database Sta3s3cs, Organiza3onal Structure, User Characteris3cs, etc.) will bring knowledge of your BI environment to a whole new level. © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 14 QUESTIONS? © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 15 APPENDIX © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 16 How to use the information? §  How many objects does my team need to maintain across all of our projects? © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 17 How to use the information? §  How are objects related and how complex are those inter-‐dependent rela3onships within a given project? © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 18 How to use the information? §  What tools do you have available to analyze the impacts of a database issue that compromises the integrity of your reports? §  If your database administrators are proposing a change to a database table or governance rules, what kind of impact will that have on your user community or repor3ng environment? © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 19 How to use the information? §  What is the risk exposure of changing the calcula3on of a metric? © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 20 How to use the information? §  To what level are your reports/documents are experiencing errors? §  To what level are users experiencing acceptable report/document performance? © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 21 Example Command Manager Scripts © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 22 Example Enterprise Manager Reports © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 23 Example Enterprise Manager Reports © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 24 Example Enterprise Manager Reports © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 25 Example Enterprise Manager Reports © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 26 Example Enterprise Manager Reports © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 27 Example Enterprise Manager Reports © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 28 Sources §  Images copied from Google Images: §  Spider: sherv.net §  Treasure Chests Opening: rfclipart.com §  Large Treasure Chest: car-‐memes.com §  Coins Around Treasure Chest: farmville2.net © 2014-2015 Wells Fargo Bank, N.A. All Rights Reserved 29