Slowly Changing Dimensions

When ever we required tracking the changes of dimension, usually slowly changing dimension comes in the picture. There are three basic techniques to implement them.

Type 1 – It’s the simplest one, Update the existing dimensional value with the latest value. So it is an overwriting of data.

Type 3 – In this Add one more field for storing the old value of dimension. So here one level of history is maintained. In this case when a change occurred then existing value is moved to this new field and new value is overwritten in the existing (current) dimension.

Type 2- Widely used to maintain the history. In this case when a change occurred then a new row is added with a new surrogate key (Primary key). But based on implementation, there could be few columns added in the existing table for tracking the history.

-Only most-recent-row Flag field is added to table – here in this case the latest row flag is equal to ‘Y’. So while retrieving the most current data we only have to use filter flag equal to ‘Y’

-Another way to add row start and end date – Here in this way when ever there is a change happened then a new row is inserted in the table and row start date is the current date and row end date is NULL or some big end date. While the existing row’s end date will be updated by current date – 1.

-The last one is combination of both the above in which all the three columns is added in the table.

Business Objects Migration to XI

Steps For Business Object Migartion to latest version (BO XI R2)

1।Identify Suuport Teams: - The support teams should be identified before proceding for migration or upgardation. These Support Team will be required during the Upgrade/Migration.

a. Database Administration Team
b. Operating System Administration Team
c. Hardware Support Team
d. Third Party Software Support Team (Web and Application Servers).
e. Business Objects Support Team

2.Analysis of Current system:– The detailed analysis of existing system should be done stating of migartion or upgardation of existing system. Following are the major area of concentration while doing the study of existing system.
a. Existing Network Architecture
b. Business Objects Deployment Architecture
c. Existing Repository layout
d. Web-Intelligence
e. Universe & reports
f. User Setup
g. Audit Data

3.Analysis for Target system:– The detailed analysis should be done for covering the requirements and providing new or enhanced features of Business Objects XI Release 2. Following are the major area of concentration while doing the study of Target system

a. Network Architecture
b. Business Objects Deployment Architecture
c. New Folder and category structure for Web-Intelligence
d. Rights for Universe & reports
e. New Security model similar to the existing system
f. Setup of Audit Data
g. Integration of New features

4. Backup of existing production environment:- Creation of another production environment of the existing setup for parallel testing and fault tolerance before starting the actaul migration or upgrades. Steps for taking a backup

a. Running a Repair and Compact on your source:- This should be performed on the existing system to rectify the error and to check the integrity of the system.

b.Exporting locally stored objects:- All the objects that needs to be migrated to new system should be present in the repository. So before starting the migration the latest or required objects should be moved from locally to Business Objects repository.

c. Source Repository Backup:- The backup of the source repository should be created. In case if there will be some problem faced while migration. So with the help of this backup system will be resorted.

5.Creation of New Environment:- Next step is to create a new development, QA and production environment of Business Objects XI release 2.

a. Configuration Business Objects Server:- The configuration of target system needs to done based on the requirement and the required hot fixes or patches will be installed on the target system.

b. Backing up the destination CMS database:- The destination CMS database will backup before migration. This will be required in case of phased migration. This step is least required for new target installations.

6. Ensuring appropriate rights before migration:- Following rights are required for migration

a. A General Supervisor in existing Business Objects 5.x environment.
b. An Administrator group user in the target Business Objects XI Release 2 environment.
c. To import any resource into the CMS, The rights needed to add objects to the destination folder to which the resource is assigned.

7. Migration of Objects (Actual Migration)

In this Incremental approach migrations will be performed Application by Application. This is a good approach in my view. In this approach initial, phased import of BI content will be performed. After each import the contents are verified and validated.

The initial migration if performed by importing source user groups and content into the new environment gradually. Validation will be done after each sweep with the Import Wizard. If there are any issues, those are fixed right there and don’t have to begin the entire migration all over again.

Migration’s can also structure in the phases deepening on the structure of existing repository or the logical groupings of user groups and resources. The security setup should be done manually.

Phase can be prepared for example to migration:
§ Department by department
§ Application by application (by resource type)
§ Domain by domain

In this step actual migration will be performed with the help of Migration wizard. Migration will be performed using incremental approach. The migration will be done by object by object. The migration will be performed from Business Objects 5.1.6 to development environment of Business Objects XI release 2

The whole migrations process will be completed in 4 steps. Each step will follow complete software development lifecycle. This will give us following advantages

· Greater control over each migration
· Only the last update to be rolled out if there are errors
· Changes can continue on the source system for the areas that are still to be migrated

