Dave’s Adventures in Business Intelligence

We got a new feature / parameter in Designer a while back called JOIN_BY_SQL. This feature allows a universe designer to turn on an option that changes how multiple SQL statements are processed. This is related to the multi-path SQL generation provided by Business Objects, which frankly I think is one of the most powerful features of the product. What is multi-path SQL? What does JOIN_BY_SQL do to change that? Read on for details.

Continue reading “What does JOIN_BY_SQL do?”

Measure objects are – in my opinion – the most critical objects in your universe. Without them you can only make lists. While lists might be good for a grocery store or library, they don’t do much to help someone run their business. Measures give your universe purpose. They also are the source of most of the design challenges like fan or chasm traps.

In this post I’m going to go back to a basic concept and talk about SQL aggregation and universe projection and how they work together. Continue reading “SQL Aggregate Versus Universe Projection”

A few months ago I wrote a post about the new Designer feature “Delegated Measures” and suggested it could be used to avoid issues with projection averages. Today on BOB I answered a question about why this is important (why averages should not be projected within the universe) and decided that I should capture a more thorough example here on my blog.

Problem Statement

I have a database with sales transactions. Users want to be able to create reports using averages and percentages. Can I put these objects into the universe and still guarantee the correct results? Continue reading “Why Can’t I Average In My Universe?”

Wouldn’t it be nice if you could run an integrity check on your universe and click a box that says to only check objects changed since last export?

I tried to see if there was a way to write this in VBA, but there doesn’t appear to be a “last changed date” attribute on objects. I have been working on a Change Log script, and I could integrate the two, but that seems like overkill. I would have to maintain an unchanged copy of the universe in order to do the compare, then run the compare and capture the differences, and finally run an integrity check on that subset of objects.

It’s probably easier just to start the check on the entire universe and go to lunch…

It might not be obvious, but when you are looking at the “list mode” screen in the Universe Designer product there is a method to the madness that is the order of the joins displayed. The joins are – believe it or not – displayed in chronological order based on when they were created. This can help when you have to include a new join in a context as it will always be at the end of the list. I have become used to that feature.

But I have yet to figure out how the contexts are ordered on the same screen. Has anyone ever noticed a pattern as to how the order of contexts is determined? If so, I would love to hear it.

A Brief Overview of Measure Objects

A universe has three basic types of result objects: dimensions, details, and measures. Measure objects are analytical values like dollars or quantities. Measure objects have two extra settings that dimensions and details do not have: a SQL aggregate function and a report Projection function. The SQL aggregate function is not enforced by the application, but it should always be present. (That’s a subject for another blog post. Or two.)

The SQL aggregate function is performed by the database server, and the projection function is used by the report engine. This is what makes measures “roll up” when you slice and dice or drill on a report. The problem is, certain calculations cannot be done by the database because there is no valid projection function. Designer 3.x gives me a way to address that by introducing a new projection function setting of Database delegated. This post will explain why that’s important and how it works.
Continue reading “Designer XI 3 New Feature: Database Delegated Measures”

Designer has provided the option to create “stub joins” in a universe for quite a few years. A stub join is a permanent where clause on a table that appears as a small stub rather than a full join connecting two tables. This feature has often been used to apply security to a table via a reference to the @Variable(’BOUSER’) value.

However this strategy had one major flaw in that if the table was not referenced in the query then the security would not be applied. I now have a much more flexible (and powerful) option in Designer 3: Class Restrictions.

Continue reading “Designer XI 3 New Feature: Class Restrictions”

The @Prompt() function has been around for a very long time. There have been subtle changes made to it over the years to support some of the new Web Intelligence extended options but they were not always documented. I don’t know about you, but I find using undocumented features a bit daunting.

Continue reading “Designer XI 3 New Feature: Extended Prompt Syntax”

Introducing the Translation Manager

Translation Manager is a new tool that allows you to extract information from your universe, provide translations, and then store that translated information back into your universe. Anything that the user can see can be translated, including class names, objects names, descriptions, and even prompt text. Things the user cannot see like table names and joins are not translated, which makes sense. The data itself is not translated by this tool. Once the universe is published, the user’s location can be used to deliver a language-customized version of the universe. In the United States I’m not sure how often this would be used. (”Texan” is not recognized as a language, y’all. ) But for international companies it certainly makes sense.

No Custom Languages

One thing that I have suggested to the Designer product team is the ability to set up custom languages. And no, I’m not doing this because I want to translate my universe into Texan.

Imagine this scenario: You are a company with a product that requires reporting support. You have selected to bundle Business Objects along with your product. But each client that you sell to has the option to customize your application, including custom naming for various elements. You would like to be able to deliver that same custom name as a part of the universe.

Today you can do this with the @Select() function and by setting up a hidden class with source data, client-specific classes with their renamed objects, and security restrictions. But this is complicated. If you could create a custom language file and distribute it with your universe, wouldn’t that solve this problem in a very cool way?

This feature was one of several mentioned in my Designer Essentials presentation delivered at the 2008 GBN conference. This particular feature was on page 7.

Some time back I posted a series of articles describing how to create “dynamic date” objects based off of the current system date. For examples I used the Oracle database. This post simply contains a collection of links that I have found to references for date/time functions for other databases. I hope it will be useful.

Related Links

• DB2 Date Functions
• Informix Date Manipulation
• MySQL Date and Time Functions
• Oracle Date Functions
• Dates and Times in Teradata
• SQL Server Datetime functions

External links are provided without endorsement and may become inactive at any time. These links were functioning properly at the time this blog post was published but dagira.com has no guarantee that they will continue to be available.