Tag Archives: Naked Data

Jedox to SAP Connectivity Part 1 – The SAP Java Connector

Amongst other available connection types to other source systems, Jedox also provides connection to SAP R3 system.

The first step of configuring the Jedox SAP Connector is to setup the SAP Java Connector.

At the time of Jedox All-in-one suite installation, you can select the Jedox SAP Connectivity option. This consists technically of two components: A Jedox ETL Add-On which defines the SAP connection and extract types, such as SAP ERP content and SAP BW and SAP Add-On which has to be transported to the SAP System.

You need to have the SAP Java Connector Adapter Version 3 or higher downloaded on the machine where Jedox is installed. You will need to have a valid account to download it from SAP Service Marketplace. You need to download the relevant file based on your operating system.
Extract the relevant zip folder to a directory where Jedox is to be installed.
You will need to set the Environment Variables. Go to “Environment Variables” in the System Properties:

Create a new System Variable called: “CLASSPATH” which should specify the sapjco.3jar file in the SAP JCO install path, the below screenshot shows this:

In the existing System Variable called: “Path”, you need to add the entire SAP JCO install path in the variable value. The below screenshot shows that folder: sapjco3-NTAMD64-3.0.9 contains the SAPJCO3 dll, jar files etc:

To verify that the installation of SAP JCO has been done correctly, you can enter the following using command prompt: java -jar {sapjco-install-path}/sapjco3.jar.
Then the following screen of SAP Java Connector comes up. This indicates that the SAP JCO installation has been done successfully. If there was any error, you can see the errors in the window:

Note: For more information on SAP JCO Connector, you can open the Intro document in your SAPJCO install-path in a web browser: file:///c:/sapjco3-install-path/javadoc/intro.html

To follow on to the next steps in Part 2 of this post, please click here

Hitting the Sweet Spot

Leave a reply

Today was the second day of a Naked Data Bootcamp (basically a paid Proof of Value) at a client site. Even though I have been involved in many of these, it is always an extremely satisfying moment when you watch a client finally “getting it”, and understanding the range of flexibility and power that Jedox (and your solution you are building them) offers.

For me, getting the client’s penny to drop is and end game of a delicate process – we want to really deliver what they are looking for but at the same time steer them to a design and a model that will be robust and long lasting. The Sweet Spot.

The client wanted to be able to analyse down to a detailed level of granularity but the level they wanted in the model just did not make sense in a multi-dimensional world of a Jedox cube. It was just way too flat and transactional. I knew that if I built the model the way that the client was demanding, they would be left with a model that was sub-optimal in performance.

I convinced them that we should build a model with summary data (still with 30k plus elements in some dimensions), with drill through capability to their relational database for the flatter, transactional data. Although unsure at first, once they saw it in action, the client quickly agreed that this would more than satisfy their requirement.

There was a fair bit of time white boarding and convincing the client that this was the way to go. Why bother? We could have easily built the model and finished in half the time if we initially agreed to their demands. From experience, however, managing expectations and communicating your rationale is critical in the long term success of a model and in forming a great relationship with your client. It basically is an exercise in building trust.

Sometimes you need to risk the relationship by backing yourself and your experience. Initial engagements mean that people come to the party with their own expectations and baggage. It takes them a while to understand that you may be offering them a new way of looking at their old problems.

VWWR8KCJMTUZ

Web Reporting with Dynaranges and Attribute Filters

2 Replies

On the Web, with Dynaranges, you can make your report length determined by how the user interacts with the report. Dynaranges allow you to specify a single row of data and then determine what happens to that row when the user interacts with it in report mode.

It is a powerful Web component because you can create completely customised, dynamic reports that allows users to explore your data without restrictions while keeping a structure of a formatted report.

You can use the following as datasources in a Dynarange:

Subsets
Formulas
Relational Queries

Dynaranges can be on either columns or rows. This means that both axes of your report can be dynamic. In addition, if you select a subset that contains hierarchical structure, the user can double click to drill down to the next level of the data, and the report will expand automatically.

