Tag Archives: Planning

Logging your users

1 Reply

Occasionally during a planning process, disaster strikes. Users can accidentally delete hard-worked-on plans, change some key drivers or alter report values during a printing process. Plans may be altered by unwittingly by colleagues or by well meaning management without understanding the consequences. This is where you need to understand what has been entered in the system and by whom. Jedox gives you the flexibility to create user logs to illustrate what has changed.

Continue reading →

Council planning with Jedox

1 Reply

Sunshine Coast Council optimise long-term planning with Jedox

Located south of the glorious Great Barrier Reef, Sunshine Coast Council formed in 2008 in Queensland, Australia after the amalgamation of three separate councils. Each individual council had a separate ledger in separate Financial system. It was difficult to access the information required to report and drive their planning process. Here we speak with Wayne Jensen, Manager Financial Planning & Performance at the Sunshine Coast Council on their recent Jedox project with Naked Data consultant, Halim Joe.

Naked Data: What was it about Jedox that initially got your attention?

Wayne Jensen, Manager Financial Planning & Performance: The number one thing about Jedox was that it was built around an Excel interface. This meant no translation into an unfamiliar environment. It was easy to migrate across from our current systems, and this certainly grabbed our attention. For reporting and data analysis, we wanted Jedox so we could to get a more holistic view of the data and be able to spot trends in the data coming from payroll and finance.

Naked Data: Which other options did you consider?

Wayne Jensen, Manager Financial Planning & Performance: We already had a home-grown attempt at using Microsoft SQL Server. We didn’t have the budget for TM1, but still looked at Cognos Express in detail. We found it to be clunky by comparison, and with a restricted budget couldn’t look at a full Cognos implementation. We were already more than happy with the Jedox tool, and Cognos Express simply didn’t stack up.

We understood Jedox’s fast implementation time early on and this made it attractive. As part of our diligence, we had done our research, including speaking and visiting other organisations using Jedox. This confirmed we had a quick implementation process, and Jedox didn’t involve specialised IT training.

Naked Data: How was this initiative considered within the business?

Wayne Jensen, Manager Financial Planning & Performance: Finance had started a project for BI [Business Intelligence] reporting and monitoring. As Finance, we had particular objectives, such as being able to identify information which would assist management to get a snapshot of the business in terms of KPIs, so that they could take immediate action where necessary. Finance needed something simple and easy-to-use, but IT had something different in mind. IT was trying to build something that wouldn’t be available for a few years, but we wanted something we could use now.

Naked Data: Was it easy to find common ground between Finance and IT?

Wayne Jensen, Manager Financial Planning & Performance: At the start, we needed to work to get our IT team on board. Initially they had their own preference. Their project was homegrown – they had been building from scratch, using SSIS [Microsoft SQL Server Integration Services] to pull data from other systems into a SSAS [Analysis Services] cube and then run Sharepoint over that. It was a complex process to try develop and it took a lot of input from IT and specialised technicians.

At Sunshine Coast Council, Finance depend on IT for access the data from finance, payroll, and property management systems. Our main interest was IT support for our initiative. We needed a long-term financial model that we could manage and securely existed within the IT operating environment – trust and open communication made things run more smoothly.

Naked Data:How did you start?

Wayne Jensen, Manager Financial Planning & Performance: We began with a one-week Naked Data Bootcamp. Working with [Naked Data consultant] Halim onsite was great. We felt very comfortable and confident that Halim understood exactly what we were looking for. He was able to provide advice and deliver exactly what we wanted.

IT had been developing an ad-hoc reporting systems – 6 months on a particular report, and Halim was able to replicate that in a day. This was showed us that Jedox could play a significant role in the organisation’s future.

Naked Data: Are you planning to expand Jedox in the future?

Wayne Jensen, Manager Financial Planning & Performance: Certainly. Low ongoing costs and rapid implementation time were initial drawcards, and we’ve been very happy with Naked Data’s support. From our Finance perspective, we initially needed something we could be confident in pulling through data, and doing forecasting and scenario analysis in front of senior management, and Naked Data with Jedox have certainly delivered that. There is every chance that Jedox will expand to more budgeting and reporting in the future.

Source: GL: Technology One – Finance One, SQL Server
Project: Long-term planning model

Better beverage budgeting with Jedox

De Bortoli Wines streamline budgeting with Jedox

De Bortoli Wines is a third generation family wine company, established in 1928. The winemaking philosophy is that great wine begins in the vineyard. With vineyards in some of Australia’s best known wine regions including the Yarra, King and Hunter Valleys. The Riverina produces the acclaimed Noble One and world-class premium varietal wines.

Anyone with experience in food and beverage production knows modelling these processes can be complex. From raw materials with expiry dates, to average costs versus standard cost for different parts along the supply chain. Fortunately, De Bortoli have some of the best and brightest working with them: their internal team actually developed a Jedox plug-in for the data integration tool Kettle. In this interview, we speak with Management Accountant Ian McLain, and Solutions Architect, Pieter van der Merwe about streamlining budgeting with Jedox.

