querying relational database

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

  • querying relational database

    Hi Gents,

    I am currently trying to establish a query to a relational database, from Palo Web. I know Palo is not initially built for this, but I need to offer my user the possibility to view the details of a cube cell. I think it is a feature that Palo will need to offer in many scenarios. And quey has to be relational, because loading detailed data in a cube is of course counter-productive.

    Say for example, my user views his consolidated balance for a given month on a specific account, and then wants to see the detail GL postings for this cell.

    Below are the solutions I have tried until now:
    1- use the drill-through option using PALO SVS (premium edition): Unfortunately, this feature is offered in the Excel client, but not in Palo Web (please correct me if wrong)
    2- using the ODBCINIT / ODBCDATA / ODBCEXEC functions, and passing parameters from Cube report:
    This solution is not really sexy, as you need to query almost cell by cell the datas, and because it is not really dynamic (as you can't predict the number of rows that will be retrieved from you query)
    3- using the native Excel query editor (through Microsoft Query editor): would have been wonderful, but unfortunately this excel functionality is no implemented in Palo Web.

    I just wanted to know if any of you guys have experienced such an issue? If so i'd be very happy to have your inputs.

    Thanks in advance for any help,
    Pierre
  • Hi,

    how many rows do you import from your DB? If not too many, your
    4) should be "Importing data in a Staging cube"
    You can create a copy of your current cube with an additional couter dimension (elements 1 to 100) and import your detail data there.

    Regards.
  • RE: querying relational database

    Robert, George,

    Thanks you for your replies.

    Robert:
    Do we mean having one dimension as a counter, and all other fields (Invoice number, amount, currency, etc.) under the dimension measure? Then I suppose displaying Dimension "Counter" in rows, and "Measures" in columns? It's the only way I can see to display fields in a "relational" way. Please let me know if I understood you correctly.

    Indeed, that would not be optimal for a large amount of data.

    George:
    I'm not really a big fan of macros and php (maybe because I am disastrous at it ;) ) But maybe I'll explore that option in case no other works.

    Thanks again
  • Hi,

    try to mix the relational and the dimensional approach.
    Take most dimensions of your cube to a new cube and add the counter dimension and a larger measure dimension (everything that's not in the dimensions).
    Calculate the row number in your extract within each unique set of elements (defined by your dimensions).
    Example: ROW_NUMBER() OVER(xy) PARTITION BY (YZ)

    Date Invoice Count
    1.1. ASDF 1
    1.1. sdfa 2
    1.2. lkjdfg 1
    1.2. asdf 2
    1.2. asdg 3

    (if invoice is not a dimension but date is)
    It's only a workaround, but helps in many situations.

    Regards.