Relational Dynaranges allow you to write a relational (SQL) query via an ODBC connection and return rows of a single column dynamically. You can then use ODBCDATA() functions to pull out the additional columns you require. The report expands and contracts with the data you are returning.

Everything in the Dynarange range will be calculated. This means that non-subset values (eg spreadsheet formulas) can be included and the results will be calculated at runtime with the Dynarange.

OK. An example.

Attribute Subset in a Dynarange

Download the example here.

This contains a Dynarange and subset based on attributes in the customer dimension. The user selects an attribute from the Customers dimension in the first combo box. This then filters the second combo box to show all the data values against that attribute name. The user can then filter the elements on the dynarow. If you look at the subset on the dynarow, you can see that the attributes are dynamic and pointing to the results of the combo box:

This is a handy dynarow style for filtering a sorting through a dimension with many attributes. Note: In the example, the elements returned to the second combo box (the attribute data) may be duplicated as it is displaying a value for each element in the dimension. You can remove duplicates only by writing a macro (that is attached to the combo box) to loop through an array of elements and picking out only the unique ones.

Extending Web Batch Reporting

Leave a reply

Batch reporting is a critical process in some organisations. The ability to print multiple copies of the same report for different parts of the organisation for example, allows a controlled distribution of reports via a central source. Jedox provides a built in Batch Reporter. It allows you to spin off multiple copies of the report via specific dimensions of the data (for instance, by Cost Centre, Region, Division etc).

The standard Batch process only prints to pdf, and can only deliver the reports via email. With the help of some php script and web macro, we can take a simple web report and allow it to be printed as a batch in some different ways. We can get a series of reports (not just one) generated. For example, one report per region element. We can also allow that report to be printed as either an xlsx or wss file directly to the filesystem.

See the example here.

Steps to get the report to work:

Report Directory: Enter in a path that the Jedox Web server can see (normally a local path). For the moment, this directory must exist. Although directories are not created as part of this process, that could be by extending the php code further.
Report Title. This will be used as the first part of the filename that gets generated in the batch process. The second part of the filename will be the dimensional elements that you are looping through. Also select the checkbox if you would like values only generated by the process.
Reporting Filename. Ignore this, as it will be generated automatically as part of the process.
File Type. Select xlsx or wss
List of Elements. Under the chart, there is a hidden column which contains a list of elements that I want to loop through. Put a zero in cell A1 to unhide the column. It is a named range, so if you want more elements, just insert some rows in your report within the grey area to extend the range:

Once you have done the above, run the macro by clicking the batch button. If you get a dialog box telling you that the macro is complete, then usually the process has run successfully.

If you do not get that dialog, then something has gone wrong. Normally, the problems is related to insufficient permissions to write out the file or that the directory does not exist. This useful extension to the built in reporting can itself be extended. If you wanted to loop through more than one dimensional list, you could have multiple nested loops in the php macro generating the reports you required.

Videos and Stories from the Coal Face

Leave a reply

In case any of you missed it, a few weeks ago, Jedox released the Virtual Showroom This site is a showcase of the Jedox technology via videos and helps explain to business users how the technology works. It is an excellent starting point to understanding what is in the latest Jedox version, especially if you are using this for the first time.

From time to time, we will be putting out some interesting posts on some stories from the field from Naked Data consultants and our clients under the Category “Stories from the Coal Face”. I hope you find them enjoyable.

Naked Data also has a group of videos presented by David Upton which dive a little bit deeper in terms of system functionality and are really helpful, especially when you need specific help on a certain feature.

Here is one on the Jedox Web System Manager:

Subsets : Picklists

Leave a reply

A Picklist filter helps you choose and order specific elements and is especially helpful in a combo box. You can manually add elements from the element picker on the filter, or refer to spreadsheet or variable values for the elements:

The above selection will deliver a combo box with 3 elements: 2008, 2009, 2010. I could also have achieved the same by using a formula in the combo box properties:

This is one usage of Picklists. Another use is to position specific elements in the combo box. Check out the example here. You can see that on the first worksheet, we have a grid of 4 cells where you can enter in valid elements. The drop list will be filtered based on the pick list behavior that you select. The picklist behavior allows you to insert the elements at the front, back, merge (their normal position in the dimension) or subtract (show only those elements you have specified).

This enables you do things like selecting a default element that you want users to see when they open the report:

A more common approach is to use picklist to insert a default element into a combo box. For example, you may want the current month to always show up as the first element in the list. See the second worksheet in the example you downloaded. Here, we calculate the current month (using a now() function wrapped in a text() function) and pass this value into the picklist subset that dives the combo box. The result is that the user always sees the current month as the first element in the drop list when they open the report.

There one other trick required to get this to work. If you want the report to default to this value, you need to pass it into a variable that you can set when the report is opened. This requires a small macro on the __open function of the report:

function __open() {
$value = ActiveSheet()->Range('D10')->value;
define_variable('Month', $value);
}

Rules for Allocation

Leave a reply

One of the key reasons that people use Jedox is for its planning capability. And any planning system worth its salt has a rules engine built in. A rules engine is a real time calculation algorithm; think of it like excel formulas inside a multi-dimensional environment. This is critical for planning as it allows planners to tweak high level drivers and change result sets down at the base level.

Common uses for rules in a Jedox model include:

Payroll on-cost calculations
Internal charge allocations
FX Rates (especially around multiple eg spot/avg rates occurring at different parts of the model simultaneously)
Project Costing labour from GL Cost Centres down to a Projects Model.

It also facilitates what–if scenarios: for example, what happens to our SKU (Stock Keeping Unit) level margins if the product-labour mix on a specific product group changes 5% next quarter? This type of analysis is difficult to achieve in excel with decent sized datasets; and clunky in a relational environment (and certainly not a job for your garden-variety business analyst). With rules there is no pre-processing: the results are instantly available when the user changes the inputs to the formulas.

Rules in Jedox are in-memory and depending on the type of the formulas you create, calculation intensity can be high. So it is always important that you start with a small area and expand out from there. This will help you debug your rules effectively and monitor memory and cpu usage on your calcs.

A general maxim for rules: if the data is volatile (ie the input data to the rules can be changed anytime) then rule calculation is the way to go. If the data is static (ie changed at predefined points) then you should use ETL to calculate your formulas. The bigger the dataset, the more ‘fine tuning’ you need to do to your rules to ensure performance.

An additional note on real-time formula calculation: Jedox gives you options. As well as rules, you have php, available through web templates (php Web Macros) or via Supervision Server. More on that in a later post.

To help illustrate the concept of a rules based model, we need to tweak the Bikers database.

Download the files here.

Add a text attribute called “Allocation” to the Products dimension.
Add an element in the Measures dimension called “Allocated Expenses”
Open up the Rules Editor on the Orders2 cube
Click on the New button
Copy the rule and paste into the top box, labelled “Rule”.


['Measures':'Allocated Expenses'] = 

IF((!'Products') == "All Products", STET(), 

       IF(PALO.EPARENT("Biker", "Products", !'Products', 1) == "All Products", STET(), 

       [['Measures':'Units']] 
        / 
      PALO.DATA("Biker", "Orders2", !'Years', !'Months', PALO.EPARENT("Biker", "Products", PALO.EPARENT("Biker",        "Products", !'Products', 1), 1), "All Customers", "All Channels", !'Datatypes', "Units") 
* 
PALO.DATA("Biker", "P_L", !'Datatypes', !'Years', !'Months', PALO.DATA("Biker", "#_Products", "Allocation", PALO.EPARENT("Biker", "Products", PALO.EPARENT("Biker", "Products", !'Products', 1), 1)), "Other operating expenses")
)
)

