Altius Community

The HMIC Police Performance website goes live

Kate Rounding — Sat, 13 Mar 2010 11:41:00 GMT

Her Majesty’s Inspectorate of Constabulary (HMIC) has today launched the new police performance website MyPolice.org.uk.

The public facing website which had been designed and implemented by Altius Consulting provides HMIC with the ability to efficiently publish and maintain useful information about police performance, thus providing greater public transparency.

The new site provides the public with the ability to view local police performance information. Crime statistics are available at both local and national level, allowing the public to see how statistics within their borough compare to the national average.

The website can be viewed at www.mypolice.org.uk

Automatically extracting data from PDF files into a database

GlenChambers — Fri, 12 Mar 2010 21:57:57 GMT

Extracting value from unstructured data files has always been a tricky task with no obvious solution. Any attempts to design a custom solution would undoubtedly take more time than it was practical or worthwhile to do and involve a large amount of hard coding. Any implemented solution would have to be flexible and cope with frequently changing source files and be quick to implement, enter Informatica PowerCenter and the Unstructured Data Option.

The Challenge
Our oil & gas client received dozens of drilling reports each month in multiple formats and were manually extracting the key data they required manually by hand. The data stored within the data files was used for three main purposes:

Monitoring operating performance;
Accurately determining when new wells came online and started producing; and;
Tracking and controlling spend.

Despite the plethora of information available in the reports only a small subset was being recorded because to the high overhead associated with capturing the data. In mid-2009, due to an acquisition, the volume of drilling reports received monthly by our client increased dramatically to many thousands per month. Clearly it was not practical to continue capturing the data by hand and an alternative approach was required.

The Solution
After examining the available options, of which there were few, we advised our client to invest in Informatica PowerCenter with the Unstructured Data Option. This product gave us the ability to seamlessly access, discover, and integrate data from virtually any business system, in any format, including data locked in documents and industry-specific data formats. The ability to access and process any data format increased the teams productivity and saved costs by eliminating the need for manual intervention and improved business responsiveness and agility in their competitive market.

In our solution we implemented the following process to import the data for reporting and further analysis:

Various multi-format PDF documents are received into a Windows file system folder
(As an alternative to the Windows folder it is possible to extract file straight from an Exchange Mailbox)
The folder regularly parsed for new files, processes each file and extracts the data into a SQL Server database
SQL Server Reporting Services (SSRS) is used to provide various on-demand reports

This solution solved the immediate needs of the business and from here many further improvements can be made. The key point is that once the data is in database it can be used in many different ways to provide value to the business.

For more information on the Informatica PowerCenter Unstructured Data Option review this datasheet:
http://www.informatica.com/INFA_Resources/ds_unstructured_data_6668.pdf

Customer Risk Assessment

JohnGamble — Thu, 11 Mar 2010 22:13:00 GMT

Last week the Jersey Financial Services Commission (JFSC) published a report on findings into it’s Anti-Money Laundering examination of Trust Company Business in Jersey. Under its regulations the JFSC requires Trust Companies to prepare business risk and assessment strategies for all customers by capturing “sufficient information about a customer that will allow it to develop a profile of expected activity sufficient to provide a basis for recognising unusual activity and transactions, and identify higher risk activity or transactions which may indicate money laundering or financing of terrorism.” The report was based on a series of on-site assessments of some of the Trust firms in Jersey.

You can read the report here, but some of the findings of the report were interesting:

Situations were encountered where information used to produce a customer profile was held by the business, but held in disparate places and not summarised in one central area;
Information not stored in a way that “facilitates periodic updating of the information”; and
In cases there was a failure to put in place a mechanism to monitor transactions and compare payments and receipts to customer profiles on a regular basis.

A further point was an acknowledgement that changes constantly take place within an organisation’s customer base and Directors should have the ability to “step back and take an objective view of the business which may be lost sight of during day to day ‘business as usual’”. The report states that whilst there is no specific requirement to periodically review an organisations risk assessment, customer bases do change and having sight of this change and how it affects your risk assessment is valuable in the Anti-Money Laundering (AML) arena.

All of the above is at the heart of Business Intelligence (BI). Business Intelligence tools and solutions specialise in collecting data from many disparate sources. BI tools enable Directors or anyone within an organisation to take a step back and take a view of the business from multiple perspectives. With BI tools people are empowered to make informed decisions. Data Warehouses collect and preserve changes in source systems.

In fact all of the information required for an accurate risk assessment just falls out of a data warehouse. You may have all of the information about your customers in many disparate sources but a warehouse is the ideal place to gather it all together to provide you with a consistent, cleansed, conformed, single view of each of your customers. From there algorithms can be built to periodically profile and assess each customer. A well designed, regularly updated, warehouse, will track changes and report on what was as well as what is. It will provide people with the ability to see how a customer base has changed over time, be it via deposits, geography, volumes or complex calculations such as risk assessments. If the warehouse contains them transactions then they can also be monitored in a easy and efficient manner.

Risk assessment is not an easy task but if your considering a technology based solution for risk assessment, think about the role a data warehouse might play. Having built multiple risk assessment systems for financial services firms, it’s much easier task by including a data warehouse as part of the overall architecture. The warehouse answers all of the points made above and through the ability to combine your risk assessments with other customer information such as wealth and demographics can provide valuable insights into your customers.

Further, non-BI, findings on the report can be found on the AML 360 website here.

What Know Your Customer (KYC) is really about

JohnGamble — Thu, 11 Mar 2010 22:11:09 GMT

One of the main items I’ve been working on this year is a regulatory compliance project based around the ability to Know Your Customer (KYC). In order to try to reduce fraud and risk, banks and other financial institutions have a requirement to keep up to date records of documents which prove the customer is who they say they are. If you’ve opened a bank account recently you’ll have been asked to fill in a legally binding application form and supply copies of passports, a proof of address and possibly a statement showing your evidence of income such as a wage slip or pension statement.

The problem is that people move house, jobs, and make other changes in their lives which makes these details out-of-date and it’s the banks responsibility to keep their records up to date (i.e. they have to request and store a copy of your new proof of address). As mentioned before this is a regulatory requirement for the banks and if you don’t comply the banks can, with enough warning, put blocks on your accounts preventing you from using them.

At the moment this doesn’t sound too hard does it? A customer phones in says “I’ve got married”, gives their new surname which is diligently recorded and records updated. The operator recording the change, may say “congratulations, we’ll need to see a copy of your new passport to confirm the name change.” The customer will promise to send it in. The operator will move on to the next call. All is well. Or is it? With a couple of customers this is fine, but when you have tens or hundreds of thousands of customers you will get a lot of changes and the operator who took the initial call will not have time to ensure the copy of the new passport actually arrived. If you think of each change as a “trigger event” which causes a document collection process to start, then this is an ongoing process which never really ends. This process may require a dedicated team to administer the trigger events, ensure that the copy of the new passport actually arrives and document records are kept up to date. Before long you will be weighed down with data because an effective KYC process requires that you collect:

Data detailing changes to the customer records;
Data detailing contact with the customers (when did you last speak / write to them);
Data detailing documents requested and received;
Data detailing any special instructions for a customer;
Data detailing all individuals attached to a customer record; and
Data providing a full audit trail for each customer and trigger.

Each trigger event will produce it’s own story. Some customers will be happy to send in documents and will do so straight away. Others will resist, forget, delay, complain, be hard of hearing, etc. and to provide good customer service you will need a complete list of all contact to and from each customer along with special instructions for them. This is important because your KYC team will change, and multiple people will communicate with each customer. Each new team member must be able to pick up each customer story and carry it on. It’s also important that your customer advisor or relationship managers (RM) can see this contact information, especially when dealing with High Net Worth clients. This sounds like CRM territory doesn’t it?