For each step testing will be done as:

Verifying the import process:- In this step initial testing of import process will be performed by following ways
1. Checking the log files
2. Verifying the counts and timestamp of objects, user etc.

Testing:- In this part Objects testing should be performed based on manual and random testing.

The steps are defined as follows -

a. User / Group Migration: During this step all the users information will be migrated from Business Objects 5.1.6 to Business Objects XI release 2. In first step following Objects will be migrated

· Repository Domain Migration – The existing repository domain will be migrated in new environment. In Business Objects XI release 2 the domain is migrated as folders.

· User / Groups Migration – All the User and Groups will be migrated from existing system to the New system. New security system will be implemented in new environment similar to existing environment.

b. Universe Migration:- In this step following objects will be migrated. All the universes will be migrated from Business Objects 5.1.6 to Business Objects XI Release 2.

· Connection Migration - Here all the connections related information will be migrated.

· Universes Migration - All the universes will be migrated from source system to new target system.


At the end of second step there will be following deliverables
· Detailed document on Universe Management
· Test case and test result on Universe and connection migration
· New development Environment having Universe and User information ready for migration in QA environment for UAT.

c. Report Migration:- In this step all the reports will be migrated and verified. In this step following objects will be migrated.

· Full Client Reports Migration – In first pass all the full client reports will be migrated and verified with the migrated universes and connections.

· Webi Reports Migrations – after successful migration of full client reports, Webi reports will be migrated to new system.

d. Integration testing :- In this step the integration testing, Minor modification will be performed on objects. Here the entire new development setup of Business Objects XI release 2 will be tested in one go.

At the end of this phase we will get the new system of Business objects XI release 2 having above objects.

8. Planning for Customization: - The required customizations should be identified so that new environment will also be similar to the existing environment. As the security model in Business Objects 5.1.6 is user centric and the security model in XI Release 2 is object centric. There will be need to customize the security setup and user information.

Dimensional Modeling

Dimensional Modeling is the designing technique used to support End- User quires from a data warehouse. Entity-Relationship design technique is very useful while designing a transactional system for day to day entry.

What is ER?

ER is a logical design technique that removes the redundancy in data. The ER modeling technique is used to illuminate the microscopic relationships among data elements. The ideal form of ER modeling is to remove all redundancy in the data. This is very useful because transactions are made very simple. The transaction of updating a customer's address may involve to a single record update in a customer address master table.

The main complexities with ER modeling are

· End users cannot understand or remember an ER model. End users cannot navigate an ER model easily as compared to DM.

· Software faces performance issues while querying a general ER model.

What is DM?

Dimensional Model is a logical design technique that seeks to present the data in a standard, intuitive framework that allows for high-performance access. Every dimensional model is composed of one table with a multipart key, called the fact table, and a set of smaller tables called dimension tables. Each dimension table has a single-part primary key that corresponds exactly to one of the components of the multipart key in the fact table. This characteristic "star-like" structure is often called a star join. The term star join dates back to the earliest days of relational databases.

A fact table, because it has a multipart primary key made up of two or more foreign keys, always expresses a many-to-many relationship. The most useful fact tables also contain one or more numerical measures, or "facts," that occur for the combination of keys that define each record.

Dimension tables, by contrast, most often contain descriptive textual information. Dimension attributes are used as the source of most of the interesting constraints in data warehouse queries, and they are virtually always the source of the row headers in the SQL answer set.

DM vs. ER

The key to understanding the relationship between DM and ER is that a single ER diagram breaks down into multiple DM diagrams. The first step in converting an ER diagram to a set of DM diagrams is to separate the ER diagram into its discrete business processes and to model each one separately.

The second step is to select those many-to-many relationships in the ER model containing numeric and additive non-key facts and to designate them as fact tables.

The third step is to de-normalize all of the remaining tables into flat tables with single-part keys that connect directly to the fact tables. These tables become the dimension tables. In cases where a dimension table connects to more than one fact table, we represent this same dimension table in both schemas, and we refer to the dimension tables as "conformed" between the two dimensional models.

If the design has been done correctly, many of these dimension tables will be shared from fact table to fact table. Applications that drill down will simply be adding more dimension attributes to the SQL answer set from within a single star join. Applications that drill across will simply be linking separate fact tables together through the conformed (shared) dimensions.

The Strengths of DM

The dimensional model has a number of important data warehouse advantages that the ER model lacks.

First, the dimensional model is a predictable, standard framework.