Click the parse button (the little tick on the right side of the dialog). This will ensure that the rule is syntactically correct. Click OK then Close (to close the Rule Editor).
Open up the spreadsheet. Type into the Allocation Attribute the values as you can see in the screen shot below. The easiest way is to double click on the cell to bring up the String Editor. Make sure your spelling is exact. This step is matching Cost Centres (or Clients) to Product Groups so the allocation can take place.

Once you have done this, you should see allocated values being populated against the individual product SKU’s. The basis for allocation in this case is the SKU’s unit proportion of total units for that product group. You can, however, base your allocation on anything you like: revenue split, headcount, fte, floorspace %, etc.

By playing with changing the GL Allocation value by client, or the mapping on the attribute cube, you can see the allocation instantly changing at the SKU level.

Introduction to Security in Jedox

6 Replies

Jedox has a robust security model. You can allocate permissions down to cell level if required. All of its security objects are held within cubes and dimensions so it is pretty straightforward to set up and apply security. Its user security is group and role based, which means that users are members of groups and groups have certain characteristics defined for them by their role memberships.

Jedox can be set up for LDAP and Single Sign on, which enables Network Administrators to deploy user security centrally via an Active Directory server.

Moving down from user security, most objects in Jedox can be managed by security as well. This includes elements, dimensions, cubes; as well as reports, folders, web objects (eg Report Manager). For instance, you might have an ETL developer who only requires the ETL Manager. You can easily set up a role which would only give this access.

The only thing that cannot be managed (at the moment) is databases. I am certainly looking forward to this in a future release as this would open up the potential for more powerful multi-tenancy applications.

User Security is managed by System Manager. Here we can add users, roles, groups and manage relationships between them:

Unfortunately, you need to create your own reports out of the system database to manage Cube and Dimensional Security. After doing this half a dozen times, I realised that standardisation was required here, so I set up a couple of templates. Here are the ones we use for our clients:

Web Security Template (.wss)

Excel Security Template (.xls)

The Web Template:

The Excel Template (password to open: jedox):

These templates allow you to easily manipulate and manage security, including doing things like applying like-for-like security to new users, setting dimensional security, and cube permissions.

NOTE: If manipulating/changing these templates, DO NOT use PALO.DATAC formulas when accessing security cubes. You will get instability. You must use PALO.DATA formulas.

Permissions – The Hierarchy of Rights

Without getting too technical at this point, the key part to remember here is that you need to assign to a group certain levels of access to particular objects. By default, a group with no rights assigned or restricted will have access to everything (you can reverse this default if needed from Jedox 5 onwards).

When assigning security to cells, dimensions or cubes you have the following options:

S (splash): Exists only for the rights object “cell data”. It includes writing into consolidated cells and (indirectly) their children down to the lowest level.

D (delete): Permits the deletion of rights objects.

W (write): Permits the writing of rights objects.

R (read): Permits the reading of rights objects.

N (none): No type of access permitted.

A trap for young players here: assigning write access does not give you the right to enter a zero or delete a value. You can only add data. To get full ‘write’ rights, you need to actually assign Deletion (D) rights. The Deletion right also allows you to delete your data, which sometimes comes in handy.

Pasting Subsets

Leave a reply

Thomas Arampatzis from the Jedox Academy was in town last week for some training. An interesting point that he touched upon with the students in relation to subsets was how the subset array works. Depending on the orientation and dimensionality of the array, you can retrieve different information from the subset. Below is an example of a simple subset using a hierarchy filter.

Single Column

If you paste out a single column, you return the element name:

Two Columns

If you paste out 2 columns into the spreadsheet, you get the alias as well. Helpful if you want to show an element code plus description in a report.

Three Columns

..will give you the hierarchy enumeration (in this case, indent)

This is very handy if you want to test the row for conditional formatting. You can bold and underline totals, for example.

Four Columns (1 row)

An interesting thing happens when you paste out across 4 columns. The Subset array automatically ‘pivots’ and now shows the levels across the columns:

Four Columns (2 rows)

