Getting Started with the Information Design Tool

What kind of semantic layer tool would SAP create if they could start over?

Today, I’m presenting Delivering Secure and Personalized Business Intelligence at the ASUG SAP BusinessObjects User Conference (SBOUC) at the Walt Disney World Swan and Dolphin Resort in Orlando, Florida (see related article, Delivering Secure and Personalized Business Intelligence). There’s a whole Semantic Layer track at the conference, including several sessions led by Pierpaolo Vezzosi, SAP’s product owner for the semantic layer tools. The presentations at the conference are split fairly evenly between the legacy Universe Design Tool (UDT), formerly known as Universe Designer or just Designer, and the new Information Design Tool (IDT) that debuts with the SAP BusinessObjects Business Intelligence 4.0 platform (BI4). I’m focusing on the latter, showing how to do some common data security techniques in a new and unfamiliar tool.

Visually, the Information Design Tool is a radical departure from the legacy Universe Design Tool. Here’s a typical reaction from Mark Bradbourne:

I’m so lost with [the Information Design Tool] at the moment it’s scary… It’s like I never touched Designer… Much less been using it for the last 10 years.

Mark captures exactly how I felt the first time I launched the Information Design Tool. The semantic layer team at SAP pushed aside the old Universe Design Tool, grabbed a clean sheet of paper, and asked themselves “what kind of semantic layer tool would we create if we could start over”. The Information Design Tool is a thoughtful redesign and my reaction has been very positive as I figure it out. Overall, I’m much happier relearning the Information Design Tool than I am Web Intelligence 4.0, which has also been significantly redesigned.

Thankfully, I’ve had several months using the BI 4.0 ramp-up to fumble my way through. I’d like to share three tips with you that can shorten your own personal IDT ramp-up with hopefully less fumbling.

Download free documentation on the SAP Help Portal

I bought a Kindle last Christmas, primarily because it could hold Adobe PDF documents, not because I could buy paperless Kindle books (see related article, SAP BusinessObjects Business Intelligence 4.0 for Kindle). At the time, I was traveling a lot for work and figured that I could read BI 4.0 documentation on the plane.

SAP documentation doesn’t have any pictures, which is a rant for another time. But in this case, it was actually helpful. By not having any pictures about how the Information Design Tool looked, I was able to focus on what it did. And I was pleasantly surprised to learn that the Information Design Tool builds a universe. Seriously. There are still tables, connections, objects, loops, aliases, and contexts. Plus a few new features that users have been requesting for several years. In many cases, we’re performing the same activities, but using new workflows in a new Eclipse-based tool.

Even if you won’t be adopting BI4 right away, you can get this manual now and begin reading at a leisurely pace.

Check out the excellent tutorials on the SAP Community Network

The Quick Reference Getting Around Information Design Tool provides a cross-reference between work flows in the old Universe Design Tool and the new Information Design Tool.

SAP has created free(!) SAP BusinessObjects Business Intelligence Platform 4.x Product Tutorials, including a whole set for the Information Design Tool. You can see short videos of the Information Design Tool in action, elegantly organized by task.

Both of these resources were personally helpful to me as I created my conference presentation. Nice work, SAP!

Buy the Book, Before the Movie Comes Out

We included a single chapter on the Information Design Tool in SAP BusinessObjects Web Intelligence: The Comprehensive Guide. But our focus was providing the best resource for Web Intelligence users, not universe developers. Cindy Howson and Elizabeth Newbould provide a much more comprehensive approach, with 11 chapters of universe design in SAP BusinessObjects 4.0: The Complete Reference (read my book review). UPDATE: And in 2014, SAP Press released a 724-page guide devoted exclusively to the Information Design Tool (see my book review of Universe Design with SAP BusinessObjects BI: The Comprehensive Guide).

Get Vendor Training

I’m always surprised how many people never received official training for the old Universe Design Tool (see related article, Avoiding the Big Mess). I can’t comment on training materials for the new Information Design Tool because they are behind schedule and not yet released. But they are coming. Unlike the Universe Design Tool training which is divided into two courses, there is only a single 5-day course for the Information Design Tool. I’m biased toward having a real instructor that can go “off-road” with questions.

