Some ETL tools out there use pure scripting to extract, load and transform data. Jedox has a classical ETL interface which allows you to re-use components and structure things in a modular, logical way.
Scripting in ETL
1 Reply
Logging your users
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.
Increasing Visibility of Load Errors
The Jedox Task Manager gives you key information about a job’s execution. It notifies you every time a job is run, regardless whether there were errors or not. Sometimes though, you might require a bit more sophistication in the notifications. For instance, you might only want to receive a notification when there are errors or warnings in a job. Maybe receive the jobs’ error log in the body of an email or even attach the full server log as an attachment.
Fortunately, this is all possible with a small piece of groovy script magic.
Simple Profit and Loss Planning Template
Templates should be simple, clean and easy to understand. They can come in a myriad of different formats, styles and cover a multitude of various models. As a template designer, the key is to try and make the template itself disappear into the background, while at the same time coaxing the actual information it contains into the foreground.
So no crazy fonts,
and no crazy colours.
You will give your users a headache, especially if they have to stare at it for a long time.
Jedox 5.1 – A Consultants Perspective : Part 4 – Wrap up
A final note on some small(ish), cool features in Jedox 5.1. The new write back functionality allows you to directly write back to a cube via controls such as drop lists, hyperlinks and macros. If the target cell is a palo.data, Jedox will try to write the passed value into the intersection in the cube. Check out some examples here.
And web report fans will be happy (and relieved) to have available to them a custom colour palette and palette history:
The Jedox 5.1 release has been a solid step up from Jedox 5, bringing with it new innovative features to make the Jedox consultant’s life easier. I have highlighted a small selection of new functionality which enhances your existing Jedox toolset. There is a lot more here as well, such as Data Driven Modelling and R Integration which I will focus on separately in later posts. In case you missed them, here are the links to Part 1, Part 2 and Part 3 posts on 5.1.
Jedox 5.1 – a Consultant’s Perspective : Part 3 – Data Validation
Data Validation
One of the big changes for existing Jedox web users and consultants is the advent of Data Validation. This opens up a lot of possibilities for development of web entry templates and forms. The validation is essentially a format (like Excel), so therefore you can easily copy it across to other cells, or use in a Dynarange report. The list validation is similar to data validation in excel but with an important tweak: you can add additional columns to the validation array which allows you to display one value and enter another. I will run through a couple of example below:
Make your manufacturing smart with Jedox
The 3 steps to improve how your people collaborate with information
Recently we published articles and interviews on how manufacturer GWA streamlines business planning and reporting processes with Jedox. Here are three lessons:
1) Don’t start with a long winded RFP (Request for Proposal). They rarely work. Instead, run a Proof of Value with a Bootcamp that delivers tangible results. You can build your capacity and deliver results, right now.
2) Your people aren’t stupid. Achieve more with less resource by empowering your business users. If you know Excel, you know Jedox. We call this ‘lean-BI’.
3) Don’t stop. Move beyond Data Discovery and recognise that any business process where you capture, consolidate and report data, is a candidate to streamline.
The result? You become an information entrepreneur who steadily replaces unstructured Excel-based processes throughout your organisation and transforms how people collaborate. Read this white paper to learn more about best-practice on collecting and collaborating with data.
This case study gives you an easy reference on how to do it right from the rapid and effective Bootcamp that delivers immediate results, to the ongoing visibility and efficiency through Jedox.
Enabling Session Limit per User in Jedox
Jedox doesn’t allow you restrict the amount of times a user logs in to the server, and if the amount of concurrent users you have is small; then you will have the need to limit the amount of connections per user.
Luckily it’s quick and easy to do so, by following these simple steps:
- Create a new element in the User Dimension: it will define the amount of sessions that the user has.
- Change palo.ini: add a new authentication method.
- Add a SVS script: php script that will handle the authentication.
Creating the new element in the User Dimension
We need to add an attribute to the _USER_PROPERTIES dimension (which is hidden for the normal user) in the System database, which in this case is going to be called “sessions”.
Since there’s no direct access to that dimension, we need to modify it through an ETL job.
Change the session values
Create a Paste View to modify the values for the users. The rule is that that cell for the user is blank, then the user has unlimited logins, otherwise you need to specify a number (starting from 1).
The Paste View should look like this:
Add the SVS script to handle the sessions
Look for the current SVS script that is running, if you don’t have anything running then go to <Jedox Installation>\svs\ and save the following file.
Then the sep.inc.php file in <Jedox Installation>\svs\, needs to be changed to include the previously download file:
<?php include './sep.inc.session_limit.php'; ?>
If you already have a SVS script running, then you need to change/complement the OnAuthenticate function, with the one provided.
Change palo.ini
Once the SVS script in place, now it’s time to enable SVS (if not already done) and the authorization login in palo.ini.
For that, we add the following lines (if they didn’t previously exist before) to palo.ini:
workerlogin authentication worker "<Jedox Installation>\svs\SupervisionServer.exe"
Disadvantages
There’s no information in the login screen that will reflect that the user has reached the number of concurrent sessions. It will say “Incorrect Username/Password”.
However, going to the SVS log file, the information will be stored (in case it’s necessary to identify how many users are trying to create more sessions than allowed).
Jedox Apps in manufacturing & wholesale
Lean-BI
Recently we interviewed GWA on how Jedox initially highlighted source data issues and helped them drive a culture that took data more seriously. One of my favourite quotes was the answer to how many people in the business supported their Jedox: “One. Not even one full-time employee.” We’ve heard of lean-manufacturing but what about when you apply this to your information-based business processes? To help illustrate how efficient GWA have been, we’ve summarised just some of the business applications they use Jedox for.Corporate Consolidations
Post organisational restructure meant reporting Financials for other businesses. The changes meant four businesses which require detail reporting in their own chart of accounts and complex mapping and consolidated reporting for the GWA Group for P&L Balance Sheet and Cash Flow.
Enterprise Budgeting and Forecasting
Comprehensive budgeting process from payroll, expenses, revenue drivers, sales and automatically calculating direct costs including customer freight accounts and line haul freight accounts.
Stock Management System
Monthly and Daily products claims, and information related to it, like customer, dates. The daily model is to support the operational view, including what is outstanding/pending, and what was closed yesterday. Every time a customer returns a product, this tracks the action. The monthly model generates KPIs by customers, warehouses, products, including claims by user, customer, processing days for each stage in the process, and days to be returned.
Inventory & Stock Management with Jedox
Sales & Inventory Analytics
The sales model details daily product analysis including by customer, currency, division, sales type.
By analysing historical sales pattern and stock consumption, you can improve their stock allocation in warehouses across the country. Stock turnover rates provide invaluable information on best-seller and order priorities.
Sales Forecasting
Based on customers’ historical purchases across various channel, forecasting algorithms produce a sales plan which would which becomes the input for the manufacturing schedule.
Users enter their monthly plans by product group. It enables collaboration across the organisation for sales plans and a group consolidated view. Planning is for both individual manufactured products, and for products collections sold as a separate product, called an ‘assembly’.
Jedox is flexible, so it often ends up streamlining all types of business processes in a way that is easy for users, because you don’t eliminate Excel – just the drawbacks. If you have some interesting ways your company uses Jedox, we’d love to hear from you!Jedox 5.1 – a Consultant’s Perspective : Part 2 – Security
Security is critical in any Jedox implementation. Efficient ‘lock down’ of your models are just as important to an organisation as the data they contain. There has been a need for an additional layer of security in Jedox’s database architecture for a while now.
Pre-5.1 , you could lock down cubes , dimensional elements, even cells. But you could not actually hide or restrict databases themselves. This is a hugely important step – now you can hide all assortment of databases from certain user groups. A great example that has come up time and time again for me is around Payroll models. Even though Jedox security stops unauthorised access to cubes and dimensions within a Payroll database, the fact that unauthorised users could ‘see’ the database folder name made administrators nervous. This is now a thing of the past with the Jedox developers re-writing the security model to cater for databases. There is now a separate security object (#_GROUP_DATABASE_DATA) that allows admins to restrict by group access to a database.
This new security development also opens the door potentially to a more multi-tenanted approach to model development. I have updated our security templates for database restriction and made them available :
Excel Security Template (pw:jedox)
Enjoy!
the naked leaf 