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:

sap-part1-1

  • 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:

sap-part1-2

  • 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:

sap-part1-3

  • 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:

sap-part1-4

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

adam-gilchrist

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.

google_whiteboard

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

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

a-filter_1

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.

a-filter_2

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:

a-filter_3

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

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:

  1. 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.
  2. 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.
  3. Reporting Filename. Ignore this, as it will be generated automatically as part of the process.
  4. File Type. Select xlsx or wss
  5. 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:

batchprinting3

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.

batchprinting2

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

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

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:

picklist1

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:

picklist4

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:

picklist2

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.

picklist3

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

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.

rule_excel_ss

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.

2014-05-19 10_02_36-Jedox Allocation.xlsx - Excel

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.