Last week, I received 35 lbs of BI4 training manuals for topics such as administration, Web Intelligence, and Dashboards (formerly Xcelsius). I’ll write a review of the Information Design Tool class as soon as I can.

Final Thoughts

I hope that these tips, along with the presentation I’m giving today at the ASUG SAP BusinessObjects User Conference, will help you get started. There’s a bright future ahead for the universe.

Disclosure of Material Connection: Some of the links in the post above are “affiliate links.” This means if you click on the link and purchase the item, I will receive an affiliate commission. Regardless, I only recommend products or services I use personally and believe will add value to my readers. I am disclosing this in accordance with the Federal Trade Commission’s 16 CFR, Part 255: “Guides Concerning the Use of Endorsements and Testimonials in Advertising.”

Identifying SAP BusinessObjects queries using END_SQL

A useful trick to help SAP BusinessObjects universe designers and database administrators identify the source of queries.

NOTE: I originally wrote this article about XI R2 in 2008 but I have since updated it to include information about XI 3.x, BI 4.0, BI 4.1, and BI 4.2.

Here’s a useful trick that can help both SAP BusinessObjects universe designers and database administrators find the true origin of queries. Using this technique, we can identify which report, universe and user is generating a potentially problematic SQL statement and take corrective action. The END_SQL universe parameter is typically used to allow universe designers to append additional SQL such as database hints to SQL statements. But it can also be used to add a seemingly benign SQL comment. Because this comment can use @Variable functions from the universe, its contents become dynamic.

For classic universes built with the Universe Design Tool (formerly known as Universe Designer or just Designer), set universe parameters by choosing File -> Parameters from the menu or click the Parameters button on the toolbar. Next, navigate to the Parameter tab.

END_SQL parameter in Universe Design Tool

If you are using SAP BusinessObjects Enterprise XI R2, the semantic layer can dynamically identify the user name and document name using the @Variable function.

/* Hard coded Universe Name - @Variable('BOUSER') - @Variable('DOCNAME')*/

SAP BusinessObjects Enterprise XI 3.0 introduced several new @Variables, so you can get a bit fancier (see related article, Using @Variable Functions in the Universe) and use an @Variable for the universe name. Now the entire END_SQL expression is dynamic.

/* @Variable('UNVNAME') - @Variable('BOUSER') - @Variable('DOCNAME') */

The Information Design Tool introduced with SAP BusinessObjects Business Intelligence 4.0 (BI4) also supports universe parameters like END_SQL. To set, click on the “Properties” tab of the Data Foundation Layer (*.dfx file) and click the “Parameters” button.

Information Design Tool END_SQL

Keep in mind that certain database platforms such as Teradata strip out comments, negating the value of this trick and preventing a DBA from seeing the information we wish to share. If your organization uses Teradata, check out this helpful article from Dave Rathbun or this SAP Community Wiki from Jacqueline Rahn about ConnectInit and BEGIN_SQL.

For more information about END_SQL, check out this thread on the BusinessObjects Board (BOB).

Converting eFashion from UNV to UNX

Taking the Information Design Tool for a test drive.

UPDATE: This article was written with SAP BusinessObjects BI 4.0 SP2, the General Availability (GA) release. Raphael Branger reports that SAP BusinessObjects Feature Pack 3 includes fixes to help migrate Microsoft Access universes like eFashion to the new UNX format (see Raphael Branger’s article, BO 4.0 FP3: get eFashion and other MS Access datasources working).

One of the first things I’ve tried to do with SAP BusinessObjects Business Intelligence 4.0 is convert the sample eFashion universe (actually, there are two sample eFashion universes, but I digress) from a traditional UNV universe to a new UNX universe using the Information Design Tool. The process for converting universes in the Information Design Tool is simple and straightforward (see official product tutorials on SCN) but the results, at least with Microsoft Access universes like eFashion, are not.

