A quick update for those who have been using the Excel spreadsheet we posted a while back for running ETL processes. Due to some fundamental changes in Jedox 6, that spreadsheet requires some additional information (such as username and password) to run loads and jobs. You can find the latest one here.
Hope you like it. Please let us know what you think.
Month ends are like haircuts. Towards the end of the process, there are a lot of little snips and clips. Enter month-end adjusting journal into ERP, run Jedox Integrator, rinse, repeat. The adjustments can go on for a long time, so an easy way to process them is critical. Having a custom Jedox web page to run jobs helps, but sometimes customers are so wedded to their Excel environments, that they would rather a “one stop shop” in a spreadsheet.
Necessity, as always, is the mother of invention. For various customers we have created from time to time excel driven ETL processes. We have used a couple of approaches but this latest one uses SOAP calls and web services to execute the jobs, just like the web macros do. This approach has many advantages : It is fast (as it uses the native Jedox ETL api), it is clean (you are not trying to build batch files on the fly, for example) and you can circumvent security issues (like Impersonation) when trying to talk to the server remotely.
Jorge Mendoza, our legendary Excel expert put the following files together. The Excel file is more of an ETL control centre. It allows you to refresh all jobs, review any object (including variables) and run , stop and check the status of jobs. It is pretty cool.
Instead of using this interface, you can attach the following function to your own code to run a job of your choice. As long as you have the module in the file, you are good to go:
CallETL(sServer As String, sProject As String, sType As String, sName As String, Optional vVariables As Variant)
The vVariables is a 2 dimensional array, specifying the variable name and the variable value you want to pass.
You can download the Excel file here. You can also download the .bas file and import it into any excel vba project. You can get the .bas file here. We tested this on Jedox 5.1 SR3, but not on any other versions.
The final point is to get this to work on your server from a client machine, you must ensure that the ETL port is available outside the server. By default, is it not.
On the server, open the tomcat/conf/server.xml file. Search for “7775”. This is the default ETL port. Change it from “127.0.0.1” to “0.0.0.0”. This will not allow you to communicate via web services to this port.
NOTE: What we are doing here is essentially opening up an unsecured port on the server. If this is an issue in your environment, you can restrict access to a specific IP address, password protect the Excel file, or both. In Jedox 6, a new security layer will be introduced around ETL, which will mitigate this issue.
Jedox Web variables are session based, in-memory objects which allow users to transfer values between and around reports. Variables allow you to do things like:
Setting a Variable value – 3 Ways
1. Directly on the spreadsheet. If you type in “=@hello” into a cell , and you would have created your first variable, called hello. If you check the Used variables Manager (on menu, click Data, Variables) , you will see that you variable has been created. It has #VALUE as a value as you have not yet passed a value to it:
Type over the cell again with a value , and you will see the Variable has now changed to that value. You can now refer to @hello anywhere in your spreadsheet or macros and it will return whatever value that you have assigned to it:
2. Using a control to assign a value to it. You can use the date picker, combo box, check box or button to assign a value to a variable:
3. Finally, you can assign a variable & value via a macro:
function __open()
{
//variable name is hello and hello’s value is world
define_variable('hello', 'world');
}
You can also retrieve a variable like this:
function __open()
{
//get variable value
$value = retrieve_variable('hello');
}
You cannot assign a cell value to a variable (for instance, a result of ENAME formula), but you CAN write you own small user defined function to return the cell value to a variable:
function CellToVar($variable, $value)
{
define_variable($variable, $value);
}
you user defined function looks like this in the spreadsheet:
=CELLTOVAR(“variable1”, D3)
Once you assign a variable, you can use it in many areas of you report. One of the more interesting is the relational drill through:
This functionality means that you can construct a variable in a macro and then filter your relational query with this.
When a user logs out, the session variable value will disappear unless the variable has been marked as a private variable. If it is marked private, Jedox stores the last assigned value and the next time a user logs in, that value will be used.
You can see variables in action in a web spreadsheet here.
OK. So you know have got the hang of writing a bit of php code and you can now refer and transfer data around your spreadsheet with ease. However, there is a problem here. The issue with referring to cells in Jedox Web is the same issue you get with referring to cells on a spreadsheet – a cell reference is relative and my php code is fixed: if I insert or delete a row or column on my spreadsheet, suddenly my macro stops working.
As with Excel, Jedox Web can use named ranges. By referring to a named range it means that my code will not fail if the cell moves. Jedox refers to ranges differently than Excel. The trick here is that Jedox uses a Name class, while Excel uses the Range class. Jedox has a Range class too, but not to refer to named ranges…
Download the sample here
// Referring to a Named Range
$value = ActiveWorkbook()->names->item('MyRange1')->value;
We can also create a name range on the fly:
// Creating a Named Range in code:
ActiveWorkbook()->names->add("MyRange3")->refers_to("=10");
Using the Name class is powerful – you are really starting make your web templates bulletproof when you employ this method for referring to cells or ranges.
The Jedox Macro Engine implements PHP to interact with the Jedox Spreadsheet. One of the most used and basic functions is referring to a cell reference in the PHP code. You can put data into a cell on the spreadsheet, or you can read from that cell.
Download the example spreadsheet here
Reading Cells
// read a cell value:
$value = ActiveSheet()->Range('E7')->value;
return __msgbox($value, 'Returning a Cell Value', 'Info');
// or you can use
return __msgbox(ActiveSheet()->Range('E7')->value, 'Returning a Cell Value', 'Info');
Writing Cells
// Write a value into a cell:
ActiveSheet()->Range('E10')->value = "hello again";
Note, the warning in my previous post. Depending on the complexity of the calculation and the type of cell you are referring to, the calculation timing may be out when referring to cells.
For instance, if you have a =PALO.DATA in the cell that is volatile and you are using a Macro to read the cell value, depending on the order of calculation you can pick up the wrong result. If you are trying to read a value from a cell that contains a PALO.DATA formula, sometimes it is easier to just refer to the cell in the cube using the application-> command in the Macro.
Returning Cube Values
// Get a value from a cube :
$value = application()->palo_data("localhost/Demo","Sales", "All Products", "Europe", "Jan", "2011", "Actual", "Units");
Note here that the formula palo_data, not palo.data. This is because the Palo/Jedox functions implemented in the Macro engine are directly from the Jedox PHP API. Also, the point/full stop/period is a reserved character in PHP. It is the concatenation character between strings.
Finally, an interesting/semi -advanced concept for you. Sometimes you want to retrieve a cell value that is not necessarily an intersection in the cube, but the sum of certain subsets across one or more dimensions. For example, want to retrieve the Total Unit value for a collection of top 10 Regions. Unfortunately, you cannot refer to a subset as a substitute for an element name in a PALO.DATA formula (yet). But, we can use a macro to achieve the same effect.
Returning a Subset
// Return data cell based on a subset on the Regions Dimension.
// NOTE: The PHP subset is different to the spreadsheet function in 2 ways:
// 1. Replace all . with _ (eg palo.subset becomes palo_subset)
// 2. all blank arguments need to be replaced with NULL (eg PALO_HFILTER("West",FALSE,FALSE,,,,,,) becomes PALO_HFILTER("West",FALSE,FALSE,NULL,NULL,NULL,NULL,NULL,NULL))
$ArraySS = application()->PALO_SUBSET("localhost/Demo","Regions",1,NULL,PALO_HFILTER("West",FALSE,FALSE,2,NULL,NULL,NULL,NULL,NULL),NULL,NULL,NULL,NULL,PALO_SORT(1,0,NULL,0,NULL,0,1));
// Get array size for looping
$ArraySize = count($ArraySS);
for ($i=0; $i<$ArraySize; $i++)
{
$value = application()->PALO_DATA("localhost/Demo","Sales", "All Products", $ArraySS[$i][1], "Jan", "2010" ,"Actual" ,"Units");
$ValueTotal = $ValueTotal + $value;
}
ActiveSheet()->Range('E22')->value = $ValueTotal;
The Jedox Web Macro Engine is one powerful beast. It is based on PHP Hypertext Pre-processor (PHP5) and it enables power users and Jedox BI developers to write PHP code which is executed within Jedox Web’s Core run-time. What this basically means is it gives you the power of something like VBA on the web. It allows you to script event driven macros that can be executed by the user on demand.
Why on earth would you want to write macro scripts in Jedox web? For all the same reasons that you would write VBA in an Excel workbook. It helps provide an additional layer of sophistication, flexibility and power to the users of your reports.
Before you get started with Macros, a couple of points to note:
If you come to Jedox web from the world of PHP, you are in luck. The Macro Engine gives you complete freedom in terms of the PHP you can write, as well as additionally implementing an extension to refer to spreadsheets. Existing PHP extensions can be added, which allow you to extend your application way beyond just reporting off cubes.
If you come to Jedox web via VBA (like me and I suspect a vast number of people reading this) , PHP can be a little daunting at first, but if you persevere you quickly get the hang of it.
I will be publishing a few examples over the next little while. Some examples are simple and some may be a little more advanced. If you are new to macros, then this may be a good place to start.
the naked leaf 