Aug 29 2013
BI4 UNV Versus UNX … Which Do You Choose?
When SAP released BI4 several years ago it featured a major upgrade to one of the core technologies used by Business Objects since the beginning of the company: the universe. What does this mean for you and how does it impact your intentions to move forward with the latest and greatest offering from SAP? Many of you know that I currently work for a fairly large company, and large companies are often slower to move on to new technologies as they’re released. I have not talked a lot about BI4 in my blog yet primarily for that reason. However, we’ve had over a year to review the new Information Design Tool (IDT) and the BI4 .UNX format, and I’m finally ready to share some thoughts.
A Brief History of the Universe
When Business Objects (the company) was launched back in the early 1990’s (yes, it really has been that long!) their claim to fame was the universe or semantic layer. The founders of the company felt that there should be a way for folks that were not SQL literate to be able to create queries against a database. They created tools that allowed a developer to create a framework of various query elements and publish it. They then built an engine that would put all of the parts (objects) together in a valid SQL statement based on the elements selected by the query writer. The basic concept wasn’t that difficult, but it became (at least in my opinion) the primary force driving the success of the company.
When Business Objects 4.0 came out (the original 4.0, not our current BI4) in the late 1990’s there were several major changes made to this core technology. For one thing we finally got a graphical interface with drag-and-drop to create joins! The new universe designer tool generated files with a .UNV file extension. This tool was enhanced over the years but the core functionality of a universe remained as it was when it first came out. If a universe developer from 1996 were to come forward in time and look at the way universes are built today they would be comfortable right away. There just weren’t that many big changes.
That all changed with BI4.
Revolution Not Evolution
By far the most significant feature of BI4 is the ability to create multi-sourced universes. This has been a gap for a very long time, and it seems that SAP decided the best way to implement this was to start working on a new, more modern and extensible universe framework. This resulted in the need to create a brand new universe design tool, and along with it a new universe format. As a result in BI4 we have two ways to build universes: the legacy Universe Designer which creates .UNV files as it always has, and the new Information Design Tool (also known as IDT) which creates .UNX files.
On the surface the basic requirements are the same. The goal of a universe is still to present a semantic layer that insulates a query writer from the complexities of the database design. But in order to support multiple data sources in the same universe they needed to move beyond the limitations of the original .UNV format.
For example, here’s a rough diagram of how a universe was built using the .UNV format:
A connection had a one-to-many relationship with a universe. This means that a universe had one and only one connection, but that connection could be utilized in multiple universes. Keeping to the entity-relationship motif:
- A universe could contain one or more classes
- A class could contain zero or more objects and filters (predefined conditions)
- An object is based on zero or more tables
- A universe contains one or more tables, but tables can only be inserted based on the singular connection
We used to have a very cool database structure that looked quite a bit like a nice normalized database design. Each of the entities listed above appeared as a table, and their relationships were maintained by appropriate key definitions. (Unfortunately we lost that after the switch to the Crystal server technology and no longer have the ability to look at universe components in the repository database.)
This structure was robust enough to last over ten years. However, it seems that based on the way .UNV files were built, it was going to be very difficult to add multi-source capabilities (along with multi-developer) while retaining compatibility with the older structure.
Which brings me to this, a rough diagram of what a new BI4 .UNX universe looks like:
In a BI4 .UNX everything is contained in a project. A project can contain multiple connections, a data foundation and a business layer. The technical part of universe design (connections and database structures) has been completely separated from the business representation (classes and objects). In fact we can build more than one business layer on top of a shared data foundation, allowing us to potentially leverage a single enterprise model for multiple universes! All of these are good steps, and very welcome advancements in the process of building a universe. I will cover many of these features in more detail in future blog posts.
The question that this blog post wants to answer, however, is which format should we use?
Product Support
The .UNX format is brand new in the BI4 release. Because the universe (semantic layer) is and will continue to be one of the most critical parts of a BI implementation, I believe that SAP wanted to make sure that we could ease into the new .UNX features at our own pace. Can you imagine how bad it would have been if the .UNX format was a complete flop out of the gate? Because of this, BI4 included both the Universe Designer and the Information Design Tool. Universes migrated from XI 3.1 (or earlier) will be upgraded to BI4 .UNV files. Migrated reports will continue to use these universes. Any migrated .UNV universe will also continue to be single-sourced, and our primary reporting tool (Web Intelligence) will continue to run just as it did in XI 3.1. In short, everything can continue to operate as it had before.
Going forward, however, I would bet that support for the legacy .UNV format will slowly be phased out. I can see indications of this today in looking at the following product support matrix provided by SAP earlier this year. Notice that some of the newer tools (SAP Dashboard Builder and Explorer) will not work with the .UNV format; they require a .UNX universe. SAP clearly wants their customers to move forward to the new .UNX format, and if we want to use the latest “toys” then we may be pushed there sooner rather than later.
Today of course Visual Intelligence is now Lumira, and BI 4.1 will re-introduce .UNV support for Explorer.
If I were starting a new project today, it would seem that the best choice would be to immediately jump on the .UNX platform and take advantage of all the cool new things. 😎 However…
UNX Concerns
… there are several concerns that I would like to present for consideration.
- SDK Support
XI 3.1 offers a Software Development Kit (SDK) interface which allows programmers to automate specific tasks. For example I wrote a utility to compare two universes, and I also wrote a utility that we use to update Teradata schema names (for example from _D to _P) while moving from development to production. Neither of these tools will work on the new .UNX format. There is an SDK being developed for .UNX but it will be java rather than VBA, so these utilities will have to be rewritten. (I recently posted about an update from our DBA team that means the schema change utility will no longer be required.) - Product Stability
The Universe Designer tool is a very mature product that has been in place with only minor upgrades for over ten years. As you might expect, it’s very stable and well known. The Information Design Tool is a brand new product that inherited some of the functionality from the designer tool. During my initial testing for the BI4 suite I encountered a number of issues, many of which have been addressed through service packs and updates. However, we have one complex universe that still will not convert to .UNX correctly. - Developer Experience
As mentioned in the prior bullet, IDT is a new tool. There are new workflows for standard universe design tasks (such as creating a self restriction or “stub” join) and brand new workflows (such as creating and maintaining projects) that did not exist in the Universe Designer. That means that there will almost definitely be some productivity issues during our initial adoption. I know that I personally found some of the new workflows very confusing at first! Fortunately, there is an excellent resource for experienced universe developers put out by SAP. They have created a series of short YouTube videos that describe many of the features and workflows of IDT. As we begin to move into BI4 on an enterprise level, we are going to plan for some initial confusion and lower productivity from our universe designers while they come up to speed on the new tool.
Conversion Process
One final item to consider: when files (reports and universes) are upgraded from XI 3.1 they will come into BI4 as .UNV universes. To convert from .UNV to .UNX is fairly simple, but the end result is a new universe, not an update to the existing universe. This means that in order to move forward and completely adopt the .UNX format, each and every document would have to be opened and “re-pointed” to the new resource. As of today, there is no automatic way to accomplish this task. Once the reports are reset to use the new universe, we would want to do thorough testing to make sure the .UNX universe is generating the expected SQL and the correct results.
Long Term Viability
Has anyone noticed how long it has taken SAP to finish the #diedeskidie process? 😉 I’m not sure they want to go through that again. I was just about to publish this post when Dallas Marks published one of his own: Wait a minute! What about Designer?. Dallas quotes a source at SAP that suggests that Universe Designer is dead, and that all work should already be done in IDT. You can read the entire quote on his blog; here is the first sentence:
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.
We’re certainly not a “new” customer, having been using Business Objects universes for well over a decade. But even for new development we’re finding that there are reasons (most of them detailed here) that are causing us to continue to use Universe Designer rather than jumping immediately on the IDT train. Hopefully this post gives you some additional perspective on some of the differences, advantages, and concerns around the new .UNX universe format in BI4. (I didn’t even bring up linked universes like Dallas did.) Ultimately, of course, we are all going to get there. It just may take a while.
Another thing that the IDT doesn’t allow that you can do in Designer is Linked Universes. At a former employer which hosts software and data for its clients, we made extensive use of linked universes. Each client had the exact same underlying data structure and the universes were complex. We had a requirement for each client to see only its own data. If that were the only requirement, we could have used connection overrides and a single universe. However, there was the additional requirement that folks in our main office had to be able to run consolidated reports across all clients, so there was no way to use a connection override for that.
Several years ago I did user experience testing on the IDT at a user conference. When I mentioned our need for linked universes I was basically told that the development team didn’t think anyone was really using them (despite the fact that they’re recommended as a best practice in the advance universe design clas…) and so they hadn’t included them in the IDT design. At that time, SAP was planning on not including .unv support in what is now 4.0. I’m glad they changed their decision for now.
-Dell
Hi Dave,
as a seasoned universe developer, the lack of a OK button on the object creation just freaks me out. I fear for the complex universes over application databases that my customers currently utilise.
James
(welcome back)
Hi,
Great article and it will take some time to get used to the new IDT. That’s a big failing with the conversion process with having to re-point reports – something I wasn’t aware of! Another caveat when converting is that the new platform must have same database drivers installed. I was migrating a demo from SQL Server to Oracle and the universe to UNX but the conversion failed as the new platform didn’t have SQL Server drivers.
Last year I worked on a project building universes using IDT and we found stability a major issue – hopefully that has improved now with more recent service packs. We also found a bug with entering ConnectInit parameters for a connection – our workaround was to define connections using Designer, publish to BI Platform and then import into IDT.
Lastly Live Office is also does not support UNX according to Altek’s Blog – which could be an issue for customers who utilise live office.
Regards
Al
Having heard Dave make noises that are not publicly repeatable when he first started using IDT, I expected a less objective approach to this blog post. 🙂
Yelak
Hi Dave,
Thanks for sharing this. I’ve been developing Universes for more than 15 years and I have experienced the Designer module to be an outstanding and very stable product. I’ve been using the IDT now for a couple of months and YES it’s workflow puzzled me from time to time. IDT is really a sophisticated product and I enjoy using it. The way you can define and control contexts is very nice and also the ability to define queries and keep them for future analysis is very productive. Some irritating issues are the high usage of the CPU and the integrity check. It takes ages to parse objects containing ‘sum case when..’ logic. Sometimes measure objects which are referenced are believed to be non-numeric. Publishing the business layer does not always include a changed foundation layer. I hope you will get back to the fundamental choice for a multisource or single source UNX. My understanding is that if there are no business reasons to combine multiple data sources the single source unx is the better choice thus avoiding the join mechanism to be handled by the data federator. The way WebIntelligence can handle multiple queries pointing at different data sources is quite good , so in what scenario would you choose the multisource approach ?
Regards and thanks again for your excellent article.
Ton
Ton, as I mentioned at the top of the post, I believe that the multi-source capability of IDT is one of the most significant advances over Universe Designer. When starting a new universe with IDT that is the first critical decision, and once made, there doesn’t seem to be a way to revisit it. Meaning once you’ve created a single-source universe you cannot then later decide to convert it to a multi-source. The same is true for the reverse (can’t convert a multi to a single-source) as well.
That being said, in a very early release of BI 4 we experimented with the multi-source capabilities and found that in our test case our performance was better when we asked Web Intelligence to create two data providers and merge them as compared to creating a complex universe! To be very clear, we did not do anything to attempt to tune the federator process, we were just trying to prove that the plumbing worked. We definitely accomplished that. I expect that if we revisited that experiment today and spent even a little bit of time tuning the server processes that the performance would be better with a federated universe.
Also our enterprise standard is to move all of our core reporting data onto Teradata, so for us (at least currently) the multi-source capabilities are less important. I think partially it’s because we’ve lived without it for so long that we’ve gone that direction though. 🙂
From what little I have looked into this, if you use Crystal Reports there will be additional work. Crystal Reports 2008 reports are upgraded to Crystal Reports 2011 when you run the upgrade management tool and work fine against UNV universes. However, if you want to use UNX universes, you will need to convert the Crystal Reports 2011 to Crystal Reports for Enterprise. This is fairly easy but must be done for each report.
I’m not sure how fast we will be moving to the new format once we have completed our upgrade. Because of this, I haven’t looked at the IDT very much yet.
Great article Dave! BI4.1 version of Explorer will support .UNV’s. so if a customer is looking to upgrade BI4.1 is the way to go.
Ainsley, the addition of UNV support to Explorer in BI 4.1 is indeed a welcome “regression” for platform support. 🙂 We’ve been building new UNX universes for our Explorer use so far, but we’re not able to convert our legacy universes to UNX just yet. When we get to 4.1 we’ll be able to retire the UNX version until we’re ready to completely convert.
My other major complaint about .unx universes is the lack of a robust SDK that allows you to build a complete universe in code. That capability is present in the Designer SDK for .unv’s but not in the Semantic Layer SDK for .unx’s. For most, this is probably not a real issue, but I have a couple of (OEM) client who include BO in their software as a service offerings. These clients offer their accounts a way to add custom fields to their software and we’ve written code to pull the info about the custom fields from the database and use it to build new dimensions on each accounts custom universe.
I realize this is not something many folks will be interested in, but it definitely affects the recommendations we make for some of our clients.
Dell, from what I know there is a basic SDK that came out in 4.1, and I am confident that it will continue to be enhanced.
I’ve made the switch to IDT and despite the bugs and all my screaming and swearing to the contrary, I have to say, I won’t go back to Designer. The interface is much more modern, and the Views feature alone has probably saved my eyesight long-term. I appreciate the separation of the business layer from the data foundation, even if in practice the two could be dissociated a bit more: I’d like to see both treated as source code, in some text-readable format, that could be easily tracked/merged across a development team. (THAT will likely never happen — but a girl can dream, right?) Contexts are easier to manage (even if they have some work to do there in my opinion). And while there isn’t an OK button for object creation, 4.1 does have a reassuring little green checkbox.
I was initially enthusiastic about multisource universes, and especially about the Data Federation layer introduced in 4.1. Since I’m (ahem) often building universes on OLTP databases of all stripes, I thought this might buy me some sorely-needed flexibility on some projects. However, I did an about-face when I ran into some (what I consider) thorny issues around database qualifiers. I was so frustrated I submitted an “idea” to SAP Idea Place (“Multisource Universes: Reference tables without explicit database qualifier”, for anyone who is interested). I then had to convert my nearly-complete universe manually back to monosource, which lost me a week. After that little misadventure, I won’t be playing with multisource universes any time soon.
Hi, Emily, thanks for your comment and insight on IDT.
I tried to save my eyesight by convincing my manager I needed a 42″ monitor for my desk. He didn’t go for it… 😉 I agree that in theory the context management is easier to use, but unfortunately most of my IDT experimentation has been done on converted universes which means the contexts are as complicated as ever. I haven’t had time to go back and clean things up. You say “more modern” but I say “too busy” as far as the interface goes, so perhaps we’ll agree to disagree there. 😎
I love IDT but I’m missing ‘FILTER_IN_FROM’.
I don’t think I’d start another universe that was going to need outer joins in IDT.
Hi Dave,
I’m new in the IDT world, so when we create the Data Foundation. Can that be published? Our goal is to have more than one person working on the universe simultaneously, and I’m not sure if this is possible.
Thanks
Neal
Hi Dave,
Thank you for sharing your blog.
I have a question about IDT and WEBI 4.1. This problem really drives me crazy now…!
I am working on a Oracle database based IDT universe, I’ve created the DF and BL based on that Oracle database and tested the report by creating the query in IDT. While everthing works fine in IDT but when I create the Webi report using WEBI 4.1 with the same query objects, the data/value that webi report returned is different from the value/data in IDT query.
I really cannot figure out what is the problem or what causes this problem to happen, if you could help me with this or if you heard other people had the same problem and they have solved it, please let me know! That will be really helpful and I am appreciate your help!
Thank you!
Lily Kong
Hi, Lily, thanks for your question. The first thing I would do is validate whether the SQL code being generated is the same in both cases.
Hi Dave,
Thanks for your reply. I’ve solved that problem by checking the table joins. 🙂
Now I am having another problem :(. I am creating a new report of Inventory for customers. The universe is created against Oracle database as well.
So, for each part in the inventory, it has more than one customer quote prices and quote dateS. My requirement is to get only latest quote price that has been quoted, and if the part has more than one same latest quote dates, we get the maximum price among those same dates.
Example for part “BC20135”, it has several quote prices and different quote dates:
[NO] [Quote Date] [Quote Price]
1. 04/12/2015 $12,000
2. 04/12/2015 $15,250
3. 01/20/2015 $18.400
4. 08/05/2014 $19,000
5. 12/10/2012 $12,000
in this case, since we have the same latest quote date of 04/12/2015, we will get the highest quote price which is No. 2 “$15,250”.
I did several tests:
First one was– SELECT MAX(QUOTE.UNIT_PRICE) FROM QUOTE WHERE MAX(QUOTE.DATE). However, I cannot use Max function under Where clause.
Second one was —
I created a dimension for quote date and have the “SELECT MAX(QUOTE.DATE)” on the select statement, and a measure of quote price “SELECT MAX(QUOTE.Unit_price)” on the select statement, what the result returns to me is the latest date and highest price: 04/12/2015, $19,000, but this isn’t the right result.
Third one was —
I created a derived table and used GROUP BY function and HAVING function:
SELECT
QUOTE.UNIT_PRICE,
MAX(QUOTE.DATE) AS DATE
FROM
QUOTE
GROUP BY QUOTE.DATE
HAVING QUOTE.DATE=MAX(QUOTE.DATE)
but.. none if them is working.
Do you have a suggested SQL for it?
Please help me 🙁 …
Thank you
Lily Kong
SELECT
QUOTE.DATE,
Max(QUOTE.UNIT_PRICE)
–MAX(QUOTE.DATE) AS DATE
FROM
QUOTE
WHERE QUOTE.DATE=(SELECT MAX(DATE) FROM QUOTE)
GROUP BY QUOTE.DATE
–HAVING QUOTE.DATE=MAX(QUOTE.DATE)
Hi,
We are thinking of moving over to .UNX universe formats – mainly to take advantage of the multiple database connections. We have the requirement where we would like to deploy a general universe that needs to connect to database A and B. What I mean by general is that the same universe will be deployed in many different environments. Some of those environments will only have an instance of database A not database B.
Can BO handle this? Can you setup no connection for database B and still get queries to run that only need to access database A?
Thanks,
Allon
Allon, that seems a bit of a strange expectation. When you create a multi-source universe I assume you will have objects built against both sources? What is going to happen when a user tries to use an object pointing to database “B” and it doesn’t exist? I’m not sure how you would work around that.
I have been using UNV for well over a decade without any major issues that were not resolved.
Tried using IDT for a number of months. So far really buggy and clumsy to use. Lots of stuff there to play with but then again when there is so much out there it makes stumbling even more apparent.
That said, we will not be going to IDT aside from this one universe we created. Looking down the road to get out of the SAPBO environment; too many bad decisions on their part; too many wasted nights trying to fix or implement their way of thinking.
This is really helpful.
I’ve been googling for other articles such as this, to get the current status of Crystal Reports for Enterprise or CR 2013, in regards to the Product Support (but this article seems to be the best answer).
So in the chart, it says Crystal Reports (2013 I assume) only has limited support for UNV ? Is that still accurate ? One of our main goals in moving to BI 4.x is to add Crystal to the report stack.
Thank you.
Hi,
I am new in IDT and converted universe from UNV to UNX format. But I am facing below issue.
Prompt displaying incorrect List of values in webi but same object shows correct values in IDT Business Layer.
Please suggest.
Thanks
Dave,
Has anyone created a universe documentor tool for the idt???
There is someone (internal) at my employer that is working on replicating this in Java, but since it’s not my product I am not going to be able to post it. I can tell you that it’s definitely possible given the current state of the SDK for IDT.