Here is what a query on the original eFashion.unv looks like for Year, State, Store name, and Revenue. I’m using SAP BusinessObjects Business Intelligence 4.0 SP02 Patch 4 (the most current release).

SELECT
Calendar_year_lookup.Yr,
Outlet_Lookup.State,
Outlet_Lookup.Shop_name,
sum(Shop_facts.Amount_sold)
FROM
Calendar_year_lookup,
Outlet_Lookup,
Shop_facts
WHERE
( Outlet_Lookup.Shop_id=Shop_facts.Shop_id )
AND
( Shop_facts.Week_id=Calendar_year_lookup.Week_id )
GROUP BY
Calendar_year_lookup.Yr,
Outlet_Lookup.State,
Outlet_Lookup.Shop_name

And here’s the SQL generated by the converted eFashion.UNX.

SELECT
Calendar_year_lookup.Yr,
Outlet_Lookup.State, Outlet_Lookup.
Shop_name,
sum(Shop_facts.Amount_sold)
FROM Calendar_year_lookup,
Outlet_Lookup,
Shop_facts,
{ oj Outlet_Lookup LEFT OUTER JOIN Shop_facts ON Outlet_Lookup.Shop_id=Shop_facts.Shop_id },
{ oj Shop_facts LEFT OUTER JOIN Calendar_year_lookup ON Shop_facts.Week_id=Calendar_year_lookup.Week_id }
GROUP BY Calendar_year_lookup.Yr, Outlet_Lookup.State, Outlet_Lookup.Shop_name

The outer joins are ambiguous, so the SQL statement cannot execute. Plus, the ANSI92 parameter is turned off by default, so I’m confused how the joins ended up in the FROM clause.

I can only speculate what the root cause is (bad settings in the PRN files?), but I’ve posted the question in the SCN Forums (click here to access). In the meantime, I’ve used the free Microsoft SQL Server Migration Assistant (SSMA) (click here to download) to move eFashion from Microsoft Access to Microsoft SQL Server.  My immediate concern is to get some party pants demos put together for my upcoming presentation at the SAP BusinessObjects User Conference.

So far, my BI 4.0 experience has been in the lab.  But Michael Welter indicates that he’s had success converting real production universes.

And let’s face it, most production universes don’t use Microsoft Access. Especially the production universes of ramp up customers.  But it seemed to me that my little eFashion test was the “Hello, world” of the new Information Design Tool.

Thanks to Pierre LeRoux at SAP for passing along my dilemma.

Although eFashion.unx has difficulties generating SQL, there are some good experiences with both the Upgrade Management Tool and the Information Design Tool in their handling of universe restriction sets. I will share that news in an upcoming blog post and next month at the ASUG SAP BusinessObjects User Conference.

I’ll keep everyone up-to-date regarding what becomes of my eFashion SQL question in the SCN Forum (see Raphael Branger’s article, BO 4.0 FP3: get eFashion and other MS Access datasources working).

Resources

Delivering Personalized and Secure Business Intelligence

Security profiles in the new SAP BusinessObjects Business Intelligence 4.0 Information Design Tool.

Walt Disney World Dolphin ResortI’ve been selected as a breakout speaker for the 2011 ASUG SAP BusinessObjects User Conference, October 9-12, 2011 at the Swan and Dolphin Resort at Walt Disney World in sunny Orlando, Florida. This year I’ll be speaking about securing the new UNX universes created by the SAP BI 4.0 Information Design Tool. Here is the abstract for my session.

Do you need to tailor semantic layer security to specific users or groups within your organization? Attend this session to learn about security profiles in the new Information Design Tool in SAP BusinessObjects Business Intelligence 4.0. Understand how applied restrictions can control objects, rows, query types, and connections. See live demonstrations on how to use each type of restriction and the effect it has on end users’ interactive experience.

Key Learning Points:

  • Find out how to secure and personalize your users’ ad-hoc query experience.
  • Learn how to secure semantic layer in the information design tool.
  • Gain confidence in the security model using built-in testing tools.

