Universe Design | BI HAPPY

Archive for the ‘Universe Design’ Category

Migrating BusinessObjects Query as a Web Service

Published by Ron Keler in Universe Design, Web Intelligence, Xcelsius on June 25th, 2011 | 2 Comments »

BusinessObjects Query as a Web Service started a few years ago as a lab project. It quickly gained traction and with the release of XI 3.x was integrated into the main product tree. This feature offers BusinessObjects metadata (universe) and reports designers the ability to publish reports as web services. If you are not familiar with it, the QaaWS interface is very intuitive and essentially is completely similar to that of a web intelligence report query panel. The difference is, when done designing the report, it is published as web service on the BO server, and provides you the WSDL URL automatically.
QaaWS have many uses for integrating data from your BO system into other applications, and BO itself uses this interface to transfer data to Xcelsius, since it already came with a web services data connection.
While creating a QaaWS is very simple, migrating them takes a couple of maneuvers that you should be aware of, to assure a successful deployment of your QaaWS data driven application from dev to test and prod.
The basic migration is done using the BusinessObjects regular migration tool, the Import Wizard. Use the Import Wizard to select the option to migrate application objects and folders.

payday loans lenders online

These would be your QaaWS objects on the BO system. With this option selected, the Import Wizard will direct you to select the appropriate QaaWS from the applications folder section of the repository, which is not visible in InfoView.
After you complete this portion of the migration, your QaaWS will essentially exist on the destination system, and will be in the same general state you would expect from any migrated BO object, with their CUID retained from the original system so that any links that exist between your QaaWS and other reports or application will continue to work as they did in the original system.
However, the web services URLs have not actually changed during the migration process, and will still be pointing to the original BO system. To reconfigure the recently migrated web services and assure they are pointing to the new target system, you will need to follow one more step.
Using the QaaWS designer application, login to the destination BO server, and open each and every migrated QaaWS. When you open a QaaWS that has a different server URL then the server name they are currently deployed on, the tool will prompt you to change the URL. Make sure you select the NO option to change the URL, and then continue to republish the web service.

Once you complete this step for your QaaWS queries, all your web services will be pointing to the correct system and ready to operate properly and as expected.

Tags: QaaWS migration, Query as a Web Service migration

Why Data Modeling Fall Sideways?

Published by Ron Keler in BI At Large, Data Warehousing, Universe Design on March 31st, 2011 | No Comments »

One of the most surprising aspects I have come to expect during the years of data integration and warehousing consulting is how little data modeling is actually being practiced in this field. While it is hard to find a database technologist who would argue that data modeling is “bad” or “wrong”, it is simply not practiced. When I ask for the model of the existing database, the usual response is in the form of a 4-5 years old ER diagram that is completely outdated (at best) accompanied by an embarrassed shrug. It is one of the most basic and fundamental things to do when approaching any data problem, and yet companies are always impressed by this and are surprised by the huge benefit and value the model brings to the development process. Don’t they know how important data modeling is? Of course they do. So why don’t they practice it?

I think there are many explanations to this puzzling question, and I would like to offer a few. As you would expect the answers are not related to technical competence, or lack of appreciation for the fundamentals of good development practice. It has more to do with the reality of corporations, and the pressures of IT operations.

Lack of time to model

The fact of the matter is that you do not have time NOT to model.

The fire alarm doesn’t stop ringing. The users are constantly demanding increased functionality and services, “threatening” to replace internal IT services with external “Cloud” services or outsourced solutions. You need to deliver results every two weeks. In that kind of an environment, where you can barely plan how your day will end, you don’t have time to model. Or so goes the argument. It is not always spelled out and conscious, but it is a big reason why modeling falls sideways. The “lack of time argument”. You understand how good modeling practices can save you time in the long run, and keep your development streamlined and your environment maintainable, but you simply don’t have the luxury of long time planning right now.

While it’s absolutely clear why modeling saves you time in the long term, it will also save you LOTS of time in the short term. Databases are not built in vacuum. They are used to house information and data that will need to be consumed by users through applications. And unless you have only one person who does all the work, a data model is the ONLY way your various team members and participants in your data driven application will be able to make progress together and IN PARALLEL.

The data model becomes the contract between the application and the data it represents. It shields and separates the various parties that are collaborating to realize the latest initiative, and allows them to make progress in parallel, without needing to wait for each portion of the work to be completed before they can proceed.

Lack of tools

This does not come up often, but I still run into this sometimes. Enterprise modeling tools are typically expensive and are complex to learn and use. Licensing restrictions prevent many people in the organization from using them and lack of functionality, or training, causes database administrators and developers to eventually stop using them.

Keeping up the model can be a lot of work, but it is the only way to keep everyone informed about what is going on in the database, and how it evolves and changes over time. When it’s time to work on a new initiative, everyone can go to one place and understand the state of the information.

And while you will need a tool to store and maintain your model, especially the physical one, the most important modeling tool you can ever have is your whiteboard.

Modeling ALWAYS starts on the whiteboard. There is simply no better tool for collaborative data modeling then standing in front of a whiteboard with a dry erase marker, and a group of other talented database developers all standing or sitting around, shooting down approaches or embracing new ideas, erasing, tweaking and refining until they crystalize to a reality everyone can understand.

Lack of discipline

Modeling takes a lot of practice, and a lot of discipline. It is a bit counter instinctive for a super star developer. Their instinct, when presented with a technical challenge, is to “jump right in” and solve it in a blazing flash of genius. So the discipline to take time to see how this “fits the model” takes an extra effort upfront, which is very easily dismissed.