Your team also needs to know which customers need to chased, called back, sent a second or third letter asking for the data, which accounts to block due to non-compliance. Producing these letter lists in mail-merge formats is a huge time saver. Your KYC customer details also need to be kept up to date. If, for example, a block has been put on a customer on the banking platform then that must be updated on the KYC application. Likewise other details such as customer names must be kept up to date. You can also bet that tonnes of Management Information will also be requested.

Multiple Systems
Few banking platforms, that I have seen, are set up for KYC management. Your banking platform will hold all the records of customer details and account activities that a customer can change with a phone call, online, or by email, but probably won’t generate triggers events. Banking platforms also tend to be strategic systems within the organisation and developing them is not straightforward. You’ll need something else to detect changes in customer details and report on them. You may also need a second system to track all the contact, documents requested and received and hold those special instructions which are so vital to good customer service.

So what you need then is a system which tracks changes and is capable of bringing together the customers details along with the contact made to them and KYC documents. In my opinion the data warehouse is perfect for this. Even if it too is a strategic system, it will be designed to track changes, collect data from multiple sources and be flexible in it’s reporting capabilities. Business Intelligence tools will give you the insights you need to effectively manage the remediation process. KYC, customer contact and financial data when combined from the different sources will give you very powerful reporting capabilities and insights into your customers.

As you can see this is all about data. Yes you need to contact each customer whose new documents you need, but the key to making this a successful and effective process is accurate record keeping, audit trails and data management. A database operating behind the scenes is crucial.

TSQL tip – Avoid Increasing Row Counts in Aggregate Joins

JohnGamble — Mon, 08 Mar 2010 22:02:00 GMT

A typical function required of many data warehouses is the ability to do currency conversions. This is especially true in the Finance industry where you will probably have accounts in multiple currencies, but for reporting purposes you will probably want to be able to view them in a single consistent currency. Aggregating all your account balances in currency simple doesn’t make any sense. You’ll probably also find that for the same currency on the same day different exchange rates will apply for different reporting purposes. For example, for the purposes of accuracy you may wish to look at all your Euro currency balances for a particular day converted at that day’s mid-position rate. This could give you an overall liability picture when combined with your GBP accounts. However, if you want to see how balances have moved over time due to customer activity, then you will need to eliminate any exchange rate gain or loss and you do this by using a consistent exchange rate.

Recently we came across a currency conversion function that was performing very slowly. The function ran during the night so it wasn’t causing any issues, but was still confusing us as to why it was so slow. The function was a “burst view” and used to combine the exchange rate fact table with the date dimension to show for any date the exchange rate in use. In this system one set of exchange rates is supplied monthly and applies for the whole month. For space reasons then this is stored as in a single table with “from” and “to” dates:

CurrencyKey   INT
CurrencyName CHAR(3)
Rate          NUMERIC(14,4)
RateFromDate INT
RateToDate    INT

For the conversions the application logic also required us to increment the “From” and “To” Date to the next working day. Without going into too much detail the query to “burst” the table for every day working day looked broadly like:

SELECT
       fx.CurrencyName,
       fx.Rate,
       MIN(fromDate.PK_Date) as FromDateNextDay,
       MIN(toDate.PK_Date) as ToDateNextDay
FROM
(
       SELECT
               CurrencyName
              ,Rate
              ,RateFromDate
              ,RateToDate
       FROM dbo.F_ExchangeRates ex
) fx
INNER JOIN olap.D_Date fromDate
ON -- conversions are applied to day plus 1
       fx.RateFromDate < fromDate.PK_Date
LEFT JOIN olap.D_Date toDate
ON
       fx.RateToDate < toDate.PK_date
GROUP BY
       fx.CCY,
       fx.Rate

This was taking about 20 minutes to run and the query execution plan which was odd given that we only had 700 rows in the currency table and 7000 (20 years) in the date table. The query execution plan showed that the problem lay in the LEFT JOIN, in fact removing the LEFT JOIN caused the query to return instantly.

After trying a number of scenarios (including one which gave us a cartesian product – don’t try joining on a column created by a UDF!), we came to the conclusion that the SQL query execution plan might perform better if it handled the JOIN clauses one at a time. This produced the following query:

SELECT
       fx.CurrencyName,
       fx.Rate,
       fx.FromDateNextDay,
       MIN(toDate.PK_Date) as ToDateNextDay
FROM (
       SELECT
              fx.CurrencyName,
              fx.Rate,
              MIN(fromDate.PK_Date) as FromDateNextDay
       FROM
       (
              SELECT
                      CurrencyName
                     ,Rate
                     ,RateFromDate
                     ,RateToDate
              FROM dbo.F_ExchangeRates ex
       ) fx
       INNER JOIN olap.D_Date fromDate
       ON -- conversions are applied to day plus 1
              fx.RateFromDate < fromDate.PK_Date
       GROUP BY
              fx.CCY,
              fx.Rate
) fx
LEFT JOIN olap.D_Date toDate
ON
       fx.RateToDate < toDate.PK_date
GROUP BY
       fx.CCY,
       fx.Rate,
       fx.FromDateNextDay

This second query returned almost instantly. So what is going on here? Well the execution plan for the query shows us that 86% of the execution time is down to the LEFT JOIN and looking at the stats shows us that there are an estimated 3 billion rows to evaluate. See below:

How can this be when we’ve only got 700 exchange rates? The INNER JOIN is producing 1.9 million rows and that is being fed into the LEFT OUTER JOIN. That is combined with another copy of the date table and producing the massive result set, and it is on this that the MIN() functions are being evaluated. The database engine is considering all possible row combinations.

The second query has a different execution plan and the MIN values are evaluated one at a time in the nested SQL. The first one is evaluated and the result set reduced down, then the second JOIN evaluated. The result, only 7 million rows considered.

It is worth noting that the first INNER JOIN produces 1.9 million rows in both queries.

The table below sets shows how the execution plan row count grows over time for both queries:

Query 1

Command	Estimated Rows
NESTED SELECT on Exchange Rates	1,159
SELECT ON DATE TABLE	2,861
INNER JOIN WITH DATE TABLE	1,905,730
LEFT JOIN WITH SECOND DATE TABLE	3,078,410,000 !!!!

Query 2

Command	Estimated Rows
NESTED SELECT on Exchange Rates	1,159
SELECT ON DATE TABLE	2,861
INNER JOIN WITH DATE TABLE	1,905,730
Aggregate MIN Function Performed	4,365
LEFT JOIN WITH SECOND DATE TABLE	7,052,140

I can’t work out the math of how the 300 million row result set is being computed. I have 700 exchange rates and 7500 dates. If you know please feel free to get in touch and let me know.

So when using aggregate functions across data joined from multiple tables consider doing them one at a time rather than all at once. With even small amounts of data in your fact tables you may find that your query slows considerably.

PS. Thanks are due to Jake for helping work out why this was happening.

Achieve Business Intelligence Project Success with Executive Sponsorship

NoelPhillips — Fri, 05 Mar 2010 15:16:38 GMT

At any given time, dozens of project teams will be fighting for recognition, the enterprise's budget dollars, and wide-spread user adoption. Having an executive on the Business Intelligence (BI) implementation team will ensure that the project is not stalled, under-funded, or ignored by the enterprise. In a September, 2008 survey on Business Intelligence, 42% of respondents cited gaining executive buy-in as a major challenge in the implementation. Learn how to prove to the executives that the BI initiative is worth their commitment and support.

What Executive Level Support Will Do For the BI Project

Significant benefits are derived from the presence of executive support for a BI project. Overall, the day-to day experiences of the BI project team will be exponentially easier and fundamentally different with executive buy-in. Some benefits include the following:

Political immunity. BI implementations are often done on a large-scale that can cross physical offices and oceans. Consequently, political battles ensue in terms of how the business will operate. The BI team can use its executive team member as the trump card to ensure that politics don't get in the way of project success.
Control of the mole. There will be business decisions throughout the BI project that need the approval of executive management. A C-Level on the BI team offers an equal voice in these situations to advocate for important issues and expedite decisions to help keep the project on time.
Access to staffing resources. BI solutions often affect a number of users across the enterprise. It is important for the BI implementation team to gather requirements and feedback from all stakeholder groups. Executives enforce project participation which increases the probability of overall project success.
Funding. By having true buy-in to the BI project, the executive sponsor will better understand the inevitable hiccups and budget overages. They are the team's best asset in justifying the additional funding necessary for a successful BI implementation and subsequent ongoing operations and upgrades.

Recommendations

Understand the executive need. Every level of the organization has its own set of needs. The first step in gaining executive support is to uncover which strategic priorities are on the organizational agenda and tailor the executive pitch accordingly. For example, if the enterprise is fighting to cut marketing costs and maintain market share, cite how a BI solution can improve the accuracy of targeted promotions.
Speak their language. Spend the time up-front to do a total cost of ownership (TCO) calculation. Ensure that all assumptions are explicitly stated and can be defended. Every executive hopes to boast that his or her project saved company dollars.
Invite the executive to sit through a proof of concept (POC). Seeing is believing. The benefits of BI may not be clear to the executive unless he or she can actually see how the solution works with the enterprise's data and existing infrastructure. If it is impossible to find time for the POC that fits into both the vendors' and the exec's schedules, ask the vendor if the BI team would be able to replicate the presentation for the executive at a later date.
Choose wisely. Select an executive sponsor that will be most affected by the benefits and value produced by the BI project. Even better, find someone responsible for a critical business initiative. For example, if the VP of Sales is under pressure to drive profit, explain that a BI solution will allow sales managers to spend less time analyzing reports and more time working on sales force effectiveness and managing customer demands.
Prepare a business case. Don't leave any opportunity for unanswered questions. Think like an executive by focusing on how BI will affect the enterprise's operational strengths, weaknesses, opportunities and threats. Put together a report that outlines everything from resources required and the vendor selection process to expected benefits and future plans.

Bottom Line

Executive-level support increases the probability of success in a BI project by keeping the enterprise focused on the BI initiative. Ensure there is at least one enterprise change agent on the BI team to avoid potential pitfalls.

Changed priorities for the 2010 CFO – Business Intelligence moves to top of the agenda

GlenChambers — Fri, 05 Mar 2010 14:40:00 GMT

IBM have just released their 2010 study of over 1,900 Chief Financial Officers (CFOs) and senior finance executives from across the globe and the verdict is that “Cost reduction matters to today's CFO, but not as much as getting stuck in to wider corporate business decisions. Playing their part in the wider corporate strategy is now the top priority for CFOs who no longer rank cost reduction at the top of their agenda.”

"Their priorities for the next three years were to cut the enterprise cost base, make decisions faster and provide more transparency to external stakeholders."

That statement makes a refreshing change given the current economic climate, it indicates that CFOs are starting to think about gearing for growth and I believe that Business Intelligence (BI) and Performance Management (PM) are core to delivering each of those priorities:

Cut the enterprise cost base

With a comprehensive and organization wide reporting strategy CFOs can easily identify areas of the business that are not performing and becoming a drain on resources. A well designed BI solution can reveal and explain why one area of the business is most efficient than another and then steps can be taken to reduce cost. Subsequently effective budgeting, planning and forecasting can then control the cost reduction efforts.

Make faster decisions

To make not only faster decisions but better and more informed ones it is critical that people at all levels of the business are provided with accurate and timely information in a format that is easy to access. Portal based reporting, dashboard and scorecard solutions can provide part of that answer; they enables up-to-date information to be shared so everyone is working from the same ‘one version of the truth’. Automating business processes can also prove invaluable, improving process control and accuracy and slashing effort and time requirements.

Provide more transparency

If you can measure it then you can report and manage it. Saying “I don’t know” just doesn’t cut it these days, people want answers, they want they quickly and on their terms. Providing those answers provides external stakeholders with the confidence that things are going well or helps identify potential issues early enough to mitigate them before they have a chance to develop.

It is often said that Information is Power and for CFO nothing could be truer. Without the facts they simply cannot be expected to perform their role effectively and BI and PM play a pivotal role in providing them. Strategic decision making is no small matter and requires a well integrated and comprehensive data capture, reporting and analysis environment. The good news is that one thing that CFOs can’t complain about these days is a lack of quality tools to provide the solution.

The quotes in this post were taken from the Finance Week website that delivers topical, practical content to senior finance professionals in UK industry: http://www.financeweek.co.uk/topic/career-ladder/changed-priorities-2010-cfo/32194

To Dashboard or Scorecard, that Is the Question

GlenChambers — Thu, 04 Mar 2010 22:52:28 GMT

Both dashboards and scorecards are mechanisms for visually representing complex enterprise data which provides insight into business performance. The data types include summaries of key performance indicators (e.g. sales data) and other measures of business performance (e.g. number of customer service calls).

The distinction between these two reporting mechanisms has become increasingly blurry. The terms are often used interchangeably, reflecting a widespread belief by industry professionals that they are the same thing. Although they have a great deal in common, there are good reasons for keeping a clear distinction between the two. In particular, they embody two distinct approaches to the communication of enterprise data, each with its own virtues.

Dashboards: Providing Current Insight on Business Operations

A dashboard is an interactive user interface designed to deliver user-specific information relating to the health of the business. They use various types of visual representation techniques (e.g. graphs, dials, speedometers, stoplights) and process controls to focus user attention on important trends, changes, and exceptions.

The purpose of a dashboard is to allow for an immediate understanding of the state of the business. The data is typically displayed in real time allowing immediate response to changes in business activities. In this way, dashboards allow for tactical (as opposed to strategic) decision making. Dashboard data tracks key performance indicators (KPIs) to monitor the vital signs of the business. They have drill down capability from summary data to underlying reports, enabling users to investigate the root cause of problems or changes.

Scorecards: Measuring Strategic Business Performance

Scorecards (or a Balanced Scorecard) also provide insight on business operations but involve a much more structured approach. They are designed with the aim of measuring performance relative to a specific set of business goals and strategies. Rather than provide a snapshot of the business, they track business activities as trends over time to inform the business strategy. Scorecards need to be supported by a management process that defines actionable results-oriented tasks (see Table 1 for the main differences between dashboards and scorecards). They can be used to monitor and assess performance at the individual, departmental, and enterprise level. Industry performance benchmarks and risk factors can also be incorporated into scorecards.

Visual representations of information are organized in such a way (e.g. by individual, department, and business unit) to ensure a focus on business goals. Information is typically not presented in real time. Summary data usually funnels vast quantities of enterprise data over larger intervals and measures against baselines (e.g. last year) and benchmarks (e.g. industry performance). They can be tied into communication systems (e.g. email) to send out alerts when results are not in the expected range or when actions/initiatives are overdue.

Table 1. Comparing Dashboards and Scorecards