My session occurs on Tuesday, October 11, 2011 from 4:00 PM – 5:00 PM. I hope to see you there!

UPDATE: The slide deck is available on my presentations page.

Learn more about the 2011 ASUG SAP BusinessObjects User Conference

Learn more about the Walt Disney World Swan and Dolphin Resort

Learn more about Walt Disney World

The Universe, Yours to Discover

Checking out the universe from Atlanta, GA.

International Year of Astronomy 2009 Logo

I’ve recently been traveling to a client in North Carolina, requiring me to switch planes at the Hartsville-Jackson Atlanta International Airport (ATL). The layovers have been pretty casual, so I’ve been walking between concourses instead of taking the subway. There’s currently a wonderful NASA exhibit of photographs from the Hubble Space Telescope in the tunnel that connects the concourses. According to the exhibit, the United Nations has declared 2009 as the International Year of Astronomy. The slogan of the International Year of Astronomy is “The Universe, Yours to Discover”. I did actually use some Hubble photographs in my GBN/ASUG presentation, “Universe Design: Evolution, Intelligent Design, Or Just A Big Mess?” (see related article, Avoiding the Big Mess). But I would have probably made a bigger deal of it if I knew I was collaborating with the United Nations.

If you’re in the Atlanta airport and have the time, it’s certainly worth the time to see how vast and glorious the universe is.

To find some cool Hubble photos, check out the official NASA site or HubbleSite.org.

SAP BusinessObjects Universe Designer Wish List

Will my wishes become reality in the SAP BusinessObjects Business Intelligence 4.0 Information Design Tool?

I’ve been using Designer since my BusinessObjects career began in 2003 with version 5. Designer hasn’t changed much in that time, although derived tables and index awareness were added in version 6.5, restriction sets were improved in XI R1, and smart measures added to XI 3.x.  At the recent 2009 SAP BusinessObjects User Conference, SAP indicated that the next major release of BusinessObjects Enterprise (code named Aurora and headed our way Q4-2010) will integrate Data Federator.  We’ve been promised that future versions of Designer will assume all of the capabilities of Crystal Reports Business Views so that semantic layer can be retired in favor of the universe, and Data Federator could certainly make that possible.  And as with all other BusinessObjects products, it’s a given that adding new SAP-centric features is at the top of the priority list. But how about some desperately needed usability improvements? I humbly offer the following list of recommended enhancements.

Disclaimer: Some of my friends were able to sign an NDA and check out Designer XI 4 at the recent SAP BusinessObjects user conference.  However, I was not one of them.  So I have no idea if any of the ideas below will actually appear in the next release.  But with another year before the product is shipped, there’s still time to code some of these up… 😉

Usability Enhancements

  • A read-only field on the object properties tab that displays the class hierarchy for the object (ex. class namesub-class namesub-class nameobject name). This field should make it easy to cut and paste from Designer to other applications.
  • Ditto for the class properties
  • The ability to use the standard Windows shortcut CTRL-A to “select all” of the contents of any field in the object properties
  • A spacer object that can be inserted into large classes to indicate related items. Many developers resort to kludgy objects with hyphens to accomplish this feature.  Sometimes subclasses just aren’t an option (or requirement). In Web Intelligence, the spacer object should have a small icon to collapse/expand the objects from the spacer to the next spacer (or end of class).
  • An enhancement to the integrity checker that warns when a measure object does not have a database aggregate function in the SELECT clause. Should be a warning, not an error, because some folks (sigh) actually like measures w/o database aggregates.
  • An object comment editor, with a filter button to show only classes and/or objects that do not have comments.
  • A faster way to grab an object’s SQL than navigating to the object Properties tab, clicking on the Edit button for the List of Values (LOV) and viewing its SQL.
  • Move the Refresh Structure menu option from View to Tools. I know it’s been there for years, but “View” implies that the menu choices don’t affect the universe but only it’s display in the application. Refresh Structure most certainly alters the universe and should be classified as Tool. Add a toolbar button while you’re at it.
  • Put some space on the toolbar between the helpful List Mode button and the evil Arrange Tables button. It’s too easy to hit the latter and I generally prefer to not use it at all. And per my friend Andrew Koller, why can’t we undo the effects of the Arrange Tables button, anyway?
  • Allow user to right click in schema window and zoom in to exactly that spot (or table, if selected)
  • Allow user to right click in schema window and center the display at that spot (or table, if selected)
  • Allow paste from clipboard to text blocks in the schema view
  • Allow designers to create “groups” of tables, similar to groups of objects in Microsoft Visio.
  • In addition to placing comments on the schema itself, allow comment blocks to be tagged to tables, joins, or table groups so that comment blocks always remain associated with the appropriate object(s), regardless of how they are arranged
  • Allow designer to assign a unique color (not just Windows grey) to individual tables or groups of tables (Should these colors also be applied, maybe as an option, to object names?)
  • Allow for multiple fonts, block colors, and block outlines for text blocks in the schema view. These enhancements will facilitate better documentation
  • Variable zoom, please? Maybe a nice slider instead of the current drop-down with limited settings?
  • When I right click on a table and choose View Associated Objects, please pop up a box to tell me the table is unused. Currently, the universe pane stays stuck on its previous object, which misleads me into thinking that it belongs to the table.
  • View Associated Tables should cause the universe window to scroll, not jump. Currently too easy to lose ones place when seeing where various objects in a class come from.  The current version simply “jumps” to the correct (mostly) location and it’s easy to get lost in a larger universe.
  • If I right click on an object and choose View Associated Table and that table is on the screen, do not center on the table if the table is already on the screen but just highlight the new table. Very confusing as it alters the user’s frame of reference. Or, the screen should re-center by gently scrolling to the new position, so the designer can maintain his or her sense of direction in the universe.
  • Right click on class name and choose View Associated Table(s), similar to what’s available for objects. This option would help a developer clearly identify all tables utilized by the class.  Like my other suggestions, screen should “gently” center/resize to help designer maintain their frame of reference.  Universe window should scroll out slowly to show all tables.
  • Similar to the properties tab in Web Intelligence, add toolbar buttons to the top of the universe pane that close all classes simultaneously and open all classes simultaneously. Also, could we filter by qualification (dimention, detail, measure), data type (char, integer, date, long)?
  • Add toolbar button to filter hidden objects, as many times these are candidates for deletion.
  • The ability to find joins without defined cardinality (suggested by James Halligan)
  • A wizard to construct @prompts (suggested by James Halligan)
  • Better date handling (suggested by Josh Fletcher)
  • How about adding a tabbed interface so that unconnected portions of schema could be stored on separate tabs for easier maintenance. Or, if that’s facilitating too much poor design, how about a right click option that allows me to right click on either a bunch of objects or tables and choose to build a new universe from them? Copy objects wizard would have options to control how many tables are brought over.
  • Back to the tabbed interface… How about a separate tab for “original” tables that are aliased but not used, per best practice. And a wizard on the tools button that identifies tables that have been aliased elsewhere, replaces those tables with an alias, and recodes any dependent objects?
  • And a recycle bin tab… With a wizard that identifies unreferenced tables (not part of a valid join path) and moves them there.
  • Just like a road atlas, and instead of rulers in applications like Microsoft Word, allow designers to turn on a grid of letters and numbers. Right clicking on an object’s properties should tell me that the table is on the “map” at B6, etc. This “map” would also be helpful for those of us who have to print large universes and tape multiple sheets of paper together.
  • Regular expression support for Find and Replace
  • When Find locates an object in a large classes, the item identified by the Find/Replace function is at the bottom of the screen and partially obscured by scrollbars. Could we see the whole object, please?

And while we’re at it, a few related improvements in the other tools…

  • Improved abilities in the Web Intelligence and other query panels to get the class hierarchy, object SQL (not just query SQL) and data lineage. Although it’s a bit tech-centric for a user-centric tool, some customers put this technical information where the non-technical help text is supposed to go. Having separate containers, and client tools that access them, makes everyone happy.
  • Allow version control history for each object, without totally cluttering the interface (might be challenging). This data should also be available through Metadata Manager
  • Add reporting capability to Metadata Manager that searches all universes on a given universe connection and identifies multiple universes that point to the same underlying SQL but have different names (consistency checker). Should have option to determine if aliases are included or not.

So what would you like to see in the next version of Designer? Feel free to post a comment.

  1. Allow table groups to be named. Still thinking about what an end-user might do with such a name…

Avoiding the Big Mess

Training is a critical ingredient in SAP BusinessObjects universes.

Today is the first day of the 2009 SAP BusinessObjects User Conference in Dallas, Texas. This year, universe design and Web Intelligence, and Xcelsius are “Spotlight Topics”, meaning that they’ll each be given significant coverage at the conference. The breakout that I’m giving today is entitled “Universe Design: Evolution, Intelligent Design, or Just a Big Mess”. I’ll be describing the key tasks, common pitfalls, and best practices at each stage of the universe life cycle.

Universe construction with the Universe Design Tool (Designer) application is an important task, yet it is only a portion of the overall life cycle of a business intelligence project. I certainly do not want to minimize its importance. However, it is my experience that many business intelligence projects are doomed to fail before the Universe Design Tool application is ever launched.

One of the best practices I will describe today is getting formal training for the people in your organization who use the Universe Design Tool. Disclaimer: I am a trainer for an SAP Authorized Education Partner; however, allow me to back up what I am saying from personal experience.

My experience is more common that I would prefer. My career with BusinessObjects began in early 2003. I was an IT consultant with many years of Oracle experience but found myself on the bench at an inopportune time. The industry as a whole was struggling from the September 11, 2001 terrorist attacks, a desire for organizations to drastically curtail IT spending after large outlays on Y2K initiatives, and an overall slowdown in the U.S. economy. But as has been true at many different points in my career, somebody in the organization turned in their resignation. And that opened a door for me that was previously unopened.

I had no experience with BusinessObjects, but with my Oracle skills and a one-day tutorial, I shadowed my departing co-worker during his remaining two weeks, then took the reins of the project. Although he was located in another city, I was grateful for a mentor who tutored me over the phone and e-mail. He also made monthly visits to work through issues together. The ironic thing about my experience is that I worked for a BusinessObjects Authorized Education Partner. Sadly, just because an organization offers training to its clients is no guarantee that they prepare their own consultants the same way.

I finally took my organization’s 3-day universe design class after nine months of designing universes for multiple customers. I had already learned many lessons “the hard way”, but also learned several new tricks that were immediately useful. About a year later, I became a universe design instructor. It’s a demanding class, both for students and the instructor.  But I can honestly say that students leave the classroom very well prepared. I’ve seen a lot of customer universes in nearly seven years of Business Objects consulting. Most of the issues that I’m called in to resolve are issues that the class tackles head-on. Recently, SAP BusinessObjects has added an additional 2-day course covering advanced topics. So even designers who have been through the first class can now take their skills to the next level.

And the classroom material is just one part of the experience. Don’t underestimate the value of the instructor’s project experience, or the benefits your team members will gain by networking with universe designers in other organizations.

So what’s the universe design IQ in your organization? Many business intelligence teams support universes created by staff members long gone, using new staff members that have never been formally trained.  Although training budgets are frequently the first ones slashed in an economic downturn, don’t miss an important opportunity to put the “competence” in “Business Intelligence Competency Center”. Keep in mind that it might be more cost-effective to bring a universe design instructor on-site rather than sending one or two folks away to a class.

Universe Designer Refresh Structure

Tips and tricks for keeping your universe and database structure in sync.

Lately I’ve been converting a proof of concept (POC) dashboard into something production-ready. Now it’s time to adopt some naming standards and bring the original POC dashboards and their associated Query as a Web Service (QaaWS) queries, database tables, ETL scripts, etc. into a bit of standardization. One of my recent tasks was to rename tables to match our naming standards.

SAP BusinessObjects Designer (known in BI 4.0 and higher as the Universe Design Tool) makes the process fairly easy but also a bit unintuitive. Renaming a table is a snap. Simply right click on a table in Designer and specify a new name. But what if the table definition has changed somewhat? For example, what if some audit timestamps have been added? Universe Designer allows you to update table definitions, either one at a time or for the entire universe structure. However, the feature to refresh table structure is in a bit of an unintuitive menu location.

The feature in question is located on the View menu as View->Refresh Structure, as shown below.

Universe Designer Refresh Structure Menu
What makes this a bit odd is that the “View” menu is commonly used for options such as zoom in/zoom out, the display of various toolbars, the status bar, etc. Things that don’t modify the actual file (in this case, a universe) that you’re working on. Not so with Refresh Structure, which will modify the table definitions in the schema window.

Single Table or Entire Universe?

There are two ways to refresh structure: on a single table or the entire universe table structure. To refresh the structure on a single table, left click on the table then choose View->Refresh Structure from the menu. To refresh the structure of the entire universe, left click on a blank area of the schema window to make sure no tables are selected, then choose View->Refresh from the menu.

If no changes in structure were detected, the following dialog box appears:

 

Universe Designer Refresh Structure No Update NeededFrom Here To Eternity

I noticed that Designer was taking a ridiculously long time to refresh a very simple universe structure. It’s very inelegant. But I’d finally had enough and looked to BOB, the Business Objects Board, for the answer. Turns out that if you have Underline Keys checked in the Graphics options (Tools->Options), the refresh operation will take a ludicrous amount of time. Cause and effect is pretty non-intuitive here, is it not? In any case, clear the box and the performance of the Refresh Structure option will be light speed. Resolving this dilemma was my inspiration for writing this post.

Universe Designer Refresh Structure Underline Keys

Suggestions for Next Version of Universe Designer

First, I’d really like to see View Structure moved to the Tools menu; however, it’s been misplaced as long as I can remember (version 5) so it’s unlikely to change. But I can dream, can’t I?

Second, the ability to refresh a single table should be added to the right-click menu when right-clicking on a table. It’s conspicuous in its absence.

Universe Designer Refresh Structure Right Click on TableLastly, the code should be re-written so selecting Underline Keys in the Graphics options doesn’t bring Universe Designer to its knees. It’s unlikely that SAP will make any additional changes to the classic semantic layer design tool, so hopefully we’ll see the new Information Design Tool bring some improvements to the Refresh Structure functionality.

What next?

Obviously, updating the structure is only the first step if the database schema has changed radically. In my case, I did not intend to create universe objects on the new database fields. However, at this point, designers should run an integrity check on universe objects to identify errors due to the database changes. Universe maintenance is a topic well-covered in the official Universe Design course (DM310) in the SAP BusinessObjects curriculum. Perhaps I’ll have more to say about universe maintenance in a future post.

References

Using @Variable Functions in the Universe

How to use @Variables in your universes.

I wrote an article earlier this year regarding the use of the @Variable universe function in the END_SQL universe parameter to help DBAs identify Business Objects queries (see related article Identifying SAP BusinessObjects queries using END_SQL). The @Variable function can also be used in the SELECT clause of objects for display to the user or in the WHERE clause to restrict data. For example, in my presentation Secure Universes Using Restriction Sets, I implemented row-level security on the eFashion universe using @Variable('BOUSER'). Row-level security can also be implemented inside of the universe by the use of a mandatory condition, a great new feature introduced in Designer XI 3.0.

NOTE: Starting with BI 4.0, the Designer application from XI R2/XI 3.0/XI 3.1 is now known as the Universe Design Tool.

The SAP BusinessObjects XI 3.1 universe designer manual describes for the first time several new system variables. It’s unclear whether the variables were introduced with XI 3.0 (they’re not documented in the XI 3.0 edition of the universe designer manual) or were simply undocumented in previous releases. While on the subject of documentation, allow me to mention that Dave Rathbun elegantly describes several previously undocumented attributes to the @Prompt function (see Dave Rathbun’s article Designer XI 3 New Feature: Extended Prompt Syntax) that are finally documented in the XI 3.0/XI 3.1 universe designer documentation (p. 537-538).

