flat table ->how to: import and do Olap

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

  • Found a workaround for adding many many elements ;)

    background:
    - I'm back to the previous version (PaloSuiteIntegrated_ramp_up.exe)
    (because the new version is missing the Olap Manager (in Palo Web) - by design)
    - using Excel is not an option for me

    Problem:
    - with Palo Web, there's the problem of entering many elements in a dimension
    (<-> copy&paste does NOT work)

    workaround:
    - get the columns from an SQL statement
    - copy these into a text-file,
    - create a *.vbs script which enters all keystrokes (thus creating all elements auto-magically;-)


    details for the workaround:
    - using Squirrel SQL (squirrel-sql.sourceforge.net/)
    issuing a select distinct, like e.g.:

    select distinct myDimensionColumn01
    from myDatabaseTableAbc



    (this outputs a list of all the elements of the dimension)

    The result can be marked (click onto first column, then press CTRL+A)
    then copied (CTRL+C)
    ->and thus I've copied that into a text-file, say with notepad, into file "in"

    following is a batch-file (Windows Xp) which writes a *.vbs script for this
    (if %1 given, then that's the input file,
    exampls:
    makenew.bat in
    makenew.bat in_all_hierarchy-elements.txt
    )

    Thus, I've saved the following as file makenew.bat:


    @echo off
    SETLOCAL
    set in=in
    IF NOT %1x==x SET in=%1
    set out=makenew$$$.vbs

    echo ...%%in%% == %in%
    echo ...%%out%% == %out%

    set f=%in%
    IF NOT EXIST %f% echo ...ERROR: not found: %f%
    IF NOT EXIST %f% pause
    IF NOT EXIST %f% GOTO halt
    find /n /v ""<%in%

    >%out% echo '# auto-created %date% %time%
    >>%out% echo 'http://msdn.microsoft.com/en-us/library/system.windows.forms.sendkeys.aspx
    >>%out% echo ' -
    >>%out% echo Option Explicit
    >>%out% echo Dim pause
    >>%out% echo pause = 750
    >>%out% echo Dim objShell
    >>%out% echo Set objShell = CreateObject("WScript.Shell")
    >>%out% echo Wscript.Sleep 2000

    for /f "tokens=*" %%a in ('sort^<%in%') do (
    echo %%a
    >>%out% echo WScript.Sleep pause
    >>%out% echo objShell.SendKeys "{ENTER}"
    >>%out% echo WScript.Sleep pause
    >>%out% echo objShell.SendKeys "%%a"
    >>%out% echo objShell.SendKeys "{ENTER}"
    >>%out% echo.
    )
    >>%out% echo WScript.Quit
    >>%out% echo 'EOF %date% %time%
    :end
    echo ...%%out%% %out%
    :halt
    ENDLOCAL
    ::v1.02,1004,*.bat


    the output file (makenew$$$.vbs) is the script which will enter all the keystrokes,
    BUT, it will enter wherever the focus is,
    BUT, I simply need to focus on Palo Web for that

    (tip: TO MERELY TEST: put the focus into an empty notepad file, and you'll see what the script does)
    (note, need to be sure there's no pop-up while the script is running, otherwise the keystrokes "disappear" in that popup window)
    (TO QUIT while running, e.g.: close the cmd.exe window)

    So basically:
    I clicked into the window of Palo Web where the elements of the dimension are to be created, (as if I would enter them now manually),
    but then I clicked at the cmd.exe window and entered the command:

    cscript makenew$$$.vbs


    THEN, pressed ENTER, then QUICKLY clicked with the mouse onto the Palo Web window (from above), giving it the focus
    (I got 2 seconds for doing that - see line "Wscript.Sleep 2000" ->increasing 2000 to 4000 would give me 4 seconds)

    Thereafter, the keystrokes are entered - all elements are created.

    By the way, before updating the above, I had to delete all elements. Instead of deleting all one-by-one (with the mouse...),
    I clicked the top element, then, while holding down the SHIFT-key, I clicked the last element (to be deleted)
    -> this marked all elements (thereafter released the shift-key)
    -->this allowed to delete ALL marked element at once
    (clicking onto the delete-button at the bottom of the Firefox window of Palo Web)

    HTH

    The post was edited 5 times, last by arny ().