A second strength of the dimensional model is that it withstands unexpected changes in user behavior. Every dimension is equivalent. All dimensions can be thought of as symmetrically equal entry points into the fact table. The logical design can be done independent of expected query patterns. The user interfaces, Query strategies and SQL generated against the dimensional model are symmetrical.

A third strength of the dimensional model is that it is gracefully extensible to accommodate unexpected new data elements and new design decisions. The following graceful changes to the design after the data warehouse is up and running by:

1. Adding new unanticipated facts (that is, new additive numeric fields in the fact table), as long as they are consistent with the fundamental grain of the existing fact table
2. Adding completely new dimensions, as long as there is a single value of that dimension defined for each existing fact record
3. Adding new, unanticipated dimensional attributes
4. Breaking existing dimension records down to a lower level of granularity from a certain point in time forward.

A fourth strength of the dimensional model is that there is a body of standard approaches for handling common modeling situations in the business world. These modeling situations include:

• Slowly changing dimensions, where a "constant" dimension such as Product or Customer actually evolves slowly and asynchronously.
• Heterogeneous products, where a business such as a bank needs to track a number of different lines of business together within a single common set of attributes and facts, but at the same time it needs to describe and measure the individual lines of business in highly idiosyncratic ways using incompatible measures.
• Pay-in-advance databases, where the transactions of a business are not little pieces of revenue, but the business needs to look at the individual transactions as well as report on revenue on a regular basis.
• Event-handling databases, where the fact table usually turns out to be "factless."

A final strength of the dimensional model is the growing body of administrative utilities and software processes that manage and use aggregates.

Myths About DM

Myth number four is "Snowflaking is an alternative to dimensional modeling." Snowflaking is the removal of low-cardinality textual attributes from dimension tables and the placement of these attributes in "secondary" dimension tables. For instance, a product category can be treated this way and physically removed from the low-level product dimension table. I believe that this method compromises cross-attribute browsing performance and may interfere with the legibility of the database, but I know that some designers are convinced that this is a good approach. Snowflaking is certainly not at odds with dimensional modeling. I regard snowflaking as an embellishment to the cleanliness of the basic dimensional model. I think that a designer can snowflake with a clear conscience if this technique improves user understandability and improves overall performance. The argument that snowflaking helps the maintainability of the dimension table is specious.

Reference: http://www.dbmsmag.com/9708d15.html

Crystal Xcelsius

Crystal Xcelsius is an intuitive stand-alone Windows application, designed to create engaging, interactive visual analytics from ordinary Excel spreadsheets. It is tightly integrated with Microsoft Office products, and requires no programming.

Crystal Xcelsius™ bridges the gap between data analysis and visual presentations, empowering users of all proficiencies to create visually stunning interactive reports and applications.

The Technologies That Drive Crystal Xcelsius:

Crystal Xcelsius™ combines two of the most widely used software applications in the world:
· Microsoft Excel
· Macromedia Flash Player

How It Work:
Crystal Xcelsius uses a simple point-and-click interface to import Excel data and formulas, and output interactive dashboards, charts and graphs, financial presentations, and business calculators directly to PowerPoint, PDF documents and the Web.Utilizing an intuitive and easy-to-use “point-and-click” interface, Crystal Xcelsius converts dull Excel spreadsheets into visual models in 3 simple steps:
Import an existing Excel spreadsheet.
Create a visual data model with point-and-click Crystal Xcelsius
Output your Excel based dashboard to PowerPoint, PDF, Outlook or the Web – with one click!




Components Available:

Following are the available components to create a Visual Data .

1 Charts:
Following collection of charts that can be used on any model.

· Bubble Chart: Despite its apparent complexity, the bubble chart is one of the most powerful analytic tools available. It lets you compare a group of items or series based on three different parameters. It has an X-axis and Y-axis to represent the item location over the chart area, and a Z value to represent the item size. You could, for example, use this chart to represent the market composition with the X-axis representing the ROI by industry type, the Y-axis representing the Cash Flow, and the Z-axis representing the Market Value.

· Line Chart: A single- or multi-line chart ideal for showing tendency over a period of time. Use this chart in models that emphasize a trend line, such as Stock Prices and Revenue History.

· Pie Chart: A chart that represents the distribution or participation of each slice (item) over a certain total that is represented on the overall pie value. The Pie Chart is appropriate for models, such as Revenue Contribution by Product. The overall pie size represents the total revenues. Each slice represents a different product.

· XY Chart: A powerful chart that displays data requiring two magnitudes to complete the analysis. The XY chart shows each data point as a result of the intersection of X values and Y values. You can, for example, use the XY chart in models that compare ROI (X axis) against Market Value (Y axis) for a group of companies.

