ER Modelling

What is ER?

ER is a logical design technique that seeks to remove the redundancy in data. Imagine that we have a business that takes orders and sells products to customers. In the early days of computing (long before relational databases) when we first transferred this data to a computer, we probably captured the original paper order as a single fat record with many fields. Such a record could easily have been 1,000 bytes distributed across 50 fields. The line items of the order were probably represented as a repeating group of fields embedded in the master record. Having this data on the computer was very useful, but we quickly learned some basic lessons about storing and manipulating data. One of the lessons we learned was that data in this form was difficult to keep consistent because each record stood on its own. The customer's name and address appeared many times, because this data was repeated whenever a new order was taken. Inconsistencies in the data were rampant, because all of the instances of the customer address were independent, and updating the customer's address was a messy transaction.

Even in the early days, we learned to separate out the redundant data into distinct tables, such as a customer master and a product master -- but we paid a price. Our software systems for retrieving and manipulating the data became complex and inefficient because they required careful attention to the processing algorithms for linking these sets of tables together. We needed a database system that was very good at linking tables. This paved the way for the relational database revolution, where the database was devoted to just this task.

The relational database revolution bloomed in the mid 1980s. Most of us learned what a relational database was by reading Chris Date's seminal book on the subject, An Introduction to Relational Databases (Addison-Wesley), first published in the early 1980s. As we paged through Chris's book, we worked through all of his Parts, Suppliers, and Cities database examples. It didn't occur to most of us to ask whether the data was completely "normalized" or whether any of the tables could be "snowflaked," and Chris didn't develop these topics. In my opinion, Chris was trying to explain the more fundamental concepts of how to think about tables that were relationally joined. ER modeling and normalization were developed in later years as the industry shifted its attention to transaction processing.

The ER modeling technique is a discipline used to illuminate the microscopic relationships among data elements. The highest art form of ER modeling is to remove all redundancy in the data. This is immensely beneficial to transaction processing because transactions are made very simple and deterministic. The transaction of updating a customer's address may devolve to a single record lookup in a customer address master table. This lookup is controlled by a customer address key, which defines uniqueness of the customer address record and allows an indexed lookup that is extremely fast. It is safe to say that the success of transaction processing in relational databases is mostly due to the discipline of ER modeling.

However, in our zeal to make transaction processing efficient, we have lost sight of our original, most important goal. We have created databases that cannot be queried! Even our simple order-taking example creates a database of dozens of tables that are linked together by a bewildering spider web of joins. (See Figure 1, page 60.) All of us are familiar with the big chart on the wall of the IS database designer's cubicle. The ER model for the enterprise has hundreds of logical entities! High-end systems such as SAP have thousands of entities. Each of these entities usually turns into a physical table when the database is implemented. This situation is not just an annoyance, it is a showstopper:

End users cannot understand or remember an ER model. End users cannot navigate an ER model. There is no graphical user interface (GUI) that takes a general ER model and makes it usable by end users.

Software cannot usefully query a general ER model. Cost-based optimizers that attempt to do this are notorious for making the wrong choices, with disastrous consequences for performance.

Use of the ER modeling technique defeats the basic allure of data warehousing, namely intuitive and high-performance retrieval of data.

Ever since the beginning of the relational database revolution, IS shops have noticed this problem. Many of them that have tried to deliver data to end users have recognized the impossibility of presenting these immensely complex schemas to end users, and many of these IS shops have stepped back to attempt "simpler designs." I find it striking that these "simpler" designs all look very similar! Almost all of these simpler designs can be thought of as "dimensional." In a natural, almost unconscious way, hundreds of IS designers have returned to the roots of the original relational model because they know the database cannot be used unless it is packaged simply. It is probably accurate to say that this natural dimensional approach was not invented by any single person. It is an irresistible force in the design of databases that will always appear when the designer places understandability and performance as the highest goals.

Figure 1.

Source :Extract from Drawing the line between Dimensional Modelling and ER modelling echniques.

Business Objects

Q. What are the different tools in Business Objects 5.1? 
What’s new in BusinessObjects 5.1?
Although not a major release over BUSINESSOBJECTS 5.0, BUSINESSOBJECTS 5.1 has some important new features.

· Save as PDF
· Add hyperlinks to reports
· Zero Administration BusinessObjects

BUSINESSOBJECTS 5.1 can be installed in two ways:
• from INFOVIEW via an Internet browser
• from the product CD

Q. Explain the Reporting features in Business Objects reporting tool?
How do you access data sources?
BUSINESSOBJECTS lets you access your data through a graphical user interface. You need no technical knowledge of the underlying data structures to get the information you want. What you do need, however, is knowledge of your business.
To access a data source with BUSINESSOBJECTS, you build a data provider. The types of data providers supported in BUSINESSOBJECTS depends on whether you install BUSINESSOBJECTS from the installation CD or install BUSINESSOBJECTS via an Internet browser.