If you can embrace the ideas that modeling can help speed up your development cycles and can be done using simple tools like a whiteboard, you will be that much closer also accepting the fact you will need to nurture a culture that encourages discipline around modeling. Before you know it you will be moving out of a reactionary fire drill mode, and into a proactive and thoughtful mode, where you can help deliver information that matters to your organization most strategic needs.

Tags: BI SDLC, Data modeling, data warehousing

The dilemma of the expanding and shrinking universe

Published by Ron Keler in BI At Large, Universe Design on March 24th, 2011 | No Comments »

One of the most fundamental questions you will need to answer as you begin designing your meta data strategy can be borrowed from astrophysics. In much the same way that our cosmos adhere to great expending forces that stem from the big bang and keep stretching and expanding it, it also adheres to gravity and forces that ultimately will cause it to shrink back again. Just like your meta data layer. Your users are putting requirements that call for combining information across any conceivable domain, related or not, and on the other hand require concise, simple and intuitive reporting capabilities. How do you reconcile these conflicting trends?

When using BusinessObjects universes as your semantic or metadata layer, you have a variety of tools to help you cope with this fundamental difficulty, contexts, aliasing, derived universes and other techniques that can be employed to manage data complexities. However, these techniques require know-how and decrease your universes maintainability.

So, which way should you go in your universe design: reach for the mega universe that engulfs everything your users want to know about in a single universe, or carve out the information into smaller mini universes?

Consider the first the picture below. It represents a “mega universe” that spans many different subject areas, contains 15 contexts and over 1000 objects scattered in deeply nested subclasses.

Is this a universe you can put in front of users? Will there ever be more than selected few analysts with very good understanding of the underlying database who will be able to use this? If the principal guiding you toward a mega universe was trying to encapsulate database complexity from the users and handle it in the metadata layer, you might want to reconsider it.

On the other hand, the simple and small universe depicted in the following image represents challenges as well. While it will be much easier and simpler to build, test and maintain, it will surely leave your users wanting to create reports that combine the information in this universe with information in others like it. So now, you have effectively “passed the buck” to your users in terms of the burden and complexity for combing multiple subject areas. This also may not go down well.

The fact of the matter is that there is really no easy solution to this problem of the mega vs mini universes. The decisions around how much information to include in a single universe must be very pragmatic and tailored to address the particulars of the specific situation and requirement you are trying to address.

You must consider the comfort level of your technical resources with a highly complex universe, and your ability to truly test it, with all the possible combinations of data it entails at a satisfactory level, and weigh that in lieu of your users’ community requirements, and your users’ sophistication and technical savvy levels. You will likely find that in most cases, resisting the initial urge to expand, and focusing on relatively small universes that address one or very few subject areas, and do so extremely well, will become a lot more manageable and effective than a single universe designed to handle your entire data landscape.

Tags: mega universe, Metadata, mini universe, semantic layer, Universe

Handle semi aggregate measures in universes

Published by Ron Keler in Universe Design on January 25th, 2011 | No Comments »

While relatively simple data marts can be designed as true simple stars, more often than we like, our reporting data models contains snowflakes. Whatever you want to call these entities, they represent one basic problem for your reporting tools: Cartesian products. Let’s see why. In a simple snow flake model, your fact and dimension tables are joined in a “one way” relationship: one dimensional attributes can have zero or many facts. That’s it. This is a nice and clean design, and it makes aggregation of measures across facts a walk in the park. You never have to worry about cartesian products when you join facts and dimensional attributes, since your facts cannot be double counted by joining them to the dimension. Structurally, the joins look like this:

And they will yield data like this:

John Doe had two transactions, totaling $50 and in total the 4 transactions sum up nicely and with no issues to $85.

When you snow flake, you have to account for the possibility of creating a Cartesian product. Now, each dimensional row has a one-to-many relationship with an additional table, in effect creating a many-to-many relationship in your model. Now, your model looks like this:

And when you join addresses in with sales and clients, you get wrong numbers as you sum things up:

Since Mr. and Mrs. Doe moved from CA to NY, when we join in this information, our relational DB will make it look as if their 3 transactions are 6 and the $85 we earned is $145, because it’s double counting transactions. This may be good news for the sales team, but the auditors will give you a bit of a hard time.

As much as you will try to avoid this problem, complex models often require this kind of snow flaking for all sorts of good reasons. In this example, imagine if you are dealing with many millions of customers, with addresses as multipliers for each customer, I doubt you will be looking to add an “address” dimension in your sales fact. Slightly normalizing your model and adding the kind of relationship illustrated in the second model is perfectly reasonable.

Different tools have different solutions to this problem; BusinessObjects Universe meta-data layer provides you with the tools to address this problem in the form of aliases and contexts.

First, create your universe and setup your joins. You can try running the universe Check Integrity tool, but it will not detect any problem. The structure looks correct from a simplistic perspective, and as long as you are not trying to aggregate, you will not see problems. But we know better…

To solve the problem, we can force business objects to issue two separate queries when users request to get data from all three tables, and maintain the correct aggregation level for the sale measures. Here’s how:

First, we create an alias table to the sales table. We will point the sales object to the alias and leave the rest of the table attributes as mapped to the base representation of it.

Next, create a join from the client table to the sales alias. Finally, create two contexts, one for the join path that includes the address table and the sales table, and the other for the client and sales alias only.

That’s it! You are now ready to handle a variety of query scenarios without needing to worry about your universe users accusing you of “cooking the books”… Here’s what will happen:

When your users select the client name and address objects, webi will issue a single query based on the join path/context specified as CLIENT_SALES. When they add the address zip code, webi will issue a SECOND SEPARATE QUERY to get that data and will stitch the result sets together in its micro-cube.