· Combination Chart: A combination Column and Line chart ideal for displaying a range of values and a trend line for those values.

· Bar Chart and Column Chart: A single- or multi-bar chart ideal for showing and comparing one or more items over a period of time or in a specific range of values.

· Stacked Column Chart and Stacked Bar Chart: A powerful chart that allows you compare several variables over a period of time. Stacked bars allow you to compare one or more variables by adding one variable on top of another. These type of charts are ideal when comparing several variables over a period of time i.e,: Marketing Cost, General Cost, Administrative Cost. Each one of the cost components is presented in a different color and each portion represents a different variable. The total bar size represents the Total Cost.

· Area Chart: A standard chart with vertical and horizontal axes. Each point along the horizontal axis represents a data point. The actual values for each data point are plotted against the vertical axis. For each series, colored areas are created by connecting the plotted points and the horizontal axis. Depending on the situation, the areas can sometimes make analysis of the data more intuitive than without.

· Radar Chart and Filled Radar Chart: A chart with axes that radiate outwards from the center of the chart. Since, these charts can have several axes, they are useful for plotting multi-dimensional sets of data. In the Filled Radar Chart, the shape created by connecting the points along each axis is filled in with color.

· Stacked Area Chart: A standard chart with vertical and horizontal axes. Each point along the horizontal axis represents a data point. The actual values for the data points are plotted against the vertical axis, with each series adding to the total value.


2 Single-Value

This group of components permits user interactivity with other components, such as a chart. These components can be classified in two groups: Output components and Input components.

· Dial: Input Component. Represent a variable that can be modified to affect other components. For example, use a slider to represent the Price per Unit.

· Slider: Input Component. Represent a variable that can be modified to affect other components. For example, use a slider to represent the Price per Unit.

· Progress Bar: Output Component. Represent a value that changes and fills the progress bar area depending on its value.

· Gauge: Output Component. The Gauge measures the result of changes in the Excel cell to which it is linked. If you link the Gauge to a cell that contains a formula, the Gauge reflects the modifications each time the value changes.

· Value: The Value component represents a single cell of your Excel file.

· Spinner: Input Component. Represent a variable that can be modified to affect other components. Users can interact with the Spinner by clicking on the up and down arrows or by typing a value into the text box. Pressing the up arrow increases the value and pressing the down arrow decreases the value.

· Play Button: Input Component. Use the play button to increase the value of a cell in your model. For example, link the Play Button to a cell that contains the headcount. What will happen if the headcount increases by one, two, three, or more? The play button takes the initial headcount value and increases its value systematically.

3 Selectors

Selectors are probably the most powerful components of all. Their overall functionality lets you create a model with multiple selections. When you combine a selector with any other component such as charts, the result is a powerful dynamic model that is elegant and easy to create.

· Combo Box: A standard user-interface component that drops down a vertical list of items when it is pressed. Users can then select an item from the list in the same manner as the List Box

· List Box: A standard user-interface component that allows users to select items from a vertical list.

· Label Based Menu: The Menu component allows users to select items from either a vertical or horizontal list.

· Radio Button, Menu, Combo Box & List Box: These options are some of the industry favorites. They permit one selection at a time from a group of selectable items. They are easy to understand and use.

· Fish-Eye Picture Menu: The Fish-Eye Picture menu is a visually engaging menu that allows users to select from a set of pictures or icons. As the mouse is moved over each item in the menu, the item emphasized by being magnified. The closer the mouse is to the center of the item, the more the item is magnified. This creates an effect similar to that of a fish-eye lens.

· Sliding Picture Menu: The Sliding Picture Menu is a visually engaging menu that allows users to select from a set of icons or pictures. The user scans use arrows to scroll through the icons or the menu can be configured to scroll through the items as the user moves the mouse.

The Sliding Picture Menu has a unique visualization, but it has all of the behavior of the traditional menu component. With each selection that is made, data corresponding to that selection is inserted into a range in the spreadsheet. All of the menu components are used to provide navigation for your model.

· Table: The Table component is a "What you see is what you get" representation of any group of cells from your Excel file. Each row allows multiple selections. For example, you can use the table to display a Balance Sheet for two years. When you click on each row, the table could display an additional chart that shows the historic balance for each account.

· Check Box: A standard user-interface component that allows users to toggle between two states: checked and unchecked.

· Icon: The Icon is one of the most simple and at the same time most functional components. The Icon can be used as a mobile selector or object. It can represent the value contained in one cell and be compared to its target or budget value. The Icon changes color depending on its value.

