Universe and Data Access enhancements

Let’s start a sticky thread to keep track of suggestions and ideas related to the Semantic Layer (the Universes) as well as Data access from those universes.

Please post replies with your ideas…

dnewton :us: (BOB member since 2004-01-30)

Idea: Better database metadata reuse and support within Designer.

Database tables and columns can have long descriptions or COMMENTs on them in SQL Server, Oracle, and presumably other tables. It would be nice to see this data within the Universe layer. When you drag in a table, you could get not just the list of columns and datatypes, but the metadata too.

Furthermore, when you then drag a table or column over to the left-hand pane to add it to your universe, the object metadata (business terms etc.) should default into the object descriptions.

dnewton :us: (BOB member since 2004-01-30)

Idea: Reusable queries. (This is a grey area between report design and Universe design) I’d like to create a query with complex filtering (WHERE clause) logic, and then reuse that query across 3 separate reports. If I change the single query, all downstream reports would be affected.

This could be hacked by using QaaWS, but I’d like to see it a standalone thing, a sort of “Query Publisher” and then a webi data provider could simply point at a Query and not just a Universe.

dnewton :us: (BOB member since 2004-01-30)

Idea: Make Universe Designer a more object-oriented approach or application.

For example, you might have a field in a table CUSTOMER_DIM.CUSTOMER_NAME. If you could define that column once (pretty name, long description, LOV, etc.), and then reuse and inherit that in all universes, it would make maintenance easier. We have dozens of standard objects that exist in dozens of universes. If we change the description of the object in one place, we have to spend time doing it many other places.

You can do embedded/linked universes, but these are at too high of a grain.

You’d also want your inherited attributes to stick when you aliased a table. Plus have the ability to override that inheritance.

dnewton :us: (BOB member since 2004-01-30)

Allow object and class names to come from a db column or a stored procedure. This would allow them to be dynamic and potentially changed by the end user without having to load up Designer and import/export.

For example, one client might want to call an object ‘Employee’, but another client might prefer the term ‘Colleague’.

ajunell :us: (BOB member since 2004-05-03)

Provide a way to let the Universe layer talk to more than one database/data source. I think this really means baking the Data Federator product into the Universe layer.

dnewton :us: (BOB member since 2004-01-30)

Idea: possibility to reuse once-defined keys for index awareness.

Scenario: I have a dimension table (DT) and a fact table (FT). They are joined via primary key and foreign key. There are many objects defined based on DT.

If I want to set up an index awareness, I need to define keys (primary and foreign) for each of the objects individually. I need to go to Keys tab of properties of each and every object and add keys manually again and again for each object individually.

I would like to have a possibility to define keys between 2 tables once and then to reuse this information for all objects where I need it.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

But Marek, that won’t work. :slight_smile: You can’t use the same key for anything other than attributes that are unique to the table. If you use a key for a value that is not unique, you get weird LOV results.

I understand where the request is coming from, and in some cases it might work, but it’s not guaranteed to be correct. A simple universe like eFashion can demonstrate that.

Dave Rathbun :us: (BOB member since 2002-06-06)

Dave, would you believe that I had discovered this problem maybe 30 minutes ago and found why it worked like that in your last-year Ninja presentation No.1 ? :smiley:

Marek Chladny :slovakia: (BOB member since 2003-11-27)

Yes. Yes, I would. :slight_smile:

Dave Rathbun :us: (BOB member since 2002-06-06)

Idea : to have an “awareness” functionality on conformed dimensions.

When we have conformed dimensions in a data model, we tend to create aliases for the tables in the schema. Each alias would have to be created as a class to be used with different scenarios.

Instead, if we could have just one class for the conformed dimension and use a kind of @aware(Alias_manualErr.description, AliasEngineerErr.description, Alias_MobileErr.description) and the right column or columns would get picked based on the objects chosen from ManualErr and/or EngineerErr and/or MobileErr tables.

