Trying to connect Jedox Excel to SQL Database

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

  • Trying to connect Jedox Excel to SQL Database


    I will be grateful if you could lend me some help.

    I'm trying to work with an OLAP cube on a SQL database but I have some difficults.
    I'm using the Modeller but I can't connect directly to this database, probably because it was a gigant error.
    I try to connect to Jedox, I put the server name and port, but the
    result is that "There is not valid licence". I don't understand because I
    have valid licence till 14/06/13.

    Could you tell me what is the
    proccess to create a connection to an existing cube on a SQL database? I think I
    have to create a conexion to the cube using the ETL Manager, isn't it? Or create a new cube?

    I would really appreciate any help in this matter.

    Lots of thanks.


    (I write you from Chile although I'm spanish. Excuse any mistake, please.)
  • Hello Chile,

    Jedox has its own internal format for storing data for cubes. You cannot manipulate cubes stored in external datasources without first going through Extract Transform Load process.

    If you're using Jedox 5.0, try the following:

    First, make sure you've set up your dimensions. You must have dimensions created before you create any cubes. Use the "OLAP Manager screen" to do that.
    Second, create your cube using the OLAP manager screen. This will be the target for the ELT job.

    1. In ETL Manager -> ETL Server, under one of the projects (by default you have "ETLTasks" and "sampleBiker", create a new Connection by click on "Connections" in the navigation pane.
    2. Configure the connection using type=Mysql.
    3. You will be prompted to fill out the Host/Port/Database/User/Password.

    After configuring the connection, you will need to Extract the cube data.

    Use Extracts, Type=Relational. Type in your sql query and verify that the field structure has your dimensions in it (or you can use a Transform job later to remap the fields, but its easier to do it here.)

    Finally, use create a "Load" tasj to persist the data from your extract task into your OLAP cube.

    You will run all of this by wrapping the load task in a Job.
  • Hi,

    Again, thank you so much for your response.

    Do you work with a free version of Jedox/Palo?
    First of all, I tried to work with Palo Community Edition 3.2 because it's free. The problem was that I don't know how to connect to an existing cube or to a SQL server database. I was thinking that I must necessary use the OLAP Manager that exists on Jedox Premium 5.0.

    On the PALO Web 3.2 Menu I have four items: File Manager, User Manager, ETL Manager and Connection. Using only the ETL Manager could I create a conexion to an existing cube not in Jedox or to a SQL server database to create a new cube?

    I would really appreciate any help in this matter.

    Lots of thanks.
  • Sorry. I haven't played around with Palo 3.2 :(

    I did find this on-line:…32-manual.html?download=1

    The ETL websetup looks very similar to that used by Jedox 4.0 and 5.0.

    Are you saying the Community Edition doesn't include the Create Connection items on Page 10? (or that the webpages are missing entirely?)

    If you don't get a response for this, check out the Palo ETL forum. I vaguely remember seeing more threads on 3.2 CE ETL there (I don't think the Palo for Excel forum has much ETL related posts...)
  • Thank yo for the link.

    Working with Palo 3.2 I have crated a connection to my SQL database but the load fails I think because I try to create dimensions manually and not using the OLAP manager. I think this is the problem but I'm working on it yet... For ecah dimension I have created a connection with a SQL query and its runs individually.

    It's true, I must write on the ETL forum... :)
  • You can populate your dimensions using ETL. Make sure you run the ETL to create all of your dimensions *BEFORE* you create your cube (Jedox, will not allow you to create a cube before creating the underlying dimensions.)

    Note: I've done this in the past where I've used .CSV files on disk and created dimensions using the Jedox ETL pipeline (Connection is type file, Extract out the elements in the dimension, Load them into the dimension and using a Job to run the entire action.)