Building a More Powerful Query
The procedures described in the following sections enable you to build a more powerful query by controlling the data that your queries retrieve. You can:
• Define scope of analysis, which means that you retrieve data that you will later use for analysis in the report.
• Limit the query results to data that satisfies conditions.
• Sort data, for example alphabetically.
• Retrieve a specified number of rows of data.
• Eliminate duplicate rows of data from the query result.

Running a Query on a Different Universe
BUSINESSOBJECTS allows you to run a query on one universe and then run the same query on a different universe. By doing this, you can test your query on a pilot universe before applying it to your real data.
1. Open the report containing the query.
2. From the Data menu, choose View Data. The Data Manager dialog box opens.
3. Choose the query you want to use in the Data Providers list, then click the Definition tab.
4. Click the button to the right of the current universe name.
5. In the dialog box that appears, select the universe you want to use, then click OK.
6. Click the Results tab, then Refresh.
7. Click OK to close the Data Manager.

Applying Groups of Conditions
You use conditions to limit the data retrieved by queries. A group of conditions consists of two or more conditions (predefined, simple or complex) applied on the same query. You use groups of conditions when a single condition does not enable you to obtain the data that you need.

A group of conditions consists of two or more conditions applied on the same query. In the Conditions box in the Query Panel, conditions are linked by an operator (AND or OR).
Building Combined Queries
BUSINESSOBJECTS lets you combine the data returned by up to eight queries as one set of results. These combined queries enable you to:
• Obtain a single column of data from multiple objects.
• Obtain data common to two sets of results, such as customers from a given region and a given age group.
• Exclude the results of one query from the results of another.
Note: Building combined queries allows you to combine or exclude data from the query result by using operators to combine the results of multiple queries. You can contrast this with using the In list and Not in list operators when you apply a complex condition on a query. The In list and Not in list operators include or exclude data from a query result based on a list of values that you enter.
Operators used to combine queries are Union, Intersection and Minus.
Setting up hierarchies for drill mode
Drill mode enables you to analyze data on different levels of detail. You perform analysis by navigating up, down and across hierarchies of data in a report. You drill down on the data to examine the details of more consolidated data. You drill up on data to examine totals for the underlying data. Your ability to drill on data requires that the data be organized into hierarchies.

Note: You can only work in drill mode if you have installed the EXPLORER option with BUSINESSOBJECTS.
Exporting Data From BusinessObjects

What external formats are available?
You can export data from BUSINESSOBJECTS to the following formats:
• Text. You can open the text file in many different applications, and on platforms other than Windows 
  (Macintosh, OS/2 or UNIX, for example).
• Spreadsheet format (Microsoft Excel). When you view the exported data in the spreadsheet application, 
  each value appears in a separate cell.
• RDBMS format (including .dbf format for dBASE). When you export data from BUSINESSOBJECTS to 
  RDBMS format, you create a relational table in the database. The table contains the columns of data  
  returned by the data provider. This feature enables you to extract a subset of data from a very large table,
  then export the result to your RDBMS. Once exported, the data can be retrieved by running a data  
  provider in BUSINESSOBJECTS. The universe designer can build a new universe, or edit an existing 
  universe, to map to the data in the new table. Alternatively, you can access the data directly by using free-
  hand SQL.
Options available to export data from BO
· Export to Local File format
· Export to RDBMS
· Copy to DDE
Using offline mode
Using BUSINESSOBJECTS in offline mode means that you are not connected to a repository which in turn means that, whatever your connection type, you will not be able to retrieve and send documents using BROADCAST AGENT. However, what you can do once you have opened BUSINESSOBJECTS in offline mode depends on the type of connection you chose to use in offline mode.
Client/Server connection
If you are using a BUSINESSOBJECTS client/server connection offline and not connected to a repository, you can still work with documents and universes stored locally on your computer and even create and refresh documents if you have a connection to the database, and the database connection and security information is stored on your computer.
Web connection
If you are using a web connection offline, you will not be able to retrieve documents from, and send documents to, Personal Document folders on the web server, and will not be able to create queries or refresh documents because all the database and connection information is stored on the web server.
In offline mode, you can continue to work on documents stored locally; you can work on the formatting of your reports or analyze data in existing reports, for example, and work with the data contained in the document to build new reports. If BUSINESSOBJECTS cannot establish the connection you requested with the web server, it may give you the option of starting BUSINESSOBJECTS in offline mode if your user rights allow you to work offline.
No remote connection
You may also choose to start BUSINESSOBJECTS in offline mode because you know you have no remote connection at all - for example, on a plane - and want to continue to work on documents you have stored locally. Note: The right to use BUSINESSOBJECTS offline is given to you by your BUSINESSOBJECTS supervisor or system administrator. Depending on how BUSINESSOBJECTS has been set up in your company, you may not even have the option of logging on to BUSINESSOBJECTS in offline mode.
Different ways of refreshing documents
BUSINESSOBJECTS allows you to refresh documents in the following ways:
• Manually
• Automatically at specific times or intervals
• By sending the document to Broadcast Agent, the BUSINESSOBJECTS product that manages the scheduled 
   processing of documents.