Dashboard	Scorecard
Tactical – focused on short-term decision making. Provides a snapshot of business performance. Operationally focused and supported by individual management expertise. Changes in performance are evaluated by the decision maker.	Strategic – focused on long-term decision making. Represents trends/changes in business activity over time. Supported by a clearly defined management strategy. Changes in performance are measured against business goals.

Examples of Dashboard and Scorecard Metrics

The differences between dashboards and scorecards are best demonstrated by looking at real examples of the kind of metrics that would occur in each. Consider a manager responsible for calls at a large customer support call center. Whether the manager needs a dashboard or scorecard depends on what they want to do with the data they collect – the management decisions (operational or strategic) they want to inform.

A dashboard can be used to monitor and manage the current activities of the call center. For example, by looking at the total number of calls and the total number of staff, management can make an operational decision to reduce or increase staff to accommodate call volumes. See Table 2 for comparative examples of dashboard and scorecard metrics for a call center.

A scorecard for a call center might have the strategic target of reducing resolution time to four minutes to improve customer satisfaction while reducing cost. Call data can then be recorded and assessed relative to this goal. When performance falls short, management can execute a pre-defined management strategy for dealing with missed targets (e.g. identifying individuals with low call resolution rates and re-training or disciplining them).

Table 2. Examples of Dashboard and Scorecard Metrics for a Call Center

Dashboard Metrics	Scorecard Metrics
Number of outbound calls in progress vs. number of individuals currently on the phones. Number of calls exceeding five minutes in duration. Number of dropped calls over the last hour. Number of calls resolved vs. number of calls made.	Average duration of calls with successful resolution. Average duration of calls with unsuccessful resolution. Number of successful vs. unsuccessful resolution calls over a period (monthly/quarterly). Number of customer complaints over a given period. Counts and comparisons of most frequent customer complaints.

Key Takeaways

The decision whether to deploy a dashboard or a scorecard should depend on the specific business needs of the organization. Keep the following in mind when considering the choice between a dashboard and a scorecard:

Dashboards support tactical decision making and scorecards are strategic.
Dashboards monitor real-time business performance to inform operational decision making. Scorecards always monitor business performance relative to a specific objective and over larger periods of time to inform strategic management.
Dashboards provide business snapshots and scorecards analyze performance trends.
Because dashboards and scorecards support different types of decision making, they measure and summarize different kinds of data. Dashboards aim to provide real-time operational data, while scorecards record data over periods of time and plot performance against defined business objectives.
Dashboards require operational expertise and scorecards require a robust management strategy.
Even if the enterprise knows what it wants to measure relative to strategic objectives, a robust management is required to change organizational behavior in the direct pursuit of those goals. The effective use of dashboards requires managers who understand the ebb and flow of business data and know when to act on changes in business activities.

Bottom Line

Dashboards and scorecards involve different approaches to the communication of enterprise performance data for management decision making. Knowing the difference is essential for those charged with developing the right reporting tool for the business.

Building a Planning outline in EPMa – pt 1

edhiggins — Wed, 03 Mar 2010 13:46:23 GMT

Altius Consulting has a long heritage of developing Essbase and Planning solutions and for this reason occasionally have cause to help clients upgrade or migrate a bespoke planning system built in Essbase, to a full installation of Hyperion Planning. The driving force behind this article is that I ran into some problems trying to import an outline from flat files into EPMa, for use in Hyperion Planning. The files were exported from Essbase using the OLAP Underground outline extractor and despite the formats appearing to match the documented requirements, after correcting some obvious faults, it just didn’t want to work. Previous versions of EPMa have been fairly unstable and many experienced Hyperion practitioners have advocated avoiding its use for the time being but the current release is much improved and we were keen to use the functionality that was available, so after a disappointing experience with the flat file, I thought I would try the Interface Tables route instead.

The first thing I realised was that when my installation of Planning was installed, the data source had not been created, the documentation regarding this appears to be a little misleading, suggesting that there is a direct menu link from Foundation Services to EPMa data source configurator, but I couldn’t find it, so I used the standard config option:

Launch EPM System Configurator.

Check Hyperion Foundation>Performance Management Architect, click Next.

Create New Datasource Link, click Next.

Select database type, click Next.

Enter your database credentials and a name for the data source, check the ‘Create Interface tables’ box, click Next.

The next page shows a task list, click Next to begin the process. Once the process has completed, indicated by a green icon, click Finish.

Check your database and you should have a number of tables with an ‘HS_’ or ‘IM_’ prefix. The IM tables are system tables, they do the following: IM_Load_Info – logs loads and updates; IM_Dimension – holds dimension definition data, like the !Dimensions section of a flat file; IM_Dimension_Association – is equivalent to the similarly named section of a flat file and is also used to associate attribute dimensions with base dimensions.

The tables beginning with HS_ are the tables that related to the specific model used in Planning, in earlier version s there was a basic set that could be used as templates, in version 11.3 the selection is much greater and four tables are created for each of the ‘standard’ Planning dimensions. Of each of these sets of four, HS_Dimension_Hierarchy and HS_Dimension_Member are required.

EPMa doesn’t include any tools specifically to help with loading these tables, so your favourite SQL tool will become a good friend, or custom build a process in ODI or whatever ETL tool you happen to have.

Matrix Tool in SQL Server 2005 Reporting Services (SSRS)

Hari Prasad — Tue, 02 Mar 2010 17:58:15 GMT

Issue: To Create a Pivot table styled SSRS report and show and hide columns based on user options.

Initial issue was to move rows of data into columns.

Original approach was to use SQL command 'Pivot'. But the issue faced was the fixed column names that the Pivot command expects. Too many columns made the query ugly and unreadable.

Second approach was to modify the SQL query to return all the rows into columns. Temporary tables used to parse the original dataset and build a final dataset with all the required columns.

Issue with this was the SQL query is repetitive and affects the performance.

With the above 2 approaches, simple table can be used to layout the SSRS report. Also using the adding report parameters for various view options, table columns visibility can be modified.

To improve the SQL query readability and performance, we could use a different tool in the report rather than a table. That would be Matrix tool.

By using the Matrix in the layout, it takes care of the pivoting of the dataset.

Fig. Matrix tool in SSRS Report Layout

With the pivoting of data taken care by the matrix, next is to modify the visibility of the columns.

When the visibility of the matrix column is set to false, the data is not displayed but the position or the width of the column is not modified. When column 3 out of the 4 columns is hidden, then the width of the matrix remains the same with a blank column 3.

Fig. Matrix tool Visibility issue in SSRS Report Layout

Solution:

This visibility is a known issue in 2005 version. One way to overcome is to make multiple copies of the same matrix and remove the various columns in each of the matrix copies.

Next step is to hide and show these matrices based on the option picked by the user.

At a given time there should be only one matrix visible and others hidden.

This approach works great until the number of copied matrices increase. In my case I had to create 7 copies of the original matrix.

There is a major performance issue with this multiple matrices solution. Each time the report is generated all the 8 matrices must have data loaded. There is no way to populate matrix on the fly.

Changing the visibility of the matrix could be faster, but the initial load time is not efficient.

Improving the above approach is to make each of the copied matrices into a separate report. All these reports can be added to the main report as 'SubReport'.

Each of these sub reports have the same stored procedure, but also takes in a special parameter. This parameter is responsible for determining which sub report to populate. Only for one instance the dataset comes back with values. For other instances the dataset comes back empty. This makes only one of the subreports to display.

Advantage with the sub reports is that it avoids data being loaded more than needed, which improves the performance.

This issue, collapsing the hidden column, looks like has been solved in the later versions of SQL Server, in 2008 version. Tablix is a new tool in the 2008 version which solves all the above mentioned issues. It’s a combination of table and matrix. For more details http://msdn.microsoft.com/en-us/library/bb934258.aspx

