flat table ->how to: import and do Olap

    flat table ->how to: import and do Olap

    given:
    - flat table which contains all that is needed for doing Olap
    - the above is on a MySql DB
    - I know Olap theory like Star-Schema, dimensions, etc.
    then,
    how to:
    - get the data into Palo
    - how to get Palo to do olap
    (how to tell Palo which of the fields in the table are the dimensions,
    and how Palo should aggregate values)
    OK, so I noticed:
    jedox.com/wikipalo/de/index.php/Palo_ETL_Server_-_Variablen

    this works fine for getting to my MySql Database
    (using a variable in the server-name)

    also,
    jedox.com/wikipalo/de/index.php/Palo_ETL_Server_-_Extracts

    made me create a relational extract,
    and within that:
    - Test == OK
    - Datenvorschau == OK (I see that it gets the data from the MySql db's table)

    then -> what next?
    How to tell Palo to
    -- build the dimensions from the columns
    -- get the "load-data" into the cube (Lade-Daten -> Würfel befüllen)
    -- aggregate in the cube (Werte im Würfel vorberechnen)
    S1 =manual named "Palo ETL Server 3.0"

    So S1::page52 gives info on
    - data source
    - load mode
    - BUT NOT on "Zielverbindung" -> what's that?

    -the TEST does not run:
    ErrorMessage==
    "Die Definition der Komponente "LoadDwhMonthlyToCube01" ist ungültig.Fehlermeldung: Failed to test EtlProj01.loads.LoadDwhMonthlyToCube01: connection ZielVerbindung01 not found.
    "

    Solution?
    The Palo Suite came with an example;
    in the ETL-Manger, there's "importBiker";
    underneath that, there's: Verbindungen, Palo, biker
    within that there's: TEST
    if I click TEST, it does NOT work;
    ErrorMessage being==
    Die Definition der Komponente "biker" ist ungültig.Fehlermeldung: Failed to open OLAP connection biker. Please check your connection specification. Could not connect to palo server at host 'localhost' on port '7777'

    what's that? The example is wrong? There some general error with Palo?
    ???
    The error persist if replacing "localhost" with the IP-address;
    I could not check if the error persist if running that on the PC where Palo itself is installed - because on the PC there's only the MSIE (version 8 ), and Palo becomes practically unusable in that.
    It makes for a nice avatar, though - see avatar ;)
    OK, so I downloaded Firefox and installed it into the VMWare PC where Palo is installed - and tatata! - the error still persists!

    That is to say:
    on the PC where "PaloSuiteIntegrated_ramp_up.exe" was installed,
    starting "Palo Web" (that's the only link Palo has created),
    logging on,
    then ETL-Manager, ETL-Server, importBiker, Verbindungen, Palo, biker
    then clicking "Test"
    gives:
    Die Definition der Komponente "biker" ist ungültig.Fehlermeldung: Failed to open OLAP connection biker. Please check your connection specification. Could not connect to palo server at host 'localhost' on port '7777'

    So... how do I get Palo to do (M)Olap?

    I can connect to my MySql Database Table from Palo...
    but how do I get Palo to go build a cube with that data,
    calculate values (aggregating),
    and then show me the results in a multi-dimensional view?
    attended a Webinar. All ran in the same VM. Obviously one does not get all the errors that comes with the "PaloSuiteIntegrated_ramp_up.exe" (default) installation - but users would not all come to the same PC, so I'm still wondering how Palo can be put to work.
    "Palo Olap Server 3.0, Handbuch",
    pages 29 and 30
    ->
    find file:
    C:\Programme\Jedox\Palo Suite\palo\data\palo.ini
    modify it:
    >>
    #old: http 127.0.0.1 7921
    #new:
    http "" 7921
    <<

    save file,
    reboot computer,
    from a second PC (i.e. not the same PC on which Palo was installed), start Firefox,
    logon to Palo Web,
    -goto ETL-Manager, ETL Server, importBiker, Verbindungen, Palo,
    double-click: biker,
    --right-hand pane should give register/tab "importBiker"
    ---on that:
    ----in field "Port" ->change 7777 to the port that you found in the above mentioned palo.ini file,
    for me that was: 7921
    (I guess 7921 is what the PaloSuiteIntegrated_ramp_up.exe puts during its default-settings installation)
    ----click the Link "Test"
    (note, I could not first see that "Test" Link -> the display is a bit buggy; first I had to expand the browser's
    window to the right/horizontally - until at the very right the "Test" link showed up)
    -----Pop-Up window appears with Text:
    "Es könnte nötig sein, dass Sie Ihre Änderungen erst speichern müssen, bevor Sie auf diese verweisen können. Sollen die Änderungen jetzt gespeichert werden?"
    (i.e. - save settings - answer: YES/JA)

    Yipiiiiiiiiii! Now, for the first time, the example Verbindung tests OK!
    (doesn't anybody screen these things before you deliver stuff?;-)

    so there is hope that it's possible to get Palo to create a cube and use it...
    DEFINE S1=Palo ETL Server 3.0, Manual, Nov.20th, 2009


    S1, page 51 says that Loads write from e.g. an Extract into a cube.

    I already added an Extract; I'm testing it again:
    a)
    I use another software, "Squirrel SQL"(squirrel-sql.sourceforge.net/) to verify that I can get to the database in question
    and yes, that is possible.(just to be sure)
    b)
    from a second PC (i.e. not the same PC on which Palo was installed), start Firefox,
    logon to Palo Web,
    -I go to ETL-Manager, ETL Server,
    then: dwh01(that's what I created),
    then: Verbindungen,
    then: MySql(that's the Type I chose->it's a MySql database)
    then: double-click "dwh" (that's the name I gave to the connection)
    --right-hand pane should give register/tab "dwh01"
    ---on that:
    ----I clicked the Link "Test"
    (note, I could not first see that "Test" Link -> the display is a bit buggy; first I had to expand the browser's
    window to the right/horizontally - until at the very right the "Test" link showed up)
    ----this works OK

    likewise:
    -I go to ETL-Manager, ETL Server,
    then: Extracts,
    then: Relational (that's the type I chose)
    then: double-click: getDwhMonthly (that's the name I chose)
    --double-click: "Datenvorschau" (might need to expand browser-window to the right to see that link)
    -->this works OK
    (I can see the same data-sets/rows as when doing the same SELECT-statement with Squirrel-SQL)

    S1, page52, top: says that a cube-load will CREATE a cube if that cube is not yet present
    (Fantastic, that's what I want)
    hence, I do:
    -ETL-Manager, ETL Server,
    then: dwh01 (that's the name I chose)
    then: double-click: "Loads" (its indention suggests that it's a sub-topic of "Extracts" - I'm ignoring that)
    -- the load-pane is empty
    (pressing F1 pops up the help of Firefox - don't you guys use the GWT for your ext?;-)
    --- click funny pink/yellow icon
    ---- right-hand pane changes:
    ----- field "Name": change "NeuLoad" to "dwhMonthly" (the name of my choosing)
    ----- Listbox "Typ": pick: Cube
    ------ now the right hand pane gets zillons of additional listboxes/fields
    S1, pages 52 and 53 are vaguely resembling an explanation - so with that:
    ------- Listbox "Source/Datenquelle": picked "getDwhMonthly" (that's the relational extract that I made)
    ------- Listbox "Target Connection/Zielverbindung":
    S1, page 53 vaguely resembling an explanation, says (and I quote):
    "Target connection Target connection in which the cube should be completed".
    Hey presto! A Target Connection is a Target Connection?
    Please, what's a Target Connection?
    DEFINE S1=Palo ETL Server 3.0, Manual, Nov.20th, 2009

    ...so the index of S1 has an entry "Connections"; this gets me to page 16;
    page 16 makes me do:
    -ETL-Manager, ETL Server,
    then: dwh01 (that's the name I chose)
    then: Verbindungen/Connections
    there:
    -- click funny yellow/pink icon
    --- right hand pane changes
    ----Field "name": changed "NeuConnection" to "dwhMonthlyOutputConnection" (just the name of my choosing)
    ----Listbox "Typ": what's that???
    well, S1, page 10 says under "4.1.5 Palo":
    "Here, the Palo OLAP Server 2.5 or a higher version is necessary in order to import or export dimensions
    and cubes"
    Well, I want to export to a cube, so I guess that means I should put "Palo" as type.
    -----so I pick: Typ== Palo
    -----right hand pane gets 5 new fields
    Uh, oh.
    Well, S1, page 10 continues:
    "The following information about the Palo OLAP Server is to be entered for the connection"
    Host, Port, User, Password, Database.
    (Hey Presto! I guess that's why these fields are named: Host, Port, User, Password, Database)
    But how am I to know?
    ->Host -> I put the IP of the PC where Palo is installed
    ->Port-> how am I to know? Oh, I forgot, when I fixed your Biker example (see earlier in the thread-blog),
    I found out that this might be the port as found in the file:
    C:\Programme\Jedox\Palo Suite\palo\data\Palo.ini
    so I put that 4-digit number, which I found in the Palo.ini file, now into the field "Port"
    ->User/Benutzer-> how am I to know? Well I just put the same user with which I log on to the Palo Web
    ->Password/Kennwort-> likewise
    ->Datenbank/Database-> how am I to know?
    This is not a listbox, it's a field (at least right now).
    So which database?????????????
    ...so I go check more documentation...
    (as to why the ETL documenations is in English whereas the Web and Palo documentation are written in German - I have no glue)

    DEFINE: PalSrv = "Palo Olap Server 3.0, Handbuch, 30.11.2009 (11 being the month)

    PalSrv's index has an entry "Datenbank anlegen 227"

    Yet, PalSrv's page 227 does not look at all like the "Palo Web";
    yet, the PaloSuiteIntegrated_ramp_up.exe has only create one icon, so "Palo Web" is all I can (or know how to) start.

    OK, so I go try to figure how to make that documentation work somehow;
    PalSrv, page 227 has a screenShot for "Verbindungs-Assistent", so I go look for that in yet another documentation:

    DEFINE: PalWeb= "Palo Web 3.0, Handbuch, 15.12.2009 (12 being the month)
    PalWeb's index has 3 entries:
    Verbindungen zu Datenbanken · 50
    Verbindungs-Assistent · 52
    Verbindungs-Manager · 50

    OK, so...
    PalWeb, page 50 says (and I quote)
    "Hier können Sie aus Vorsystemen Quelldaten extrahieren, danach passend für eine Palo Datenbank
    transformieren und danach in die Palo Datenbank laden.
    Die Vorgehensweise ist ausführlich im Palo ETL Server 30 Handbuch beschrieben"
    ->I guess in plain English that's: "Go figure where we documented that"

    Uh. Oh.
    PalWeb, page 51: "13.1.1 Anlegen einer Datenbank"
    makes me do that:
    (in Palo Web)
    -OLAP Manager
    --right hand pane changes
    ---(might have to expand browser window to the right)
    ---in right hand pane, in tab "Datenbank" click Icon as shown in the upper screenshot on page 51 in PalWeb
    (an arrow is pointing to the right button in that screenshot)
    (can't you guys add tooltip popups with Ext? Uhm, or is it the GWT which would allow to add that?;-)
    ----a new window pops up (as shown in the lower screenshot on page 51 in PalWeb)
    -----following instructions on page 52 (in PalWeb), I simply enter "dwhMonthly" for the new database name and then I click onto the button "Fertigstellen"
    -----Message-box pops up: Neue Datenbank wurde erfolgreich angelegt
    -----OK

    so now I go back to where I last got stuck:
    (in Palo Web)
    -ETL Manager, ETL-Server,
    then: dwh01 (name I picked)
    then: Verbindungen/connections
    then: Palo (the type I chose)
    then: "dwhMonthlyOutputConnection" (the name of my choosing)
    as mentioned earlier in this saga (under "Host, Port, User, Password, Database."),
    the field "Databank" was not explained in the documentation;
    so now I put into that field: dwhMonthly (that's the name I chose for the new database)

    there's a link called "Test" on the very right hand side in that pane, so I go click onto that...
    --message box pops up: "Es könnte nötig sein, dass Sie Ihre Änderungen erst speichern müssen, bevor Sie auf diese verweisen können. Sollen die Änderungen jetzt gespeichert werden?"
    ---click: JA (i.e. yes, go save that stuff)

    and would you believe it, it tests OK
    (nice pice of German literature art pops up: "Die Definition der Komponente "dwhMonthlyOutputConnection" ist gültig.")


    Because just beforehands I had to save that stuff, the left pane no longer shows me which windows are being edited.

    So instead, I need to scroll up this blog-thread to figure what else needs to be done now.
    Oh, the Cube.
    So...
    DEFINE palEtl=Palo ETL Server 3.0, Manual, Nov.20th, 2009

    as mentioned for an earlier century of this voyage,
    palEtl, page 51 says that Loads write from e.g. an Extract into a cube.
    Yet, the tautology on page 53 did not help me (Target connection is a Target connection? Great, so what's a "Target Connection", or in German a: "Zielverbindung"?)
    ...so back to:
    (in Palo Web)
    ETL-Manager, ETL Server,
    then: dwh01 (name I chose)
    then: Loads
    then: Cube (type I chose)
    then: dwhMonthly (name I gave)
    -Listbox: "Zielverbindung"/Taget Connection: clicking onto arrow I can now chose "dwhMonthlyOutputConnection"
    -Listbox: "Würfelname des Zieles"/Target Cube Name
    How am I to know?
    ...(cont.)...

    -Listbox: "Würfelname des Zieles"/Target Cube Name

    page 53 of palEtl says nothing for "Target Cube Name" except "Target Cube Name". (What? Not even a Tautology?)
    Nevermind that it's a list-box and not a field. I simply type into it "dwhMonthlyCube" anyway.

    -Listbox: "Modus"/Mode:
    (my, this must be my lucky day)
    page 53 of palEtl actually explains something under the header "The following load cube modes exist";
    ->this makes me chose: "create"

    -Listbox: "Splashmodus"/Splash mode
    quote from page 53 of palEtl:
    "Further information on the topic of Splashing can be found in the Palo OLAP Server Handbook."

    ...so,
    DEFINE: PalSrv = "Palo Olap Server 3.0, Handbuch, 30.11.2009 (11 being the month)

    PalSrv's index has these entries:
    Splash · 150, 153, 154, 155, 271, 281, 377, 382, 385
    splashen · 141, 150, 282, 283
    Splashing · 149, 159, 162, 281
    Splash-Parameter · 150, 153, 154, 155, 385

    (Great, so I'll go get lunch now)
    ...(cont.)
    so the PalSrv documentation seems to say that Jedox-understanding of "splashing" is overwriting data.

    -Listbox: "Splashmodus"/Splash mode
    ->default

    (Did I mention that I had not found documentation about what the default mode of Splashing actually means?)


    -Listbox: "Umgang mit fehlenden Dimensionselementen"

    DEFINE palEtl=Palo ETL Server 3.0, Manual, Nov.20th, 2009

    on page 52 of palEtl, that Listbox is simply missing!
    opening up the Listbox, I find 3 choices: defaultBase, defaultConsolidate, warning.
    (Is this some kind of puzzle?)

    OK, so I put "warning", because that sounds as if it won't do anything - just warn. And maybe the warning will explain something.

    -Checkbox: "Paralleler Load in Datenbank für Drill Through"/Parallel Load in Datastore for Drill Through

    page 53 of palEtl says: "Parallel to the cube export, (...)"
    No, I don't want to export (from) a cube, I want to create a cube.
    Do I need to check this to avoid an unparalelled load? What's that?

    Which Database are you talking about - the source or the target? The Target is a cube, so do you mean to say that the Database refers to the source?
    Quote:
    "Aggregation This parameter describes whether the source and existing data in the relational target system still need to be aggregated during the load."

    What???
    >>Whether the source (...) in the (...) target system?<<

    The source is the target? How can that be?
    What's source and what's target?

    And why are you asking me about aggregation? Do you have cubes which do not aggregate?

    Will wonders never cease to exist?
    ... cont. ...

    I have all day for this, but to cut short for the upcoming weekend...
    ...lacking documentation, I just make some wild guesses:

    -Checkbox: "Paralleler Load in Datenbank für Drill Through"/Parallel Load in Datastore for Drill Through
    ->checked

    -Listbox: "Relationale Verbindung"
    ->left that untouched (it's empty)

    -Listbox: "Aggregation"
    ->true

    -on the very right of the right pane, there's a link called "Test"
    --double clicked that
    ---message-box pops up: "Es könnte nötig sein, dass Sie Ihre Änderungen erst speichern müssen, bevor Sie auf diese verweisen können. Sollen die Änderungen jetzt gespeichert werden?"
    ---Ja/yes (whatever that piece of Southern German Literature means)

    ---and would you believe it:
    a message box pops up with "Die Definition der Komponente "dwhMonthly" ist gültig."
    (whatever that means in Southern Germany)
    ---OK

    Because just beforehands I had to save that stuff, the left pane no longer shows me which windows are being edited.
    So instead, I need to scroll up this blog-thread to figure what else needs to be done now.

    Oh,
    DEFINE palEtl=Palo ETL Server 3.0, Manual, Nov.20th, 2009
    palEtl, page 51 said:
    that Loads write from e.g. an Extract into a cube.

    Great, but, then why doesn't it do it?
    Do I need to double-click on the Load, or what?