Tag: Universe Design Tool

Wait a minute! What about Designer?

Yeah, what about Designer?

Earlier this week, I mentioned an article by SAP trainers Martha Thieme and Antonio Soto about education offerings for SAP BusinessObjects Business Intelligence 4.0 (see related SAP Community Network article, The Quick and Dirty Guide to SAP BusinessObjects Training). The article describes the training offerings for various BI 4.0 tools. But then the following recommendations appear after a discussion of the Information Design Tool classes.

Wait a minute! What about Designer?

Well, if you are a new customer to the SAP BusinessObjects tools, you should not be using Designer. You should be creating your universes using the Information Design Tool. If you are upgrading to the 4.0 tools, you still should be creating all your new universes using the Information Design Tool and using the old Designer (called Universe Design Tool in 4.0) only to maintain your old universes. Oh, and you should use the Information Design Tool to convert those old universes to the new universe format. (Tip: That’s a great job for interns!)

This is good textbook advice, and its what you would expect representatives from the software vendor to say. But two years after the introduction of BI 4.0, the Information Design Tool still isn’t mature enough to handle all of the capabilities of its predecessor, the Universe Design Tool (formerly known as Designer). Although the Information Design Tool in the upcoming BI 4.1 introduces some new features (predominantly around- sigh- SAP HANA), the situation is largely unchanged.  I’ve previously shared my frustrations with object formatting limitations, which remain in BI 4.1 (see related article, Object Formatting with the Information Design Tool). And Ryan Muldowney identifies some of the gaps in his article UNV versus UNX: Consuming Universes in BI 4.0. Ryan points out that some tools support UNV, some support UNX, and some support both formats. Unfortunately, this often means that both the original UNV and converted UNX must be maintained. Dave Rathbun shares similar experiences from Pepsico in his article BI4 UNV Versus UNX … Which Do You Choose?

Probably the most significant barrier to adopting the Information Design Tool is its lack of support for linked universes. Linked universes are universes that share common components such as parameters, classes, objects, or joins. But linked universes didn’t need the BI4 platform to generate controversy. They were a controversial topic long before BI 4.0 arrived. Don’t believe me? Just mention the subject in a bar crowded with SAP BusinessObjects professionals. But the core issue isn’t that the Information Design Tool doesn’t support linked universes. The core issue is that the Information Design Tool still doesn’t do a great job at supporting the benefits of linked universes: team-based development and code reuse. Sure, for team-based development there is project synchronization. But I don’t find this feature mature enough, nor is it integrated with version control, which only exists within the CMC instead of being integrated into the development tools. And while data foundations allow a single data foundation to be shared across multiple universes, there really isn’t a good mechanism for sharing objects from multiple business layers or allowing a composite business layer to be built by a team.

The situation is disappointing because the Information Design Tool and it’s “common semantic layer” were promoted during the BI 4.0 launch as the future of the universe. Perhaps my expectations were set too high by the initial BI4 hype. Because I expect a new tool to be superior when compared to its predecessor, not immature and struggling just to catch up with basic functionality. Perhaps SAP has been caught off guard as well, spending much of the last two years focused on BI 4.0 platform stability rather than analytic innovation. Jonathan Haun shares a similar perspective in his article The Top 5 tips all vendors can learn from Apple in 2012.

[SAP BusinessObjects Business Intelligence 4.0] was chopped full of innovation but its inability to deliver was a “battleship sized anchor” that slowed it down in the race to the finish line.

But perhaps my perceptions are incorrect. What is your experience with the Information Design Tool? Do you agree with Martha and Antonio? Will any concerns you have be addressed by BI 4.1 or will they still linger?

Other Perspectives on Information Design Tool

If you’re coming to next month’s SAP BusinessObjects User Conference in Anaheim, California, I’d love to chat. Just don’t mention linked universes in the hotel lounge- you might start a brawl!

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.”

Object Formatting with the Information Design Tool

Something’s missing but I can’t seem to put my finger on it…