Upgrading PerformancePoint Server 2007 to PPS 2010

GlenChambers — Fri, 26 Feb 2010 17:38:24 GMT

There seems to be a great deal of excitement about the next release of SharePoint and with good reason, in almost everyway 2010 is better than 2007. With so many SharePoint implementations that promised so much and improved the previous situation greatly but didn’t quite live up to the hype, SharePoint 2010 provides the perfect opportunity for existing customers to ‘sweat their assets’ and really make SharePoint deliver what it was supposed to. The Business Intelligence and Performance Management aspects of SharePoint 2010 are something to be excited about and if you throw in the ability to migrate existing business processes into portal hosted business applications you have a compelling argument to upgrade.

If you implemented the SharePoint BI features via PerformancePoint Server (PPS) 2007 and you are concerned about upgrading from 2007 to PPS 2010 then you will be pleased to hear that Microsoft have released this official blog outlining the steps to a successful, smooth and hitch-fee migration:
http://blogs.msdn.com/performancepoint/archive/2010/02/25/upgrading-performancepoint-server-2007-to-pps-2010.aspx

Blog synopsis: Most of the customers who have been using PerformancePoint Server 2007 have accumulated several months, if not years, worth of dashboards and data. Their KPIs, grids, charts, scorecards, and custom objects have gone to good use, providing a great deal of corporate discussion about how to handle business decisions and to help plan for the future. And while the 2007 version of PerformancePoint helped to do this very well, the SharePoint BI 2010 version does it even better. Understandably, most companies want to build on top of their old dashboards in 2010. And the idea of starting from scratch is unthinkable. Fortunately, Microsoft has a nice migration path so that you can migrate all of your existing objects to the new version. The migration process is straightforward, but to help ensure that things go smoothly, we’ve created a set of steps to follow.

Microsoft supports two upgrade paths. The first is a version to version, in-place upgrade that involves launching an install of SharePoint 2010 right over the top of SharePoint 2007. The install detects the prior version instance and proceeds through the upgrade process. The second involves a version to version database attach scenario that allows you to attach a 2007 database to a new install of SharePoint 2010. Both methods work equally well. The latter method is ideal if you have new hardware for SharePoint 2010 and don’t need to re-use existing servers hosting 2007 versions of PerformancePoint/SharePoint.

It looks like Microsoft have listened to their customers and really put some work into ensuring that the upgrade paths for all aspects of SharePoint prove to be as straight forward and pain-free as possible.

UKOUG goes Dutch

edhiggins — Thu, 25 Feb 2010 23:53:47 GMT

Today’s meeting of the UKOUG (UK Oracle User Group) BI special interest group was a little unusual in that three of the four presentations were delivered by Dutchmen. First off Emiel Van Bockel reprised his award winning presentation on his BI project at his employer, Centraal Boekhuis, the Netherlands largest book distributor. Like so many Dutch Emiel speaks excellent English and his project is genuinely interesting using as it does a normalised data warehouse for large volume analytics, and also including an element of BI as a service, delivering supply chain metrics to Centraal Boekhuis’s customer base via a portal. Emiel’s BI and business philosophy is also very interesting.

Next up was a preview of some of the new features of Oracle Warehouse Builder from Ragnar Wessels, this was another interesting insight into Oracle’s ‘fusion’ tool set, using as it now does, the familiar interface layout of JDeveloper and SQL Developer. Although this is likely to be last release of OWB under that name, it’s worth getting familiar with the layout as it is the ‘go forward’ style for Oracle tools.

Thirdly, and occupying the ‘siesta slot’ after lunch, Chris Stanley gave us an insight into the challenges facing him and his colleagues as they try to modernise the BI practices of Network Rail. Even delegates from large commercial enterprises would have been surprised at the scale of the task I think, running 18 major stations, looking after tens of thousands of decaying fixed assets including 40,000 bridges alone, not to mention HR metrics for 35,000 staff is no mean feat. Add to this the pressure to reduce costs and they have got their work cut out.

Finally, the third Dutchman of the day, Sjaak Vossepoel of Oracle, aided by Craig Stewart, gave a us an overview of ‘Golden Gate’, the real time data reporting tool Oracle acquired a few months ago. I did know anything about the capabilities of this tool or how it worked so this was a very interesting presentation, it had never occurred to me that one could ‘extract’ transactions from a system with out actually querying the OLTP database, and thus it’s overhead on the transactional system is minimal. As Golden Gate was a niche player in this market and successful enough to be acquired by Oracle, I guess not many other people had realised this capability either.

All together a very interesting day and an opportunity to meet some new faces, if your business is an Oracle user and your not a member of UKOUG visit www.UKOUG.org for more details. You can meet the Altius team at the EPM and Hyperion annual conference beginning 16/06/2010, and the next BI special interest group meeting is scheduled for the 16/09/2010, although there is possibly an extra date to be confirmed if there is demand enough for it. The user group is a good place to here hands on technical overviews but also to here real world customer stories and experiences, and they are always looking for new volunteers to share their experiences with the community at large.

UDML to the rescue

edhiggins — Wed, 24 Feb 2010 21:13:27 GMT

A note of caution today for anyone using Essbase and OBIEE in an environment where the Essbase cube design is changing and those changes need to be reflected in OBIEE. There are several ways to add new dimensions and members to the representation of the cube in the repository, the simplest being to manually create the new objects. My good friend Christian has already blogged about how to edit the UDML to overcome a bug which causes problems with level enumeration when using the manual method. I think I may have found another issue; If a newly added column is subsequently used in a query filter, this results in an error of the '[nQSError: 46008] Internal error: File .\Src\SQ......' kind, very similar to those some people have reported when using direct database requests.

However, I have found that these errors after adding members can be eliminated if the new dimension/members are added following these steps:

use nqudmlgen.exe to create the UDML for the rpd layers relating to the particular data source you have added to, save the code in a single txt file;

delete all three layers of objects, then save the rpd and close;

then use nqudmlexec.exe to create a new rpd based on the original plus the amended UDML file.

I can only assume the reason this worked is that the udmlexec.exe rebuilds things more gracefully behind the scenes and the internal error is thus avoided, or it could be that I suffered some other as yet unknown issue. It would be interesting to here from anyone else who has had similar issues?

Are you getting the most out of your data? An overview of BOARD BI in 30 minutes

GlenChambers — Tue, 23 Feb 2010 13:00:00 GMT

Unified BI and CPM, Self-Service Reporting and Analysis, Interactive Visualization and Advanced Office Integration come together in a revolutionary product.

Tough times can bring many challenges - but can also present many opportunities. Havin g a clear view of your business can enable better decision making and optimize your use of valuable resources.

Thanks to its innovative toolkit approach, BOARD enables organizations to create customized Business Intelligence and Corporate Performance Management solutions quickly and effectively, without the need for any programming or extensive IT involvement.

BOARD provides access to a comprehensive "self-service" information environment, which enables users to obtain immediate answers to key business issues from a verified, consistent, shared information source.

Join us for a 30 minute presentation of BOARD along with a user case study review.

Want to find our more? Please join us for our next webinar:

Are you getting the most out of your data? BOARD in 30 minutes

Date: March 17th, 2010
Time: 2:00PM US Central Time

Join here:
https://www1.gotomeeting.com/register/163195968

For more information on BOARD visit: www.board.com

Altius Consulting extends its strategic partnership with BOARD International to the United States

Kate Rounding — Mon, 22 Feb 2010 15:41:00 GMT

