Dave's Adventures in Business Intelligence

I recently started participating more on the SAP SCN forums. One question in particular served to remind me that not everybody has a decade (or more) of experience with the tools, and sometimes we need to answer beginner questions too.

For example, someone asked a question along the lines of the following:

I have a document with three data providers but there are four reports. How can I know which data provider goes with which report, and why are there more reports than data providers?

That wasn’t the exact question, but that was essentially what they were asking. For someone that is new to BusinessObjects it’s a reasonable question to ask. Especially if they come from a spreadsheet background where everything is right up in front. I thought it might be interesting to set up an Entity – Relationship Diagram for the various document components to help clarify how things work together.

Entities In A Web Intelligence Document

First I want to identify the various entities (components) of a document. A brief note before I start: the concepts in this post apply equally to Desktop Intelligence and Web Intelligence, but not to Crystal.

• Document
  This is the container object. Everything starts here. The parallel for Excel would be the workbook.
• Report
  This is the page or “tab” in a document. The parallel for Excel would be a worksheet. In Web Intelligence a report tab starts out as a blank canvas. I can include various other report structure items as required.
• Block
  Here is where the spreadsheet comparison starts to fall apart a bit. A report is a canvas that starts out empty, whereas a spreadsheet starts out with rows and columns. For Web Intelligence the canvas is completely blank and the structure only comes into existence when I create a block. More on that below, but for now let me close by saying there are different types of blocks such as tables, crosstabs, and charts. Is there a parallel with Excel? Sort of. Data in a sheet goes into the existing row / column structure. But it is possible to create a chart that “floats” above the worksheet. BusinessObjects uses this floating concept for every block on a report, not just charts.
• Cell
  I use this term to refer to two things: it can either be a stand-alone cell on a report, or it can be a cell that participates in a block structure. Either way, a cell is something I can click on. With a stand-alone cell I can click on a single cell. In a table I can also, even if it doesn’t look that way. It may look like I am clicking on an entire column but that’s not what happens. Viewing the report in Structure Mode helps explain that; more below.
• Variable
  Ah, variables. 🙂 Variables are like named formulas in Excel. Variables might be included (displayed) in a cell on a report, but then again, they might not. Crystal does a good job of showing which variables are currently being used. Desktop Intelligence will try to warn me if I try to remove a variable that is in use, but it’s not always correct. Web Intelligence happily allows me to delete the variable and find out for myself. 😛 In any case, a variable is a local calculation of some kind. It is not tied to a specific block or report, but it does exist in a single document.
• Data Source
  There is a subtle difference between a data provider (see below) and a data source. The source is, well, the actual source of the data. It could be a universe. In XI 3.x and earlier versions, a universe generally points to a single database. (There could be exceptions to this statement if Data Federator or techniques like database links are involved, but generally this is a true statement.) In a personal data provider it could be a workbook or a text file.
• Data Provider
  A data provider, on the other hand, is an “instance” of a data source. I can use the same source more than once in the same document simply by creating more than one data provider. To make life really interesting, each data provider can contain more than one SQL statement depending on how your universes are created and configured. 🙂 Life is never simple, is it?

Relationships In A Web Intelligence Document

Now that I have established my list of entities I need to show how they are related to each other. There is essentially a “many-to-many” relationship between data providers and blocks. (Please forgive the “ASCII Art” version of the ER Diagrams; I wanted to leave everything as text.) The overall structure of a document looks like this:

Document -< Report -o< Block -< Cell
                   -o< Cell

-< is a one to many join
-o< is a one to many outer join, where the right side of the relationship can be missing

What does the above diagram tell me? Each document has to include at least one report (much like a workbook has to include at least one worksheet). One document can have 1 or more reports, and a report can have zero or more blocks. A document can also have (but is not required to have) free-standing cells. A block, if it exists, must contain at least one cell (if it is a tabular format) or chart.

There are various block types, but each block must be exactly one type. These include tables (horizontal or vertical), crosstabs, charts, and forms.

For data it looks like this:

Document -< Data Source -< Data Provider -< SQL Statement

What this means is that a document can have one or more data sources. Each source can be used in one or more data providers. And each data provider might generate one or more SQL statements. (I'm not really going to talk about why I might see multiple SQL statements per data provider in this post.)

Finally:

Data Provider >o-o< Block

This is what I talked about earlier. Each block can have show results from zero or more data providers, and each data provider can be used in zero or more blocks. Meaning a data provider does not *have* to be used in a block, and a block can consist entirely of made up data (constants, formulas, or variables) and not reference a data provider at all.

What About Crystal?

Crystal is a more structured report writing tool. In fact, there is no concept of report blocks in Crystal at all. Instead Crystal uses what I have seen called a "banded" report structure, similar to the way Microsoft Access worked year ago when I last used it for anything. However, there are also similarities in that Crystal can also have more than one data provider, more than one data source, and those sources can be merged onto a single report.

Conclusion

I hope this helps new Web Intelligence users understand more about the structure of a document. Some of the concepts are similar to spreadsheets, others are frustratingly different. It all gets better with a little experience.

Related Links

• Wikipedia on ASCII Art