UPDATE 06/27/2013: SAP BusinessObjects Business Intelligence 4.1 was released to customers (RTC) and into ramp-up last month.  STILL doesn’t have currency formatting – could we get some before GA later this year?

UPDATE 11/09/2012: SAP BusinessObjects Business Intelligence 4.0 Support Pack 5 (BI4 SP5) was released Friday, November 9, 2012 but STILL doesn’t have currency formatting.

Michael Welter has provided a great write up of the new Information Design Tool (IDT) introduced with SAP BusinessObjects Business Intelligence 4.0 (see his Impressions of Information Design Tool). I’ve had my own journey based on its initial release (see my related articles about the Information Design Tool) so I’ve been spending a few spare moments with SAP BusinessObjects Business Intelligence 4.0 Feature Pack 3 (BI4 FP3), currently in ramp-up, to see what’s new.

Here is the Object Format panel in the classic Universe Design Tool (UDT) aka Designer.

And here is the corresponding Edit Display Format panel in the Information Design Tool, taken from Feature Pack 3.

Notice anything missing? Go ahead and look closely. I’ll wait for you.

There (still) isn’t built-in formatting for currencies in the new Information Design Tool, only Date-Time and Numeric formats. Instead, semantic layer designers must create a custom display format for each object that needs to apply it, as customized formats are not saved either in the IDT or the universe and therefore cannot be reused across objects.

The last chapter of the Information Design Tool User Guide (available from the SAP Help Portal) is named Format Editor Reference. It states

The Format Editor lets you define the format used to display date-time and numeric values. You can select default formats or define custom formats…

Default formats are available based on Common Locale Data Repository (CLDR) recommendations, managed by the UNICODE consortium.

There are enhancements and fixes in the Information Design Tool 4.0 Feature Pack 3, but I was secretly hoping for some usability improvements to some of its existing features (see related article, Ten Features that Absolutely Must be in Feature Pack 3). There are key features and workflows in the classic Universe Design Tool that users should expect in the new Information Design Tool, even if the UI implements them differently. I think currency formatting is one of them.

What do you think?

Information Design Tool Application Rights

A minor security difference between the Universe Design Tool and Information Design Tool

SAP has included a Universe Designer Users group since SAP BusinessObjects Enterprise XI Release 2 (XI R2). My typical routine is to assign users that create universes to the built-in Universe Designer Users group. Although I can add users directly, I generally prefer to assign users to one or more groups that become subgroups of the Universe Designer Users group (and therefore inherit all of its rights).

I recently noticed that SAP BusinessObjects Business Intelligence 4.0 assigns security differently between the two semantic layer tools. For the Universe Design Tool (or UDT, formerly known as Designer in XI 3.1 and earlier), SAP uses the built-in Full Control access level.

Using the Permissions Explorer, we can examine what Full Control actually provides to Universe Design Tool users.

But the new Information Design Tool, or IDT, assigns advanced rights, not Full Control, to the built-in Universe Designer Users group.

I’m guessing that the current situation is the result of multiple SAP development teams moving quickly. I doubt it will affect my approach to security.

But for Feature Pack 3 SAP BusinessObjects Business Intelligence 4.1 coming later in 2013, SAP should harmonize out-of-the-box security and:

  • Adjust all of the predefined access levels (View, Schedule, View On-Demand, but especially Full Control) to provide varying levels of access to the Information Design Tool (consistent with Universe Design Tool)
  • Assign Full Control of Information Design Tool to the Universe Designer Users group instead of advanced rights (consistent with Universe Design Tool)
  • Consider giving Universe Designer Users group a less application-centric name like Semantic Layer Designers

How do you grant users access to the semantic layer tools? Do you leverage the Universe Designer Users group or create your own? I’m interested in learning from different approaches.

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).

Retiring Desktop Intelligence Free-Hand SQL

It’s the end of the world as we know it – time to send Desktop Intelligence reports into retirement.