· Toggle Button: A standard user-interface component that allows users to toggle between two states: on and off.

· Filter: The Filter component takes a range of data that shares a common number of items and "filters" the content so that there are no duplicated records.

· Accordion Menu: The Accordion menu is a two-level menu that allows users to first select a Category and then select from items within that particular Category.

· Source Data Component: A special component that has no visual interface. It allows users to push data into other cells simply by changing the value of the component's Selected Index.

· Play Selector: The play selector sequentially inserts one row or column from a defined range. You can then use that row to create a chart that changes each time the play selector inserts a row or column. You might use the play selector, for example, in models that show sales by a large number of sales representatives. Instead of having to click several times to display a chart for each sales representative, you can create a model that automatically runs the charts with a single click.

4 Maps Library

This group of components provides specialized selector functionality in the form of maps. Maps are graphic representation of an area can be used as both an output and input component. Each region can represent an output value while at the same time having the same functionality as any other selector.

5 Art & Backgrounds

Art and Backgrounds can be used to enhance models. You can also use the background components to import images or Flash movies into your Crystal Xcelsius™ model.

· Image Component: The Image Component can be used to display JPG images, which allows you to add your own logos or artwork into a Crystal Xcelsius™ model easily. You can also add video, animations, and other interactive elements by importing a Flash movie.

· Ellipse: A simple ellipse that you can add to your models.

· Background: Backgrounds are a set of prebuilt artwork that you can add to your models to assist layout and improve design. They provide a way to create cohesive, visually stimulating models quickly and easily.

· Line: A simple vertical or horizontal line can enhance your models.

· Rectangle: A simple rectangle that you can add to your models.

6 Other

This section contains a set of components that you can use to enhance models.

· Trend Icon: The trend Icon changes its pointing direction, depending on the value of the cell to which it is linked:
o If the value is positive, the arrow points up;
o If the value is zero, the arrow is neutral;
o If the value is negative, the arrow points down.

· Interactive Calendar: The Interactive Calendar component is a selector that lets you incorporate date selection into your models.

· Local Scenario Buttons: Lets users save states of a Crystal Xcelsius™ model to their local machines. Afterwards, these states or "scenarios" can quickly be loaded, even after closing the model, making the Scenario Buttons well-suited for saving and reviewing results of what-if analyses. Since these scenarios are saved to the local machine, the scenarios will not be available if the model is opened on a different machine.

· Grid: A dynamic table that represents a group of rows and columns to dynamically display its content. The Grid lets you show the data just as it is on any table, or permits data modification that can impact other components.

7 Text

This section contains a set of components for labeling components in your model or entering text in the model.

· The Input Text component is a simple, yet powerful input component which allows users to input values into the model by typing.

· The Input Text Area, similar to the Input Text component, allows users to input text into the model. The Input Text Area, however, allows users to type multiple lines of text, making it useful for comments or entries where the size of the input is unknown.

· The Label component lets you add an unlimited amount of text to enhance your models. You can use the Label component to create titles, subtitles, explanations, help, and so on.

8 Web Connectivity

This section contains a group of components that provide various connectivity options for your models

Benefits:

Crystal Xcelsius uses visual modeling to bridge the gap between data analysis and visual presentation, so you can present and interact with business data in ways that were previously unachievable. Crystal Xcelsius gives you the power to create interactive and audience-friendly:

Dashboards and scorecards
Dynamic charts and graphs
Visual analytics
Pro forma budget consoles
Business presentations

Then, you can easily communicate your results to colleagues, with Crystal Xcelsius’ one-click integration of your interactive data visualizations and models into PowerPoint slides, PDF documents, Outlook and the Web.
The result: faster, more intelligent business decisions that let you capture lucrative market opportunities before your competitors even realize they exist.
Point-and-click ease. Crystal Xcelsius' intuitive interface, including pre-built components, skins, maps and charts – and one-click export – make it easy for non-technical users to develop.

Stunning presentation. Custom graphics and interactivity allow you to develop and present compelling yet easy-to-understand financial models and business summaries for the Web, intranets, and portals – any environment that supports Flash.Visual modeling. Sliders, gauges, filters, numerical input tools and other visual components allow you to quickly evaluate multiple “what-if” scenarios, with the click of a mouse.

Platform Independence Crystal Xcelsius visual models can be exported - with one click - to PowerPoint, PDF, Outlook and the Web. They can run on any PC, Mac, handheld or other device with the Macromedia Flash player.


References:

Taking references from following the above document is created.

1. Business Objects Site – www.businessobjects.com
2. Help of Crystal Xcelsius.