Universe based on Archive tables

[Former Member]

Hi All,

I have a question regarding Universe development on Archive tables. We have couple of tables where the data is archived on daily basis with the date as table name extension. we need to build monthly reports pulling data from those archive tables is it possible to build Universe based on archive tables or any other work around? As these table are created dynamically by batch jobs at the end of every day.

Thanks in Advance!

[PPaolo · Accepted solution]

Hello

there is no pre-build solution for this but there are a couple of tricks which you might envision (and probably many others, if anybody has other suggestions please post).

- *recommended* Create a view in your database which unions the archive tables. You need to programmatically update the view definition each time you add a new table. In the universe you just use that database view as a source table

- Create a derived table in your universe which unions the archive tables. You need to programmatically update the derived table definition each time you want to add a new table to it (and then re-publish the universe)

- If you need to let the end user access only a specific table and the table name is something like MyTable_123456  then you could create parameter in the universe asking for the final part of the table name (as long as it is numeric) and then a derived table in the universe with a syntax similar to this: "Select * from MyTable_@prompt(thenumber) " 

Hope that it helps

Regards
PPaolo

[PPaolo · Accepted solution]

another solution to let the end user choose a single archive table (independently of its name).

+ You need a reference table in your database containing all the names of the archive tables (you can add a name each time you complete the batch job of creating a new table)

+ In the universe data foundation you create a list of values based on SQL which selects the archive table names from the reference table, let's call it    'TableNames'

+ In the universe data foundation you build a derived table using the following syntax:

SELECT *

FROM @Prompt('Select table','K','TableNames',mono, constrained, persistent,{'SampleTable'})

notice that SampleTable is just the name of one of the archive tables (you need this just to create the derived table, it won;t be used afterwards).

This proposal should work fine with any kind of name of the archive tables but you still have the constraint that all archive tables must have the same structure (same column names).

Regards

PPaolo

[Former Member · Accepted solution]

Thanks for the solution PPaolo!