• Every time you open a document.

Saving a document for all users
If you want to make a document accessible to another user working without a repository, then check the Save for all users option in the Save as dialog box (Save As command, File menu). If you do not do this, users working without a repository will get an error message saying “You are not authorized to use this document” when they try to open your document. This is important to bear in mind if you are sending documents to other users via email, for example.
.

Displaying Data in Tables
BUSINESSOBJECTS has three basic ways of displaying the data in a report. You can display data in tables, in charts or in free-standing cells.
What are master/detail reports?
Master/detail reports enable you to split large blocks of data into sections. This type of presentation allows you to avoid repeating values and also to display subtotals.
Structuring a Master/Detail Report
You set up a master/detail report by setting a variable as a master cell. This section describes the different ways to do this. You can: 
• use a variable in a table or crosstab to create a master cell
• add a variable from the Report Manager to create a master cell


Organizing Data in Reports
Limiting the Data Displayed
You may not want to display all the data returned by a data provider in a report. You may want to focus on a selection of it only, which is difficult to do if there is a lot of data on the screen. A filter enables you to hide the data you do not want to view behind the scenes and display only the data you need. There are two types of filter. A global filter affects the whole report. A blockspecific filter only filters data for the specified chart, table or crosstab.
Creating more complex filters
Simple filters enable you to view only the data you need in a report. When you apply a simple filter, all you do is select values for a variable in the report. Complex filters enable you to display values that satisfy conditions. You define a complex filter by writing a formula.
Ignoring filters
You can force BUSINESSOBJECTS to ignore any filters you have inserted on a report so that when you make a calculation, the calculation is made on all the data, not just the filtered values. To do this, you use the 
NoFilter function. The syntax is: =NoFilter(formula)
Using Ranking to View the Top and Bottom Values
You may only want to show the extreme ranges of the data. For example, you may want to determine your top ten customers. Ranking enables you to look at the largest numbers and the smallest numbers in a report. Like filtering, it hides the data you do not want to display. The hidden data is not deleted from the report - you can view it again whenever you like by removing the ranking. Ranking also sorts the data in descending order. Thus, the largest value of the ranking is always at the top of the ranked column and the smallest value at the bottom. You can rank data contained in tables, crosstabs or master cells in master/detail reports.
Highlighting Data: using Alerter Note: You cannot use alerters on charts.
BUSINESSOBJECTS has standard calculation functions that enable you to make quick calculations on the data in reports. These calculations are available directly from a menu. The most commonly used calculations are also available on the Report toolbar.  
Understanding input and output contexts
BUSINESSOBJECTS defines an input context and an output context to determine the result of an aggregate calculation. Remember that a context is made up of one or more dimensions. The following table defines which dimensions in a report make up which context:
 
Analyzing Data in drill mode
What is drill mode?
Drill mode is a BOs analysis mode that allows you to break down data and view it from different angles and on different levels of detail to discover what is the driving factor behind a good or bad result.
Hierarchies
Objects are also organized in this way for drilling. When you analyze data in drill mode, you use hierarchies. The Universe classes are the default hierarchies you use for drilling but the Universe designer can also set up custom hierarchies. You can also create and edit hierarchies in your reports. Drill hierarchies only contain dimension objects. In drill mode, you drill down on dimensions, for example from Year to Quarter to Month. At each level, measures, such as Revenue or Profit Margin, are recalculated.
Qualifying Data For Hierarchies
BUSINESSOBJECTS qualifies data as dimensions, measures or details. Hierarchies only contain dimensions so if you want to include an object in a hierarchy for analysis in drill mode, you must qualify it as a dimension.   Note: You cannot change the qualification of data returned by a query on a universe.
Q.  What is Slice and Dice feature in Business Objects? 
                    Working in Slice-and-Dice Mode