The point here is that these are not measure and need not be aggregated. But to reduce the number of redundant classes that needs to be created based on aliases. Making desinger more intelligent.

itsani07 (BOB member since 2007-07-09)

Universe-defined report-level variables. Smart Measures are close, but not quite there.

Improved List View and Context Edit dialog - ability to sort and filter the list of joins by table name.

Ability to sort objects and subclasses in a class.

Auto detection of Aggregate Navigation.

Better SQL editor - syntax coloring, table/column/class windows that don’t have to be scrolled on every use.

Merge support - allow multiple developers to work on a universe, then merge changes into a single version.

Get rid of the “Groups” list when exporting!

Ability to store Connections in universe folders, and inherit the parent folder’s rights.

Ability to associate universes with preferred Job Servers or Server Groups.

Ability to add a table by name, rather than scrolling through a list.

Direct support for database links.

Define objects to automatically include Predefined Conditions in reports when they are added (giving the user the ability to remove the Condition if so desired).

General PIA fixes - if I’m editing the SELECT of an object, alt-tab away, then alt-tab back, cursor moves to “Name” field and highlights it.

Ability to set which parse warnings are displayed.

Better Help - why, why, why is there nothing in Help about @Prompt syntax?

I could go on :slight_smile:


joepeters :us: (BOB member since 2002-08-29)

Better hurry, BO/SAP: MicroStrategy just implemented this feature in their core BI product.

dnewton :us: (BOB member since 2004-01-30)

The advantage would be that you could have schedule queries, and you have multiple reports using the same query.

I remember that in the past you could do something with Impromptu (Cognos). A queryresult (report) could be reused in another report.

fta :belgium: (BOB member since 2006-11-16)

they had this in seagate info they were called views. you could even run the “view” on a schedule and the view data would be shared in many reoprts. like a little snapshot.

LANFIELD :us: (BOB member since 2006-05-18)

More Excel import-and-export options, particularly for bulk operations. There are a robust set of VBA-based utilities in BOB Downloads, because of gaps in the basic functionality. BO implementations of any size and complexity will need to document and research on a regular basis.

For example, today I had a need to search all 40 of our universes for any Joins between TABLE_A.COL1 and TABLE_B.COL2. This is a royal pain to do today. If I had a way of searching through all Universes for this, rather than opening each one and visually inspecting…

Yes I could code something with the SDK, but that’s not a good use of my time. :slight_smile:

dnewton :us: (BOB member since 2004-01-30)

Idea: An option to allow multiple queries to be run in parallel instead of in serial. (Or would this be a report option.)

Idea: Create measures objects in Designer that are processed in the reports. This would be useful for Percentages. Where I need the report to divide numerator/denominator in the report. Because pre-calculated percents can’t be drilled or rolled up correctly. Thus, I want to maintain the formula in designer but the calculation to happen in the report.

Dustin :us: (BOB member since 2003-07-07)

I like it! In the Universe object, you could have a checkbox that says “calculate in report”. I’m guessing that for these objects, they could only include other object references, rather than raw SQL, since BO will need to “know” which other objects it has to forcibly pull into the result set, for you.

Maybe this should even be another class of object, rather than the usual Measure object. Call it a “report-side Measure”, and if your object is of this type, it almost acts like a Detail object and brings in the other related objects, so that a report-time calculation can be made.

dnewton :us: (BOB member since 2004-01-30)

Great idea :slight_smile:

I don’t think it would work to have it as a checkbox in a regular object, since its definition will be in WebI formula language, not SQL. (and actually, you’d need a separate entry for WebI formula and DeskI formula).

A separate object type would be perfect – and I like the idea of automatically including in the query the universe objects that it references.

This would be a huge win if implemented.


joepeters :us: (BOB member since 2002-08-29)

Mmmm, good point. But maybe BO could do this solely using Webi, since it’ll be one more reason to get people to switch away from deski. And therefore lessen the development effort on BO’s part, rather than having to update Deski to understand these objects too.

dnewton :us: (BOB member since 2004-01-30)