What Is Event Studio?
Event Studio is a component of Cognos 8 Business Intelligence. It allows users to keep track of events that they consider important. An event can be any change in data; for example when a metric you have been watching falls below acceptable levels.
It does this by allowing you to create agents to monitor events you are interested in. there is no limit to how many events you can set up. Agents can be scheduled to run at any time or frequency.
A task can be set up to do a number of actions, including send an email, run a report or job (ie several actions) or update a database.
Each agent has a subscription list of users who will be notified when the agent is activated.
What Is Analyis Studio?
Analysis Studio is a drill-up drill-down product that allows you to examine and analyse large amounts of data. From this reports can be created and saved to be run in the future. Reports can be output in various formats including HTML, PDF, Excel and text files.
Analysis Studio works with dimensional data, which is data organised into hierarchies and levels. You can filter data, swap rows and columns and sort rows and columns. Data can be shown in a grid as shown above (known as a crosstab) or in a number of chart formats. All the main chart types, such as line, bar and pie charts, are supported. Read more
What Is Cognos Data Manager?
Cognos Data Manager uses Jobstreams to build an automated data mart.
Cognos Data Manager is an ETL (extract, transformation and load) product. Its transforms your data, typically from a transactional database to a data warehouse. Data Manager is particularly optimised to produce ‘star schema’ dimensional data marts, although it can quite happily handle other types.
Data Manager makes it easy to merge data from a variety of different data sources, for example Microsoft SQL Server, Oracle 9i and ‘comma delimited’ files can happily be merged in a single build. Read more
What Is Framework Manager?
Framework Manager stores all sorts of information about your data in a Framework model. This process is known as metadata modelling. Using Framework Manager items from several database tables can be collected into a single item (known as a query subject), given user-friendly and meaningful names and then ‘published’ to Cognos Connection from where users and report developers can use it to produce reports and analyses. A test function allows the modeller to see how the data will look when it is published.
Once information such as joins and calculations is published it can be used again and again, with no risk of getting the joins wrong. Potential reporting errors known as ‘reporting traps’ can be resolved in the Framework model to avoid these errors being made in reports or other data analysis. Read more
What Is Cognos Report Studio?
Cognos Report Studio is a web-based report writing tool. Like all Cognos 8 products it’s scaleability is unlimited, in other words you can run it with as many servers, processors and whatnot as you like.
All development takes place online, using a standard web browser. Reports are written in XML – an enhancement of the universal web page mark-up language HTML. This means report users don’t have to install any software on their desktops except a web browser such as Microsoft Internet Explorer. Report specifications are held in a database known as the Content Store. Read more
Deploying A Data Warehouse
Once the data warehouse has been designed, built and tested it needs to be deployed so it is available to the user community. This process is also known as ‘roll-out’. This can vary in size from a single-server local deployment (deployed across one country or one location) to a global distributed network involving several time zones and translating data into many different languages. Read more
Building A Data Warehouse
The building of a data warehouse is known as extract, transformation, load (ETL for short).
First we extract the data from its various data sources. These will usually be database tables from various systems within an organisation, but are often supplemented by other files such as spreadsheets and text files. Read more
Data Warehousing Design
Most data architects design and build star-schema data warehouses following the popular Kimball methodology. A data warehouse is essentially a database and is a combination of two sorts of database tables, ‘fact’ tables and ‘dimension’ tables.
Fact tables – a ‘fact’ table records the measures that are included in the data warehouse, for example quantity sold, revenue etc.
Dimension tables – these contain descriptive information. An example of a dimension table might be called D_Products and contain a list of product codes and descriptions.
A Star Schema
A simple star schema is shown above. There is only one table join between each table, which speeds up processing when using it for reports and analyses. Note that only the details of the actual sale are held in the sales fact table F_Sales. Product details, customer details and the date are held in separate tables.
Multi-layered design. The star schema warehouse is built in three layers, each of which is usually a database. The staging layer holds the raw data as it comes in, for example a spreadsheet containing product details would be stored in a table in the staging layer.
The enterprise layer is the place where things are added to the data, for example revenue might be calculated by multiplying quantity sold and price.
Finally, in the presentation layer the dimension and fact tables the end users have access to are created (see diagram above).
Data Marts. A data warehouse usually has more than one fact table. Each fact table will then have its own star schema (but they usually overlap) and each is called a data mart. So one or more data marts go to make a single data warehouse. A large corporation may have only one data warehouse, but this will include many data marts.
Requirements Gathering
It needs to be remembered that business requirements are the reason you wanted to build a data warehouse in the first place. These requirements will affect what goes into the data warehouse, its organisation and how often it is refreshed.
Hopefully you’ll already have some idea of the project scope before you get round to requirements gathering. Someone must be sponsoring the project, and to get any funding at all must have defined some boundaries around it. From here we can begin to ‘flesh out’ the project and discover what is, and isn’t possible.
There are various techniques involved in requirements gathering:
- interviewing the user community to find out what their needs and expectations are. Often this involves translating their business needs into data needs IT departments can deal with
- interviewing the IT community to (start to) find out what is likely to be involved in gathering the information required by the user community. Is the data available at all? Often the data required is available, but not in the organisation’s main operational systems. Is the data reliable?
- Organising meetings involving reporesentatives of the users and the IT community
What to gather. In building a data warehouse we are aiming to collect all the information we are going to need for reporting purposes. Sometimes users know exactly what they want to see in their reports – often they don’t, so we need to find out what the organisation’s success metrics are. We need to know how they are getting these at present (if they are at all) before we can make a judgement on whether the new solution is better than the existing one.
Scheduled or ad-hoc reporting? So far we have mostly talked about data requirements. But we also need to look at reporting requirements. Usually some reports will be ‘canned’ – run by a scheduler and perhaps delivered by email. But a lot can also be gained from ‘ad-hoc’ reporting, analysing data in a drill-up/drill-down manner or data mining. These considerations will need a different approach to scheduled reporting and will need to be taken into account during the requirements-gathering exercise.
What Is Data Warehousing?
A data warehouse is a database that has been specially designed for reporting. Data is collected from various different sources – mostly the operational systems – then reorganised in a way that enables quick reporting.
“What’s wrong with the database we already have?”. This is usually the first question organisations ask, and it’s the right question to ask. After all, why go to the time and expense of creating a database just for reporting, when the data you need is already stored electronically? There has to be a good reason to do this. And there is, several in fact.
Firstly, there’s speed. In a system with many transactions the database is optimised to write the data to the database as quickly as possible. This usually uses an organisation known as ‘normalised’. However, to get the data out quickly you need exactly the opposite, known as ‘denormalised’ data. What this means is that, to query the data you often need to look at several tables at once, and that’s inefficient.
Also related to speed, there’s the problem of several users trying to query the data at the same time as others are trying to put data in, which can slow the systems down considerably.
Variety of data sources. Another reason companies build data warehouses might be termed ‘accuracy’ or ‘completeness’. Having defined your KPIs or your reporting needs, you might find the data for this is in several places, not just the organisation’s main operational system. You may need some data from legacy systems, for example, or certain information vital to KPIs is only held on a spreadsheet. All of these sources can be combined in a data warehouse build whereas it would usually be against company rules – or illegal – to modify the operational systems to deal with this.
Additional calculations. It may be necessary to include calculations in your data, and these can easily be added to a data warehouse.
Keeping history. Finally, data warehouses allow you to store a history that often is not otherwise available.
All of the above are good reasons why organisations find it useful to build and maintain a data warehouse. A typical data warehouse is updated daily – usually overnight, but this need not always be the case. It all depends on the business requirements that you built it for.