The built-in @Variables for XI 3.1 are BOUSER, DBUSER, DBPASS, DOCNAME, DPNAME, DPTYPE, UNVNAME, and UNVID. To use them, place them inside of single quotes as a parameter to the @Variable function. It is important to note that @Variable is a universe function (along with @Prompt, @Select, @Where, etc.) to be used in the Universe Design Tool (Designer), not a report-level function to be used within Web Intelligence.

variablereferencetable

I created some objects in a universe to demonstrate each @Variable. Their values can be seen in the Web Intelligence report below. One minor lesson learned during the creation of this blog post: I had originally named the Web Intelligence document Using @Variables, but this wreaked havoc with SQL generation because I was also using @Variable('DOCNAME') in the END_SQL of the universe. A minor recursion problem, apparently. That is why the sample Web Intelligence document is instead named Using AT Variables.

@Variable Web Intelligence Report

The @Variable('BOUSER') returns the name of the InfoView user running queries in the document, which in this example is DMarks. Prior to XI Release 2, there was a @Variable('BOPASS'), but it has been depreciated for security reasons. Similar to BOUSER/BOPASS, @Variable('DBUSER') and @Variable('DBPASS') return the username and password only if the user has database credentials enabled in their user profile in the CMC. If the database username/password is defined by a universe connection, these @Variables will be blank.

@Variable CMC Database Credentials

@Variable can also be used to return information about the current report. The @Variable('DOCNAME') is the saved name of the report. The @Variable('DPNAME') returns the name of the data provider, as defined in the Query properties in the Web Intelligence Edit Query panel. In the screen shot below, I have renamed the default Query 1 to My Data Provider.

@Variable Renamed Data Provider

The @Variable('DPTYPE') describes the data provider type. I was unable to find an enumerated list in the documentation, but a standard universe on a relational database has an @Variable('DPTYPE') value of DPUNIVERS. I can only speculate that universes constructed from stored procedures or OLAP cubes probably have different values.

The @Variable('UNVNAME') returns the name of the universe as defined on the Parameters tab of the Universe Properties. I lamented that XI R2 did not have a variable (at least not documented) to identify the universe, so it’s a welcome addition. In my example, the name of the universe is Dashboard.

@Variable Universe Parameters

The @Variable('UNVID') is a new variable in XI 3.1. It returns the ID of the universe object, which is listed next to the CUID in the CMC. The universe in this example has an ID of 1303.

@Variable Universe ID

Beginning with XI 3.1 SP2, universe designers can use two new locale variables. @Variable('PREFERRED_VIEWING_LOCALE') is the user’s Preferred Viewing Locale, the locale chosen by the user to display metadata and data in his reporting tool. @Variable('DOMINANT_PREFERRED_VIEWING_LOCALE') can be used to categorize or roll up preferred viewing locales.

SAP BusinessObjects Business Intelligence 4.0 supports the following XI 3.1 @Variables: BOUSER, DBUSER, DOCNAME, DOMINANT_PREFERRED_VIEWING_LOCALE, DPNAME, DPTYPE, PREFERRED_VIEWING_LOCALE, UNVNAME, and UNVID.  BI 4.0 also adds a new variable DOCID and CMC-defined user attributes. The @Variable functions can be used in classic UNV universes created by the Universe Design Tool (formerly Designer) or the Information Design Tool. These functions are documented in the SAP BusinessObjects Business Intelligence 4.0 Information Design Tool User Guide on the SAP Help Portal.

The last item I’d like to bring up isn’t a universe-level @Variable, but a new Web Intelligence function that has been sorely missed and a welcome addition to XI 3.x. The ReportName() function returns the name of the current report tab in the Web Intelligence document. I’ve often wanted to use the name on the report tab in the report title – and now I can. SAP liked this new function so much that it is used for the default report title cell in Web Intelligence 4.0.

@Variables have many applications and I hope this article will help you take advantage of them in your universes.