Altius Consulting, the leading Business Intelligence and Enterprise Performance Management Consultancy and BOARD International, the global leader in Business Intelligence and Corporate Performance Management, are pleased to announce that they have extended their successful partnership to include the USA.The agreement strengthens the relationship which was established in the UK in June 2009, and provides both companies with the opportunity to continue their growth into the US market.

Noel Phillips, Vice President, Altius Consulting says: "We are extremely excited to be extending our relationship with BOARD into the US market - the combination of Altius' Performance Management expertise and BOARD's unique toolkit truly delivers on the promise of sophisticated, cost-effective decision making, and is a compelling proposition for businesses large and small."

Scott Jennings, Director, BOARD North America commented: “Extending our relationship from the UK to the US will allow us to continue to deliver compelling solutions into the North American market, and to leverage the successes that we have had.”

About Altius Consulting
Altius Consulting (www.altiusconsulting.com) provides specialist Business Intelligence and Performance Management consultancy to enterprises and large organisations throughout the world. The company works with partners and clients to turn data into valuable information, enabling better decisions and creating competitive advantage.
Founded in the 1993 Altius Consulting has the experience of over 500 project implementations, spanning companies in oil, gas & energy, financial services, retail and the public sector. Its customers include: BP, Gazprom-neft, Royal Bank of Scotland, Waterstone’s Book retailer, Harley-Davidson motorbikes, Santander Bank, Reed Exhibitions and the UK government’s Ministry of Justice.
With offices in both Houston, Texas and Anchorage, Alaska in USA as well as London, UK and with satellite offices in the Channel Islands and Russia, the Company has a global reach.

About BOARD
BOARD International (www.board.com) is a global leader in the BI and CPM Toolkit space. BOARD has enabled over 2000 companies worldwide to rapidly deploy BI and CPM applications in a single integrated environment completely programming-free and in a fraction of the time and cost associated with traditional solutions. BOARD provides one accurate, complete view of an organisation’s information, fully integrated with its processes, uniquely linking performance from strategic vision to all levels down to operational detail.

Modelling & Loading a Slowly Changing Many-to-Many Relationship

JakeSmillie — Thu, 18 Feb 2010 13:56:31 GMT

I haven’t blogged for a while, mostly because I have been busy. Today I am on a train to London , so I thought what better thing could I do to fill the time than to blog about something I have been meaning to blog about for a while: Modelling Slowly changing many-to-many relationships in a Data Warehouse.

This is something that, on paper, seems easy, but also very easy to get wrong / fall into common pitfalls.

When I was first asked to model such a scenario I went looking in text books and Google (or do I mean Bing now?). Anyway, I was shocked to see a distinct lack of information about this or at least no information that went into enough detail. So here is my in detail explanation on how to do this.

Consider the scenario where you have products and groups of products. A product can belong to many groups and they can also move in and out of groups, but we want to measure sales at any given time for these groups. Your Products dimension can slowly change, as can your ProductGroups, as can your ProductGroup_Mappings!

Below are extracts of an example Product, ProductGroup dimensions and a Sales Fact Extract.

Group 1 has Product A up until 20th-Feb-2009 and has Product B always. So if we wanted to get the Sum of all sales for Group 1 we would get 5(Transactions 1,2,4,5,6). We would not include 3 because this sold product A when it was not part of Group 1 (after 20th-Feb-2009).

To model this in a Data Warehouse, you may initially do it like the following:

Linked on Business Keys

So from the above you can see after 20th-Feb-2009 Product A is no longer part of group 1. This would be fine, and work if you are using SQL to retrieve data because you can add some between clauses using the RowStart and End dates etc when doing a retrieval to find total sales for Group 1, but for me, this is missing the point of a Data Warehouse. Querying a well designed Data Warehouse should be simple, I don’t want to have to worry about complex where clauses to get the data I want. It should all just work! Besides if you had an OLAP engine such as SSAS on top of your Data Warehouse it would not know what to do with RowStart and End Dates.You need to be able to write queries by just using simple joins without where clauses on RowStart and End dates to do things like this. The best way to think about this is VLOOKUPS in Excel. SSAS will in essence do VLOOKUPS from your fact to your dimension to get the correct dimension member.

So let’s see how this would aggregate all sales for Group 1.

So what’s happening here?:

First find all products in Grp1 (I used a VLOOKUP for this: Matches twice and return Products A & B.

Then:

(sum all sales where Product = A) + (sum all sales where Product = B): I used SUMPRODUCT for this

As you can see it has matched on all sales for Product A which is wrong. We want it to exclude transaction 3 as it happened when Product A was not in Group 1.

This approach on joining on business keys works fine for groups that do not slowly change. E.g. You add a product to a group and all sales of that product are attributed to that group. You then remove it and none are attributed to that group.

Ah… I need to do the relationship on surrogate keys. After all this is why we have them in slowly changing dimensions!

Linked on Surrogate Keys

Product Dimension for reference:

Now we see and extra row (2) due to the slow change of product A. Now lets see what happens when we link on keys to the sales.

Sales data for reference:

So again, we first get the product keys that are attributed to Group 1: (P1,P2 & P3) and we then aggregate all sales where those are the keys for.

Bingo! We have the right answer. Modelling on surrogate keys is definitely the way forward. But is that it? The more astute of you will realise that this has the exact same problem as the Business Key modelling. If we had a sale of Product A on the 22-Feb-2009, after Product A has been removed from group 1 but before it slowly changed itself (on 01-Mar-2009) the join would include that sale, wouldn’t it?

Well lets see…

Transaction 7 is the new transaction.

Well yes our suspicions are correct it does break!

What this means is that if any change to a group happens (add a remove a product to it) we need to slowly change the products in the ETL or ELT process before any sales data (or any fact data) comes through. So lets see what the new Product dimension looks like when this happens.

There is a new product key for product A which starts on the 20th-Feb-2009 (when Product A is removed from Group1) and then again for P5 is the normal slow change on 01-Mar-2009. This makes the sales fact look like this:

Now it definitely works. Good stuff.

Conclusion

- You need to model such scenarios using surrogate keys and not business ones

- Every time the members of a group change you need to force a change on the affected dimensions. This process makes the loading of the warehouse complex especially when you have multiples of these relationships and they change frequently, but who said loading Data Warehouses was easy?

There is one other way to do this relationship without having to force the change of the members and that is to include the date key in the many-to-many mapping. Whilst this would work, it could have a detrimental affect on performance as you could quickly have millions of rows in your mapping table / bursting view (if you join on dates dimension).

Finally I have attached my Excel spreadsheet of my working so you can have a play. Enjoy!

ManyToManyModelling.xls

Jake

Augmented Reality Mapping with Bing Maps powered by Silverlight and Seadragon. Microsoft at TED.

EdGillett — Mon, 15 Feb 2010 13:32:00 GMT

Microsoft continues to innovate in the mapping space by integrating many different data sources, from Flickr, World Wide Telescope and more, and accurately compositing them into a fluidly integrated 3D world which truly moves us beyond “just Street View”.

How long before the datasets that are limited to dashboards or reports become visual overlays over live video footage embedded in a front end like this? Why are my sales figures for this store down 20% this month – that’s because of the building work on the doorstep I can now see. As a tool for making data more discoverable, the technology now available is empowering whole new levels of user experience.

From: http://blog.ted.com/2010/02/augmentedrealit.php

BI Data Architecture – Does one size fit all?

edhiggins — Mon, 15 Feb 2010 09:22:35 GMT

Last week I attended the latest in Oracle’s ‘Unplugged’ series of partner days, a ‘BI Architecture Masterclass’. As Oracle partners it important for us to keep up to date with the messages they want to put out to the market about their products, unusually this day turned out to be a product and vendor agnostic day, and set out to explore a list of common aims and pitfalls encountered in enterprise sized BI projects, this was quite refreshing as I had expected more of a focus on BI Apps and using OBI on other sources.

Looking at the wider picture made things a lot more interesting; the first suggestion of the day to draw ‘oos’ and ‘ahs’ from the audience being a suggestion to replicate OLTP sources into a staging area and then to use SQL for change data capture to lessen the impact on live systems, rather than doing selective extraction with an ETL tool. An added advantage of this approach would be that the copied data would also effectively be live data and suitable for reporting, again without impacting the live system. Both these reasons are completely valid of course, but the reaction from the more experienced members of the audience suggested there may be a few reasons why this approach may be difficult to sell to a prospective client. Once the data from the primary sources has been captured, data from other sources can then be brought in and federated with the primary system data in a ‘Foundation’ layer using a 3nf database (more oos and arghs and much debate). Finally, this data can be pushed into an ‘Abstraction’ layer of star schemas and/or OLAP cubes as appropriate to your reporting tools.

The most radical suggestion of all was the use of a custom interface to load data to the target tables in the 3nf database, an example cited was a coded interface to generate dynamic SQL to load these tables. This API has the advantage of being a single place to add code to handle additional tables and columns that might be required, rather than editing multiple mappings in an ETL tool.

The pros of this approach are quite convincing: reduced impact on live systems, work load pushed down to database level in easily maintainable SQL; an extract/transform layer that’s easier to maintain than many multiple mappings; a federated data layer that’s more quickly available to end users than waiting for the final reports, dashboards or whatever.

The cons of the approach appear to me to revolve around two major assumptions made: disk space is cheap and your client’s IT department will provide adequate I/O capability to service that disk; and the client will have the in house skills to maintain the custom interface for changes in the data structure.

I think the reality most people will recognise falls somewhere between this approach and the OOTB pre-configured BI Apps with which most Oracle BI Developers will be familiar, largely due to the size of customers most commonly encountered. In the SME market place, implementing one or two flavours of BI Apps may well form the core of the clients BI strategy so adapting the ideas above is likely to be more appropriate, the source feeding the bi app becomes the primary data source and of course the extraction of this data is handled by the preconfigured ETL, that’s what you’ve paid for. So where do those additional data sources fit in? How can you federate data from your legacy system that can only output data in text files? And where in either of the above scenarios does you Hyperion Planning application fit in?, being as it is a data entry point, a data consumer and a data source for reporting all in one? Hopefully you’ll ask Altius for the answers.

Oracle Consulting Services forms Strategic Alliance with Altius Consulting

Kate Rounding — Fri, 12 Feb 2010 09:42:00 GMT

10th February 2010

Altius Consulting, a leading Oracle Gold Partner specialising in the full service delivery of Performance Management and Business Intelligence solutions is pleased to announce a strategic alliance with Oracle Consulting Services (OCS).
Based in Godalming, Surrey and Houston, Texas, Altius is partnering with OCS to ensure that both organisations pool resources more effectively and are capable of delivering projects to our clients more effectively.

John Gateley, Managing Director of Altius said: “delivering successful Oracle EPM and BI solutions to our clients requires alliances that offer a wider range of skill and experience. Oracle’s ownership of the technology and Altius’ track record of delivery over the past 17 years makes this a good move for all concerned. As one of the leading Oracle Gold Partners in the UK, this move will ensure that Oracle and Altius are effectively providers of one integrated and skilled resource pool. We are both totally focused on delivering successful solutions for our clients with no interference from other extraneous business activities”.

Essbase for the OBI Developer, pt3 – Find members

edhiggins — Tue, 09 Feb 2010 12:17:03 GMT

In my last blog I referred to the idea of the OBI developer using Essbase tools to their advantage in diagnosing problems and improving their knowledge of where the data they are reporting comes from. Smart View, the tool I suggested using in that blog is great when one knows what to look for, but what if you haven’t got that far because perhaps the list of required members is unclear or ambiguous.

For this type of research there is only really one place to look. The Essbase administration console does what the name suggests and provides access to administer the cube structure as well as other routine maintenance options.

If Essbase is installed on the machine you are working on you can access the console from All Programs>Essbase>Administration Services>Start Administration Console or more likely you will access it via a URL. Once logged in you will see some thing like this.

Expand ‘Essbase Servers’ and then your server to see the available applications.

Applications are instances of Essbase, each application may hold one or more Essbase databases (cubes). Drill down into the application and database until you see ‘Outline’, the outline is the structure of the cube and holds all the dimension members along with any attributes and characteristics applied to them, to view it, right click and select view. Of particular importance are the consolidation operator in brackets after the member name, (~), (+) or (-) being the most common; these control the way the values associated with the members roll up to give the dimension total. Also within the properties of the member (r.click View Member Properties) is the member formula which can be used to create a formula to generate the member value.

For now though we will limit the scope of this exercise to finding members, the ‘Find Members’ option is available from the context menu.

The Find Members dialogue has a number of options, as an OBI developer you are most likely to be searching for a member alias as that is what is exposed in OBI, so you need to remember to select an alias table. Now to set the search criteria, a slight flaw here is that the name you are searching for cannot start with a wildcard character.

Your Results are returned.

To view the member in the outline right click and select ‘Show member in primary tree’, this will show you where the member is in the outline, hopefully from this you will be able to tell if you are using the correct member in OBI.

Altius Consulting unveils new brand identity for 2010

Kate Rounding — Wed, 03 Feb 2010 10:30:00 GMT

Altius Consulting, the leading Enterprise Performance Management (EPM) and Business Intelligence (BI) consultancy, has this week unveiled the launch of their new brand identity. The introduction of the new corporate logo signifies the beginning of a new direction for the consultancy, which has experienced dramatic growth over the last five years.

Managing Director John Gateley commented: “At Altius we pride ourselves in providing our customers with an innovative and personal service that differentiates us from the competition. Having grown, and moved into new markets, our old brand no longer matched our culture or the innovative markets we have moved into. The launch of this new identity is a brave step forward from previous ‘refreshes’ and better reflects not only the personality of our company, but also the style of relationship we like to build with our clients.”

The new brand incorporates a striking blue orb, representing the ever evolving challenges and solutions that can be applied in the enterprise performance management and business intelligence arenas. The colours have also been specifically chosen to provide a fresh, clean and contemporary identity.

The new Altius Consulting website was launched on Monday 25th January, and offers visitors a friendly, easy to navigate experience. For more information on the Company, visit: www.altiusconsulting.com or email info@altiusconsulting.com to discover how Altius can support your business with on-demand information for better business decision-making.

About Altius

Founded in the early 1990s by two former PricewaterhouseCoopers consultants, Altius Consulting has a formidable track record of over 500 project implementations, spanning companies in oil, gas & energy, financial services, retail and the public sector. Customers include: BP, TNK-BP, Gazprom-neft, Royal Bank of Scotland, Waterstone’s, BA, Red Bull, Santander, Reed and The Home Office.
Based in Godalming, Surrey, with offices in Houston, Texas, the Channel Islands and Russia, the Company has a global reach with consultants sourced from around the world.

New England Seafood Choose Altius and BOARD to implement Sales Forecasting

Kate Rounding — Tue, 02 Feb 2010 11:42:00 GMT

Altius Consulting and our partner BOARD are pleased to announce our latest client: New England Seafood.

New England Seafood’s current Business Intelligence & Corporate Performance Management systems are not allowing them to produce the type of reporting or rolling forecasts critical to their business. Data ‘silos’ and lack of aggregation have meant that they are utilising Excel as a bridging tool. Choosing BOARD will enable them to align their systems and give them a unified approach to BI & CPM, all in a single product.

Initially the implementation, carried out by Altius Consulting will focus on 12 -24 month rolling sales forecast. Once complete the project is likely to extend to key account and production planning, historical analysis, reporting, profitability modeling etc

Charles Noble, Finance Director at New England Seafood says “As we made our way through a detailed review of the market for financial planning software, BOARD really impressed us with its clear user interfaces, lack of separation between planning and analysis functionality, and the relative ease with which we can administer the system in-house.

In choosing Altius to assist us in the implementation of BOARD, we were clear that we would be working with a team of proven experts who would take the time to understand the challenges within our business so that they can tailor the BOARD implementation appropriately. We look forward to completing the initial phase of the project and progressing to subsequent stages which will further improve our forecasting and BI capabilities”,

Dominic Policella, UK Director BOARD says “We are delighted that our partner Altius Consulting have been chosen by New England Seafood to deliver their Sales Forecasting in BOARD. We look forward to a long and prosperous relationship with both organisations”

John Gateley, Managing Director, Altius Consulting comments “We’re excited to work with New England Seafood to deliver an improved Sales Forecasting tool on BOARD technology. Bringing data from across the business to improve decision making and help find new efficiencies is what Altius and BI are all about.”

About New England Seafood: New England Seafood is a major supplier of premium sustainable fish and seafood in the UK with a turnover of £50m. They sell a wide range of products to the UK’s leading supermarkets and wholesalers around the UK. for more information visit the New england Seafood website.

For more information about Altius consulting visit About Us

For more information about BOARD visit the Board partner page

Altius at SQLBits V – Videos Now Online

EdGillett — Mon, 01 Feb 2010 21:56:59 GMT

My colleagues Matt Feltham and Ian Marritt were presenting at the SQLBits V Conference last year, and the videos from the sessions are now available online. Titles link to the SQLBits site, Screencaps link to the videos directly in Windows Media format. Note that although the video templates indicate this is “SQLBits III in September 2008”, this was in fact that last SQLBits conference in 2009!

Matt Feltham – Data Visualisation with Bing Maps for Enterprise

Ian Marritt – An Introduction to Master Data Services

The rest of the sessions from SQLBits are available here: http://www.sqlbits.com/News.aspx?Title=SQLBits%20Videos%20available%20now%20available

Adding ‘Help’ files to OBIEE

edhiggins — Sun, 31 Jan 2010 21:05:28 GMT

I was clearing down my hard disk when I stumbled across this note I had made for my self regarding help files added to dashboard pages. I haven’t seen these widely used which I think is often a missed opportunity, it has to be the easiest way to get content or page specific help in front of your users.

In case you haven’t seen this functionality in action, out of the box it uses the ‘Help URL’ box in the title view of an answers request, when the appropriate URL is added the question mark icon appears at the right hand end of the title view when the request is published. These notes explain how I implemented this when using WebLogic.

Create a folder to store the html files, I did this within the folder to which I exploded the analytics.war file during the deployment to web logic. There is no need to change or restart the services after adding or updating the pages, just bare in mind pages may have been cached.

Add a /../ to the fmap path to avoid the system looking for a folder called ‘Missing_’.

It appears that this is the only location to which the files need be copied, it works in design view in answers and in dashboard pages.

Essbase for the OBI Developer pt2 - Where’s my data?

edhiggins — Thu, 28 Jan 2010 15:32:00 GMT

Using Smart View to help troubleshoot your Answers query.

When one starts using Essbase as a data source for OBI things can get a little confusing, I have previously offered my own explanation of how an Essbase cube functions and how this related to the star schemas with which OBI developers will be familiar but from time to time it can still be frustrating if the results of your answers query aren’t quite what you were expecting. To get to the bottom of these puzzles it helps to learn how to use the Essbase tools to look at the cubes and their structure, lack of data where one was expecting it is one of the most frustrating things I have found but it is one of the quickest things to verify.

The majority of Essbase users will access their data, read it and even update it through MS Excel, previously this has been through the Essbase Add-in for Excel, but now the tool to use going forward is called Smart View (but it is still an Excel add-in). Smart view should be on the radar for all OBI developers as it likely to replace the BI Office plug-in at some stage.

So, a very basic example demonstrates this:

1. Taking a random selection from a demo Hyperion planning cube in BI Answers.

But this returns no results, this isn’t totally surprising as we have only selected a single measure column, whilst we could just add all the measures to the query to see which return data, it would be better to find out where the data is and what we need to use in query to see it.

2. Launching Smart View is simple, once the add-in is installed, users get a Hyperion tool bar option in Excel, the first tool in the Hyperion menu is the ‘Data Source Manager’, this appears as a floating panel on the right of the Excel window and gives access to all the available sources in the EPM suite.

To access a data source just try and drill into the folder and the login dialogue will open. Drill into the appropriate folder to find the cube you are looking for, right click it and select ad-hoc analysis from the context menu.

3. What you now see is the default view of the data with two dimensions, (Account and Period, both required dimensions and arguably the most important) displayed on the X and Y axes of the spreadsheet, and the other dimensions held in a floating palette.

Without going into too much detail, the total level of each dimension, or a member (value) from within that dimension is required in every Essbase query. In OBI this happens behind the scenes, the BI Server query engine will take our selected ‘columns’ in the query, and in the top level member from the dimensions we haven’t used, and will write an MDX query to issue against the cube. To over simplify it, in Smart View this means that what you see either in spreadsheet cells or in the palette will be used in the query.

There are few simple things to remember to help you find the data you are looking for:

Double clicking a member expands it, exposing the next generation (one can also set it to show all);

These two buttons are a revelation the first time you use them, once you have expanded a dimension, Period for example, but you are only interested in the months Jan – Jun, highlight those cells and click ‘Keep Only’, hey presto all the other members below Period disappear. I’m sure I don’t need to explain what ‘Remove Only’ does;

If you expand a member and then decide you don’t want to see all it’s descendants after all, just double right click on it to collapse it;

To move a dimension from the palette onto the spreadsheet, just drag and drop it;

So having dropped Year for example, we could drill down, see which year value we want and then do a Keep Only or a smarter way to do it, and a more practical way with a larger dimension is to select the members first before dragging them onto the sheet;

Click the drop down arrow next to Year and click the ellipsis below ‘Year’.

This opens the Member Selection dialogue, the style of which should be familiar to most people. Remove Year from the right and side and select the year you want from the left. When you close the dialogue the value FY09 will be displayed in the palette, this can be dragged onto the spreadsheet if you want or if you have selected multiple values.

Once the filters have been set by selecting the appropriate members to match the OBI query filters all that remains is to double click the Accounts dimension member to show all the measure accounts. Sure enough we have to scroll along way across to find a column of data.

So there is the reason we have no results in OBI, there is very little data in the cube, this could be for a number of reasons dependant on the nature of the cube. The final anomaly is the title of the column, a member called 411100, odd as all the measures we have in OBI have alpha character names. The reason for this is that all members in Essbase can have aliases, so for financial accounts numeric codes are usually used as the member name as they are more concise, and the account has an alias which is usually a more verbose name or description. Essbase and Smart View display the member name by default, OBI displays the default alias. To view the alias in Smart View, right click the spreadsheet and choose Hyperion>Ad-Hoc Analysis>Change Alias Table, and select the appropriate alias table.

In this case our figures turn out to be against an account who’s alias is ‘Operating Revenue’.

Altering my Answers query accordingly now returns some data.