Historically, it’s easier to create a SQL query and slap it into a Desktop Intelligence report than build or augment a universe. There are two methods to do this. The first method is to use the New Report Wizard to create a free-hand SQL report instead of choosing a universe. The second method uses a universe to build an initial query which is further edited in the Edit SQL panel. The Report Conversion Tool (RCT) can handle both cases – let’s take a closer look at what happens. I’ve used SAP BusinessObjects Enterprise XI 3.1 SP3 to generate my examples. In either case, I must convert these reports to Web Intelligence if I want them to continue to exist in SAP BusinessObjects Business Intelligence 4.0.

Let’s first consider a free-hand SQL query. For a simple illustration, I have used the eFashion universe to build the query and pasted its SQL into a new Desktop Intelligence document.

The Report Conversion Tool will examine the document’s SQL and build a derived table universe (first introduced in BusinessObjects Enterprise XI R2) with a single derived SQL table that embeds the query. Here is what the derived table looks like in the universe.

For the next example, I used Desktop Intelligence to build a standard query with the eFashion universe. But I then modified the SQL using the Edit SQL dialog box in the query panel. Unfortunately, it’s not immediately obvious when you open the query panel that the query has been customized. For example, the query panel below appears to show a simple query with Year, State, and Sales Revenue.

However, when the Edit SQL dialog box is opened, it becomes clear that the query has been customized with a WHERE clause limiting the year to 2004. To maintain the custom SQL and prevent the report from reverting back to the universe-generated SQL, I clicked the Do not generate SQL before running box when I authored the query.

When the Report Conversion Tool is used to convert this document, it does not create a new universe. It continues to use eFashion but also customizes the Web Intelligence SQL. As with Desktop Intelligence, this customization is not obvious from the query panel.

However, as with Desktop Intelligence, the customized SQL is visible from the View SQL dialog.

The good news is that in both cases, the Report Conversion Tool was able to convert the Desktop Intelligence report to Web Intelligence. However, there is some bad news, particularly for the free-hand SQL report. First, the Report Conversion Tool creates a cryptically named universe and places it in the Report Conversion Tool universe folder. If your environment has lots of free-hand SQL reports, you are going to end up with a large collection of small universes. It’s a support nightmare – the BI equivalent of suburban sprawl. In addition, reports that contain prompts with lists of values (LOV) will generate some pretty nasty LOV queries from the derived table. Frequently, the performance of the LOV queries is sub-par.

Although the universe LOVs can be modified to run faster, you should be always ask yourself if a universe is truly needed anytime Designer is opened. You’ll be much better served by a smaller number of universes that know how to answer many business questions and not a multitude of universes that each power a single report. When I help customers with migrations, I  prefer to perform an initial run of the Report Conversion Tool on all reports except the Free-Hand SQL reports (by leaving the “Convert reports containing free-hand SQL” box unchecked). The Report Conversion Tool will flag the Free Hand SQL reports as “not converted”. Then, a simple query on the RCT audit table will generate a list of these reports. Next, I’ll work with the customer to see if any of the reports can be retired or redesigned in Web Intelligence. Ideally, some of these reports can be recreated using a universe instead of one generated by the RCT.

If you decide that the new derived SQL universe must stay, take a moment to look at the WHERE clause of the derived table. Try to move as many restrictions as possible out of the universe and into the Web Intelligence report as Query Filters. Fewer restrictions will make the universe more generic and capable of answering more questions (and satisfying future report requests) than the original report. For commonly used restrictions, add predefined filters in the universe to make report creation easier.

If the odds are unlikely that nobody outside of IT will directly use the universe generated by the Report Conversion Tool, evaluate if it makes more sense to replace the free-hand SQL Desktop Intelligence document with a Crystal Report. Honestly, I’ve been surprised that Crystal Reports is barely mentioned by SAP when discussing Desktop Intelligence. There’s even talk of adding free-hand SQL to a future (post BI 4.0) release of Web Intelligence (see related article, Free-Hand SQL Isn’t Free). So perhaps my advice to choose Crystal Reports is ill-advised? Would love to hear everyone’s thoughts.

It’s the end of the world as we know it— time to send Desktop Intelligence reports into retirement. But thankfully, the Report Conversion Tool, even in XI R2 and XI 3.1, can help us reach our goal. Happy conversions!

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