Slice-and-dice mode enables you to switch the position of data in a report. You can, for ex, move data from columns to rows. The result would be a crosstab, where numeric data such as revenue appears at the intersection of rows and columns. You can also use slice-and-dice mode to:
• Work with master/detail reports                  • Display and remove data
• Rename, reset and delete blocks                  • Turn tables and crosstabs into charts, and vice versa
• Apply, edit and delete breaks, filters, sorts, rankings and calculations. 
Slice-and-dice mode is enabled by   the Slice and Dice Panel, a pop-up window that provides a graphical representation of the report you are   working on. You carry out tasks by dragging and dropping icons that represent the data you can use.
Formatting Sections
What are sections?
There are two types of section in a BUSINESSOBJECTS report.
Main Section
If you have not created any sections, the main section is the only section in the report and when you apply shading, the whole report (except margins) will be shaded in the same color.
Sections in a Master/Detail report
If you have set up a report with sections, you can apply a different type of shading to each section in the report. This can allow you to easily distinguish between the different sections. The sections you have created are contained in the main section and a main section header is displayed at the beginning of the report and a main section footer at the end of the report. You can hide the main section header or the main section footer or both of them if you wish.

Formatting Tables
Tables and crosstabs are made up of headers and footers, columns and rows, and cells. All these elements together make up the block. The cell is the most basic element of a table or crosstab. Rows and columns are made up of groups of cells. When you format tables and crosstabs, you can work on the formatting cell by cell or on the block formatting.
Using hyperlinks in BusinessObjects reports
You can easily add hyperlinks such as email addresses and web site addresses to your reports. BUSINESSOBJECTS automatically recognizes certain keywords or symbols included in the address, adds any extra syntax behind the scenes and formats the address as a hyperlink. Hyperlinks you add to a BUSINESSOBJECTS report are retained when you save the report in PDF or in HTML format.

Positioning report components
Relative positioning
Relative positioning means positioning a selected block in relation to markers such as page margins or other blocks. If these markers change in size or position the block is re-positioned accordingly. 
Using Outline View
When you have a report in sections with a lot of information in each section, a convenient way of presenting the report is to use outline view. Outline view folds up the sections in the report to display only the high level information that you’ve included at the top of each section. People viewing your reports can then open up the sections they are interested in to get more details. A bar with arrows is displayed at the left side of the report window. The number of buttons displayed at the bottom of the outline bar depends on the number of sections you have in the report.

What is a template?
A standard report does not contain information on page setup or include custom elements such as graphics. A BUSINESSOBJECTS template allows you to do this. A template is a special kind of BUSINESSOBJECTS document that contains pre-defined styles and structure that you use as a foundation to create reports. BUSINESSOBJECTS comes with several templates for you to use and you can also create your own.
Structure: The structure of a report defines how the data is presented. Your data can be presented in a crosstab, a column chart, or it can have a master/detail structure
Styles: The styles contained in a template define the report page background and the style of the headers and footers.

Data warehousing definitions

Data warehouse — a repository of business information used for analysis, separate from the “active” business system files.

Data mart — business-focused data warehouse. Usually focuses on a particular business issue.

Data mining — analysis techniques designed to detect patterns in the data. Could be applied to data mart/data warehouse contents.

OLAP (On-Line Analytical Processing) — Tools and applications that support interactive analysis of multidimensional data.

Data Warehousing A subject-oriented, integrated, time-variant, and nonvolatile collection of data in support of decision-making process.

Data Marts

1. R. Kimball - “a data mart is a flexible set of data, ideally based on the most atomic (granular) data possible to extract from operational source, and presented in a symmetric (dimensional) model that is resilient when faced with unexpected user queries”
2. “in its most simplistic form a data mart represent data from a single business process” Business process = purchase order, store inventory, etc Definitions

OLAP = On-line Analytical Processing

A reporting system designed to allow different flexible analysis in real time, on-line, with data structures designed for fast retrieval, with redundancy included to support performance. Note: “On-line” doesn’t indicate data from on-line systems, rather on-the-fly

OLAP Basics

1. Drill-down - decreasing the level of aggregation
2. Drill-up/Roll-up - increasing the level of aggregation
3. Drill-across - move between different star-join schemas using conformed dimensions and joins
4. Slicing and dicing – ability to look at the database from different views, e.g. one slice shows all sales of product type within regions, another slice shows all sales by sales channel within each product type
5. Pivoting - e.g. change columns to rows, rows to columns
6. Ranking - sorting

Business Intelligence (BI)

Forrester definition: A process of transforming data into information and making it available to users in time to make a difference.

Strategic BI (Examples: Balance scorecard, Strategic Planning)

1. Who: strategic leaders
2. What: formulate strategy and monitor corporate performance.

Operational BI (Examples: Budgeting, Sales forcasting)

1. Who: operational managers
2. What: execution of strategy againts objectives.

Analytical BI (Examples: Financial and Sales Analysis, Customer Segmentation, Clickstream analysis)

1. Who: analysts, knowledge worker, controller
2. What: ad-hoc analysis.

Dimensional Modeling The process and outcome of designing logical database schemas created to support OLAP and Data Warehousing solutions. THE Definition

OLTP = On Line Transactional Processing – Each transaction must be written to the database in real time, i.e. “on line” Transactional System Emphasis

Data structures must enable consistent and fast writing.