Now the process starts again, but this time vertically. By pasting 4 columns by 2 rows , you get the element name and the alias :

Four Columns (3 rows)

Finally we have completely trans-positioned the data with the indent now included:

To see how it actually works, try it yourself, or download it and check it out here

Managing Web Reporting

5 Replies

Jedox is a broad platform. There is a huge amount of functionality under the covers that you have access to. Sometimes the manuals are a bit cryptic in their description of what is available. So over the next little while, I plan to expose a fair bit of this functionality and how it is used in a business context. Some of it is straightforward and some is quite complex, so bear with me and just take on board what you can understand. Feel free to ask questions.

A key part of Jedox is its Excel add-in. It is by far the most popular and used front end for Jedox, given the ubiquity and flexibility of Excel. The main game, however, is the Jedox Web interface. It is powerful and flexible. More development effort has been put into the web interface than any other Jedox front end, which sort of indicates where Jedox AG sees the future of the product.

Ok, so let’s get into it.

Performance Dashboard

Every wondered who is using your web reports? How often? What are the usage peak times, peak users? All that information is available in the core.log. It is a standard log file format, which is about as useful as an ashtray on a motorbike when it comes to reporting. So we need to parse it with the ETL to get something beneficial out if it.

Download the Performance Dashboard. This .pb file (A Jedox report application bundle) is a handy application which enables you to monitor and react to users hitting your Jedox web reports. It is essentially a cube and a report built by parsing the core.log file. Once you have downloaded it, do the following:

1. Log in to Jedox Web (File Manager)

2. Select an appropriate group to import the .pb file into and import it.

3. Once imported, you should see the following:

4. Download the Jedox-Stats.xml. This is a Jedox ETL Project. Open ETL Manager and import the project.

5. Download the Jedox-Stats.zip. Unzip this file. This is a Jedox database that you need to add to your Jedox data folder (stop the Jedox MOLAP Service first).

6. Start the MOLAP Service; change the path in the ETL Projects parameters / variables to match your system settings. The file path for the Core log is the most important one at this point.

7. Run the job-[00]:CoreStatsBuild job. This will populate your cube with the settings from your system. The default ETL captures 2 measures – reports successfully loaded and reports that have been attempted to be loaded by users.

8. Once you have ran the ETL, open the Performance Dashboard. It should look something like this:

The dash is pretty self-explanatory: the line chart on the left represents all hits and avg/hts per day as per your core.log. You can change server/date period at the top. The middle doughnut represents successful loads vs attempted loads. The chart on the right represents the top 10 reports by that selected period. The real interesting stuff here is the Top Times, Users, Reports. The top times are top hours in the day users hit the reports. Hover over the >> to see more information and click to filter by users for that time. You can select a user for that time and click the >> to see that users top reports for that time. Click on the report and it will take you to a simple analysis of that report by user. Select Top or Flop (bottom reports). Click on a report and it will take you to further analysis by user of that report.

Remember, this is just a template or starting point – it is just a Jedox Web page, and you can edit this yourself and customise it as required. Note: I use cell A1 to hide/unhide rows and columns (via Jedox (HIDEROW() and HIDECOLUMN() functions) used but not displayed. Change cell A1 to 0 to display all hidden rows/columns; enter 1 to hide again.

This type of information is handy, especially when you are centrally deploying an enterprise rollout of Jedox to a wide user base and you need feedback on what reports that users are using and which ones they are not. This ensures you are targeting your reports correctly and it provides insight into how users are interacting with the reports.

the naked leaf

A blog about Jedox Performance Management Technology and how to use it.

Tag Archives: Naked Data

Jedox to SAP Connectivity Part 1 – The SAP Java Connector

Hitting the Sweet Spot

Web Reporting with Dynaranges and Attribute Filters

Extending Web Batch Reporting

Videos and Stories from the Coal Face

Subsets : Picklists

Rules for Allocation

Introduction to Security in Jedox

Pasting Subsets

Managing Web Reporting

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: