1 / 24

SAP BusinessObjects Tips and Tricks

SAP BusinessObjects Tips and Tricks. Michael Welter PS Consultant Claraview. Who am I?. Michael Welter: PS Consultant Company: Claraview, a division of Teradata Joined Claraview in March, 2011 Prior to that I worked for Westbay Solutions Group/Idhasoft for 8 ½ years

tracen
Download Presentation

SAP BusinessObjects Tips and Tricks

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SAP BusinessObjectsTips and Tricks Michael Welter PS Consultant Claraview

  2. Who am I? • Michael Welter: PS Consultant • Company: Claraview, a division of Teradata • Joined Claraview in March, 2011 • Prior to that I worked for Westbay Solutions Group/Idhasoft for 8 ½ years • Over 11 years experience with BusinessObjects • BusinessObjects Certified Professional • BusinessObjects Enterprise • Web Intelligence Can I help with your BO issues?

  3. Agenda • Universe Date Prompt: Default to Yesterday • Web Intelligence Merge Dimension Tips • Query Performance Tuning • Query Stripping • Schedule Purging • Index Awareness • Pushing Transformations • Security Tips But first . . .

  4. Universe Date Prompt: Default to Yesterday • This problem has plagued humanity since the beginning of time (Or at least the beginning of BusinessObjects) • I need to create date prompt in the universe, and I need it to default to today. • I’ve seen some convoluted solutions, but haven’t been impressed. • Of course, universe prompts are based around the @prompt function, so let’s review the syntax.

  5. Universe Date Prompt: Default to Yesterday @Prompt(‘message’,'type’,[lov],Mono|Multi, free|constrained|primary_key,persistent|not_persistent,[default_values]) • Message: The text of the prompt • Type: A (Alphanumeric), D (Date), N (Numeric) • LOV: List of Values (Hard coded, or from an object) • Mono|Multi: Allow 1 value, or multiple • Free|Constrained|Primary_Key: Enter a value, or select only from the LOV. Optional: Use Index Awareness • Persistent|Not_persistent: Keep last value selected • Default_Values: Enter a default value

  6. Universe Date Prompt: Default to Yesterday Example @Prompt('Enter start date','D',,mono,free,not_persistent) In this example, the user may type in a date, or select a date from the calendar.

  7. Universe Date Prompt: Default to Yesterday • Final Solution, based on DB2 database: CALENDAR_TABLE.CAL_DATE = (CASE WHEN @Prompt(‘Enter date or Today’,'A’,,mono,free,not_persistent,{‘Today’})=’Today’ THEN CURRENT_DATE ELSE @Prompt(‘Enter date or Today’,'A’,,mono,free,not_persistent,{‘Today’}) END) • For Oracle, replace CURRENT_DATE with TRUNC(SYSDATE) • For SQL Server, replace CURRENT_DATE with GetDate()

  8. Web Intelligence Merge Dimension Tips • Merging Dimensions continues to be the topic of much trouble, based on topics I see on BOB. • Tip 1: Adding “Incompatible” dimensions to the block • Convert Dimension to a Detail using a variable • Formula: =[Dimension] • Detail must be a detail of a Merged dimension • Detail object can be added to the block • Tip 2: Auto-Merge dimensions only works within a universe • Only between the same object used in multiple queries • Not based on object name

  9. Web Intelligence Merge Dimension Tips • Tip 3: Values displayed depend on which object is used • Query 1 object displays values from Query 1 • Query 2 object displays values from Query 2 • Merged dimension displays all values from all queries • Tip 4: “Extend merged dimension values” has a similar effect of using the merged dimension.

  10. Web Intelligence Merge Dimension Tips Rules of Merging Dimensions: • Only dimensions defined in the universe can be merged • Objects must have the same datatype • Any number of queries can be merged • Any number of dimensions can be merged • Values are cases sensitive • Values that do not match exactly will be seen as different values

  11. Query Performance Tuning Query Stripping • As reports get reused, they become cluttered with unused objects and queries • This can cause extra work by the database • Which can cause the refresh to take longer • Most important items to remove: • Unused queries • Unused measures Why?

  12. Query Performance Tuning Query Stripping • The good news: XI 3.1 SP3 has automatic query stripping • When enabled, it automatically removed objects and queries from the SQL that don’t contribute to the report • The bad news: Automatic query stripping only works with OLAP data sources • The good news: BI 4.0 will have automatic query stripping for all data sources • Until then . . . Do it manually. 

  13. Query Performance Tuning Schedule Purging • This should seem obvious but . . . • Remove scheduled jobs that are no longer needed • They are running queries unnecessarily • Use Auditor to capture usage stats • Also, take a look at Publications Please don’t burden the database with extra queries.

  14. Query Performance Tuning Index Awareness • Can be used to speed up query performance • Leverages indexed columns in a database • Can remove joins from the query • Can remove tables from the query • How does it work?

  15. Query Performance Tuning Index Awareness • Without Index Awareness: • With Index Awareness:

  16. Query Performance Tuning Index Awareness • When to use Index Awareness: • OLTP based universes • Snowflake schemas • Use with objects that have LOVs Note: If users don’t select from the LOV, the index will not be used!

  17. Query Performance Tuning Pushing Transformations • Do your universes have complex SQL Transformations? • Example: 'FY' || substr(trim(( FISCAL_CALENDAR.FISCAL_YEAR*100+FISCAL_CALENDAR.FISCAL_QUARTER)),3,2) || 'Q' || substr(trim(( FISCAL_CALENDAR.FISCAL_YEAR*100+FISCAL_CALENDAR.FISCAL_QUARTER • Can this transformation be pushed down to the ETL process? • In a recent test, removing this from the query reduced query time by 14% (Your mileage may vary)

  18. Security Tips • SAP BusinessObjects XI 3.x uses an Object Oriented Security Model • Everything is an object to which rights can be granted • Universes • Folders • Documents • Users • Groups • Servers • Try not to get confused by this: In the CMC, the User Security screen of a user’s profile has nothing to do with that user’s rights.

  19. Security Tips Starting at the root: • How do you deny access by default without creating too much work? • Go into User Security for the root folder (All Folders) • Go into Advanced Settings • Set the View Objects right as follows:

  20. Security Tips Beware of Everyone! • Remember: Everyone is a member of the Everyone group • Any rights you grant, or deny, to the Everyone group, applies to you, too • As a rule, the Everyone group should have access to nothing • This means using Not Specified • Never use Denied

  21. Security Tips Dual-ing with Security • Use a Dual Security Model • Avoids duplicating security settings • Content Groups: Have rights to folders • Determines what access each group has to each folder • Tends to mirror organizational structure • Application Groups: Have rights to Applications • Determines which applications members can use • And what they can do in those applications

  22. Security Tips Back Away From The Users • Never set security for an individual user! • Set security only for groups, then add members to the groups • Never set security for an individual user! • Even if one user has special security needs, create a group for that one user • This applies to documents, too!

  23. Questions? 24 • Michael WelterPS ConsultantMichael.Welter@Claraview.comBlog: MichaelWelter.Wordpress.comFollow me on Twitter: @UniversePro • Claraview • Business Intelligence • Various Platforms

More Related