PDF/A Technical White Paper

Technical White Paper BI Query Tools Author: MSys Inc BI Team Date: Feb, 2008 Copyright ©2004 Msys Inc All Rights Reserved. SAP, SAP logo, BW, and mySAP.com are trademarks or registered trademarks of SAP AG Germany, Introduction Overview Msys has been involved in BI consulting and BI products development for the past several years. We have seen many BI installations and come up with a suite of products to help our customers to save time and money and improve the functionality of BI system. Our BI Tools Msys has developed a set of products to help our customers. They are :• BW Copy tool to copy queries between systems without using transports BW Query documentation tool – to document BW queries with technical name in html format or pdf format BW Query Objects Cleanup tool – Tool to improve performance and delete unused objects BW Query Clean up tool – Delete unused BW Queries in batch mode. Multi Provider Validation tool – to validate multi provider for manual errors and to decrease the QA time BW Master Data Reporting – Tool to report master data (slowly changing dimension) without writing a single line of code. BW Query Migration tool – To migrate BW 3.x queries to BW 70 version in batch mode. • • • • • • BW Copy Tool Copy Query between systems In the complex world of BW, users create many queries on the production system. Users ask the IT to make production versions of queries by recreating them on the development system and move it to production. Till now this was done manually. Our tool will allow you to export the queries as flat files and import in to development system. Take Minutes instead of Days! Using our tool, you can import query definition from production to development in matter of minutes instead of days thus saving you time and money and avoid any manual errors. Features No Need to buy new software or hardware to use our tool. Copies all query objects like variables, calculated key figures, restricted key figures, selections, formula, conditions, exceptions and query properties. Very simple to use. The installation takes only about 20 minutes Compatible from BW version 3.x to the latest 7x (2004s) Many fortune customers have been using our tools for the last several years. Very matured and stable product. Excellent support from our company on our tools Very cost effective. Pays for itself! When you manually re-create queries on the development system to replicate what you have on production system it takes hours if not days. On an average it takes about 4 hours to recreate a query manually. This tool will allow you to copy any number of queries between system thus saving you time and money. Most of our customers have found that the tool pays itself over a period of 6 months! BW Copy Tool Query Reporting Document your queries with technical names! When BW team wants to debug a query or discuss the query design with other users the only thing they have is screen shots of the query from the query designer. Our tool gives you a detailed report on the query and the query objects Features Report the query definition in html format or pdf format Detailed report on query properties and properties of each objects within the query Report includes all technical names. For example if you have a calculated key figure using a restricted key figure, the report gives the technical name of the restricted key figure within the definition of the calculated key figure. Easy to customize. No Need to buy new software or hardware to use our tool. Very simple to use. Compatible from BW version 3.x to the latest 7x (2004s) Query Objects Cleanup Clean up your system and improve performance In a typical BW installation, users create many reusable objects and many are orphaned or unused when the queries are deleted. This keeps adding up and takes storage and slows down the system. This tool will report all unused objects in a easy to read format helping the BW team to identify the unused objects and later delete them. Quick Report Using our tool, you can get the unused objects list in minutes instead of doing this manually. Features Identifies objects like variables, structures, calculated key figures and restricted key figures No Need to buy new software or hardware to use our tool. Very simple to use. Compatible from BW version 3.x to the latest 7x (2004s) House cleaning! Use our tool to schedule it monthly to identify unused objects and clean up. Query Cleanup Tool Clean up your Unused Queries and improve performance In a typical BW installation, users create many ad-hoc queries on the production system. Most of the queries are not used. Over a period of time the number of unused queries build up. The only option the customers now have is to use the statistics cube and delete the query manually one at a time. Bulk Delete, more options Using our tool, you can get the list of unused queries with many filtering options like number of days unused, by user, by provider, by name etc. Users have the option to “Select All” and delete it in batch mode. Features Identifies unused queries by many filter/options No Need to buy new software or hardware to use our tool. Delete multiple queries with a single click. Very simple to use. Compatible from BW version 3.x to the latest 7x (2004s) Multi Provider Validation Tool Cut Down your QA time, improve reliability! Most of the BI installations use multi providers for reporting. Developers have to go through the process of adding each provider to the multi provider and finish the mapping manually. Since mapping is a manual process many developers leave some objects unmapped or wrongly mapped. Most of the time the problem is only identified by the users on production system and the BI team go through the process of fixing the multi provider and transporting it again. Fix problems before they happen and cut your time to QA! Using our tool, developers and the QA team can find problems before it is transported to production. The tool lists all objects which are not mapped (by one or more providers) and also incorrect mappings (objects in multi provider mapped by 2 or more different objects in the underlying providers). Features Identifies objects that are not mapped and objects that are mapped incorrectly No Need to buy new software or hardware to use our tool. Very simple to use. Compatible from BW version 3.x to the latest 7x (2004s) Master Data Reporting made easy! Report on slowly changing dimensions In many BW installations business users want change in time dependent attributes over a period of time. A typical business request we come across often is “Give me the change in price for product ABC for the last 2 years”. The usual answer is to refer these requests to the R/3 team even though the data is there in BW. The standard BW queries can not display change in values over a period of time gracefully and effectively. Our tool written in ABAP and implemented as a virtual info provider has the best solution so users can report it using BeX or Web. Features Uses Virtual Info Provider to read master data – no duplication of data Users can use BeX or web application designer to report. Easy to implement and use No Need to buy new software or hardware to use our tool. Very simple to use. Compatible from BI version 3.5 to the latest 7x (2004s) Query Migration Tool Migrate your queries from BW 3.x to BI 7x This tool migrates BW 3.x queries in batch mode (Multiple queries). Instead of doing the upgrade manually one at a time, this can do it in bulk mode. Features Native to BI installations Easy to implement and use Very simple to use. Compatible from BI version 3.5 to the latest 7x (2004s) Testimonials Saves money and time! Our tools have been used many customers around the world including fortune customers in the USA, South America, Europe, Asia and the Middle East. References available on request. All our tools are matured used by many customers worldwide for the last several years. They are also very cost effective and you get excellent support from us! Customer Quote “The MSYS query utilities save us 10 to 20 hours a week in moving queries across our BW environment. Within week it had more than paid for itself in query development time and reduced errors!” Steve Lewis, Director, BI Analytics, McKesson Pharmaceutical, San Francisco California, USA. Improve Performance By using our clean up utility many customers have seen performance improvement not only in query run time but also during the query design time. Give business users what they want Many business users want historical time dependent attribute information For example users want the changes of price for a certain product or group of products. Instead of writing any custom query or custom tool use our tool to get what the business users want! Case Study The following case studies are from many of our customers and shows how our tool is used Copy Tool Problem: In this Fortune 100 customer site users create many queries on production on a daily basis. Once users are satisfied with the query, the ask the IT department to recreate the query on development with standard naming conventions and move it back to production with a different approved name. Solution: Use the Msys BW Copy query tool. Before that the BW support team had at least one person working full time or most of the time recreating queries on the development system by using 2 windows and doing it manually. Many times the query was not exact replica of the production query. For example the properties of query/key figures were missed. This meant going to QA and coming back to development to correct errors. By using BW query copy tool from Msys the IT was able to import queries within minutes thus reducing errors, saving time and money. No of queries copied per week 10-15 Time to take to do the copy using our tool 4 minutes average Errors in QA No. Query matches exactly as in productive system Yes. Once the query is copied to development use the standard transport mechanism and company policies to move the query back to production. Excellent. Users now need not wait for days or weeks to get their queries done on production Follow standard procedure when moving to production? Response from business users Clean up Tool Problem: In this Fortune 100 customer site users create many queries and many query objects like variables, restricted key figures, calculated key figures and structures on production on a daily basis. These objects are used in test or temporary queries and those queries get deleted thus orphaning the objects they create. Solution: Use the Msys BW query objects clean up tool Before this tool was implemented BW developers occasionally right clicks on an object (like restricted key figures) and select “Where Used List”. If he/she finds no one is using the object it gets deleted. By using our tool the BW team was able to get a list of unused objects in a clear format and select which one should be deleted. This greatly improved the performance of the query run time and design time. No of unused objects The initial report was about 40 pages. Now this tool is scheduled on a monthly basis to do house cleaning Better performance after deleting all un used objects Better performance when using the query designer (for example when expanding variables or creating new variables) and other reusable objects Query run time Query design time Query Delete Tool Problem: In this distributing company, many power users create many ad-hoc queries. Over a period of time the number of queries increase and overall only about 20% of the total queries are used actively. Old Way The BI Support team runs a “Unused query” report every 2 months (with all the query names that are not used for the last 6 months). The list is sent to end users confirming the deletion. Upon confirmation the deletion process takes place (one query at a time) which takes 2 – 3 days. Our Solution: The customer installed our query delete tool. After getting confirmation from users the queries are deleted with a single click – takes about 15 minutes “Old” way Delete queries one at a time takes 2-3 days Delete multiple queries with a single click. Takes about 10-15 minutes Our solution Master Data Reporting Tool Problem: This manufacturer has thousands of products. The price of the products over a period of time. Users want to track change in price for one or more products. Current approach was to write ABAP query on R/3. One alternative method proposed by BI team was to create a ODS containing changes to material prices. Our Solution The customer installed our Master data reporting tool. The custom virtual info provider reads data from 0customer and gives the users reports using Bex. The users are able to get reports for price changes without going to R/3 and writing custom ABAP code. U Problem Old Solution Alternate solution considered by BI Team Get users change in price report Create reports in ABAP on R/3 Create an ODS containing time dependent master data Implement our Master Data reporting tool. Self serving. Users create reports using BeX Final Solution Multi Provider Validation tool Problem: In this big distributor site, developers add new providers for each new fiscal year or if a new region is added. The new providers are in turn added to the multi providers. Using the change management report QA found that 43% of the time the multi providers are either mapped incorrectly or not mapped. The problem is found 90% of the time in production and only less than 10% is found before multi provider is moved to production. Solution: After implementing our tool developers found issues with the new Fiscal Year 2009 changes and fixed it. QA also verified the multi providers using our tool. All the multi providers that were moved to production did not have any problems and the users are happy with the reliability of data. Problem Manual process of mapping multi provider objects has too many errors Report on un mapped or incorrect mappings. Fix errors before moving to production Our Solution Contact Information If you want more details on our products and/or services please feel free to contact us Email Phone Web bw@msysinc.com 919-454-5604 www.msysinc.com Copyright ©2004 Msys IncAll Rights Reserved Other names and brands may be claimed as the property of others. Information regarding third party products is provided solely for educational purposes. MSys Inc is not responsible for the performance or support of third party products and does not make any representations or warranties whatsoever regarding quality, reliability, functionality, or compatibility of these devices or products.