Naked Data: Has Jedox made your budgeting easier?

Ian McLain, Management Accountant: Jedox Web has opened up a lot of new avenues. The branches can now enter their data directly and we can see them as they are updated and understand where in the workflow they are up to. Then the branch managers can review their input. It has cut out all that to-ing and fro-ing. The whole process is streamlined.

Pieter van der Merwe, Solutions Architect: Jedox Web has made a big difference. We used to send Excel spreadsheets out to the branches, and then get them back filled in, and then have to import these and make adjustments. But everyone gets access to Jedox Web, so it’s made a huge difference to our data collection and saved a lot of time.

Naked Data: How has your planning process benefited with Jedox?

Ian McLain, Management Accountant: Our pricing model has really benefited. We’re so much more precise now. We’re budgeting and planning by litres, we have a price list, discount and rebate structures based on our customer groups. For budgeting, we used to just take the budget from the year before and add on 3 percent. Now we can work in much more precise measurements, the results are more accurate. We can break down to any level of detail for variance analyses. Instead of trying to figure out whether or not we are under budget, we can see WHY we are or aren’t, we can see whether the discounts are too high, or if the litres produced are too low – or if the cost of goods is too high.

Pieter van der Merwe, Solutions Architect: Finance used to come to us with a general idea of what they wanted and then there would be back and forth as we tried to get an idea of what they wanted, now Finance comes to us with a model and says ‘build this’, and we can model it straight away in Jedox, linking to any operational data sources.

Naked Data: Does this mean you can model business processes more easily with Jedox?

Ian McLain, Management Accountant: Yes. It’s such a free format. For instance, at the moment we are building our own labour model. We used to just budget labour the same way as all the other departments, which did not provide enough detail to allow for meaningful analysis. Now we can extract straight off the payroll package, upload it straight into Jedox, and then have budgets right down to individual staff in the budget model. It’s fantastic for building your budget models on real time issues rather than dumping stuff out of the general ledger.

Naked Data: How quickly do you get to insight with Jedox?

Ian McLain, Management Accountant: Previously we used to update journals in the GL and then we would have to wait overnight for everything to update. We couldn’t get real-time data. That changed with Jedox, we now get info on the fly. When I’m in sales meetings, sales managers ask me if I can incorporate certain things into reports and plans, and I can do it there on the spot.

Naked Data: What next for De Bortoli and Jedox?

Ian McLain, Management Accountant: The writeback is useful for more than just budgeting. We are using this feature to record monthly comments on variances. At the moment we’re doing monthly forecasting as part of the budgeting cycle, but our next priority is rolling forecasting with Jedox.

Snapshot: Jedox budgeting & forecasting solution for beverage producer De Bortoli Wines.Source systems include MFG Pro.

Loading data from Excel – welcome to my new friend, PALO.SETDATA_BULK

5 Replies

I have a pathological aversion to large spreadsheets. Probably the most common issue I encounter in consulting is people trying to read or write 20,000+ rows * 150 column spreadsheets from their OLAP system. Then wondering why everything is slow on their client machine.

In my opinion, if you have a decent BI system, and you have managed to create some cultural change in your organisation around how data is surfaced, you don’t really need a 20,000 row spreadsheet on a daily basis. All you do is create further chaos and completely miss the point of a BI system, which should be to simplify how users interact with your organisational data, not make that interaction more complex.

If there is one thing worse than Excel Hell, it is OLAP Hell.

But no matter how much I rant, the fact and the reality is that people do use and need big spreadsheets on a daily basis in their organisations. I cannot escape the reality of reporting pressures that my clients are put under to conform, especially to ‘the way things have always been done’.

So, in light of this, I was pretty impressed with the latest small but important functionality addition in Jedox 5.0 SR1 – the PALO.SETDATA_BULK function. Up until now, we have only had 2 options in Excel: PALO.SETDATA (slow for lots of cells) and Jedox Importer (slow for lots of rows of data, and a bit counter-intuitive for a management accountant). Both of these you can you use in either spreadsheet cells or vba.

The PALO.SETDATA_BULK allows you to take advantage of the same api’s that the ETL uses to load data in excel. PALO.SETDATA_BULK also takes advantage from a single array formula in a single cell (as opposed to 20,000 PALO.SETDATA formulas all trying to execute at the same time).

From my testing, it can send in over 20k rows in excel in less than 1.5 seconds. The same data sent in via normal PALO.SETDATA formulas take over 30 seconds .

Both tests were done against a localhost, so against a client server environment there will be other latencies like networks, etc. This is a massive improvement in anyone’s language and certainly will assist people who have the need for those large spreadsheets.

One note: you need to structure the data in a fact-table format to allow the array formulas to work. See the example against the Demo/Sales cube.

EDIT: I was asked to post the example spreadsheet, sans IF statement. You can grab it from here.

%d bloggers like this: