Ross Goodman

photo credit: eshm
I was having a flick through my employment contract and I found the following clause:

13.2
You agree that during the course of this contract and after the termination of the contract for whatever reason without limit in point in time you will keep in confidence and shall not disclose to any person unless authorised to do so with the prior written consent of the Company the following:-
13.2.1 Any consultancy know-how, methods, tools, techniques or intellectual capital.

I realised that some of my blog entries or tweets could fall foul of this clause so I asked for written permission to continue blogging generic “work related” content such as Business Intelligence, Data Warehousing, Data Modelling and Business Objects.
Rather than granting this and leveraging the fairly good google rankings that my posts seem to get I was asked to remove all work related posts.
Business Objects Best Practice (higher ranking than BusinessObjects.com)
Business Objects New Features (Page One)
Dimensional Modelling (Ralph Kimball ?)
As such I have password protected all of my work related posts.
If you are struggling to find content there are a variety of searches that could turn up interesting content.
Alternatively you can go to my company site, contact details can be found here or here.

Business Objects – Best Practice – Universe Design

The purpose of this document is to provide a description of what I consider to be best practice when designing a Business Objects Universe. We will also describe the reasoning behind the suggestion.

(continue reading…)

This is really obvious with hindsight but it took me ages before “the penny dropped”.
Most of the time I find myself using the “Filtered Rows” option on my selectors.
The main selector that I want to use is the “Filter”, but strangely this does not have the option of filtered rows.
What I have resorted to doing in the past is to use multiple selectors:
A date drop down list box filters the rows from “Raw Data” sheet to the “Monthly” sheet.
A product drop down list box filters the rows from the “Monthly” sheet to the “MonthlyProducts” sheet.
A customer drop down list box filters the rows from the “MonthlyProducts” sheet to the “MonthlyProductsCustomers” sheet which now lists all of the sales made for that combination.
The filter component could do that, but this component assumes that there will only be a single row in the output. As soon as you have multiple rows in your result set then you can’t use the filter component.
Ahhh, but you can with a little lateral thinking; that’s what Xcelsius is all about.
(continue reading…)

I have spent most of today throwing data around in MS Excel.

One of the things that I have had to do is to convert percentages into real numbers.

In excel percentages are stored as fractions, for example 50% is stored as 0.5, 75% as 0.75 etc.

I am trying to chart these figures in Business Objects Xcelsius but the y-axis labels are just showing 0 to 0 hence why i need to convert my numbers.

I have just found a really easy way to do this.

• In the target cells, enter the value 100 in each cell.
• Copy the source cells
• Select “Paste Special” using the options “Values” and “Multiply”

This takes the source value 0.75 multiplies it by 100 and stores the result 75 !

I have used pase special – values often but I have never seen the need for the multiply option until now !

I have spent all day in MS Excel.
I receive excel spreadsheets in a “fairly” fixed format.
I need to get this data into Business Objects Xcelsius via another spreadsheet that stores historical records and keeps the data in a format optimised for display.
“Normally” I would be using Data integrator (DI) to move the data from the source, store the data in a database table or two, use query as a web service (QAAWS) or Live Office to pull the data from the database directly into Xcelsius.
Here we do not have access to any of these luxuries.
I have written a translation spreadsheet that I can point to a specific source and it will re-shape the data so that it can fit my target spreadsheet. Then it’s just a case of copy and then paste values….
To make this process easier I am using the “INDIRECT” function in excel.
=INDIRECT("[" & $H$1 & "]Data!" & AU$1 & $C17) * 100
Here the current cell will grab the vaue from the workbook named in H1, go to the “Data” spreadsheet then grab the row and column attributes from AU1 and C17 then multiply the number by 100.
This means that next month I just need to change the name of my source spreadsheet and it will all just work. If a row or column “moves” in the source, I just update the metadata (AU1 or C17) in this case.
It takes a long time to set up and to check but next month it should be a breeze.