Universe Design Practice

[former_member672670]

Hie Guys,

Is it a normal practice to build a Universe from a SQL query when there is no access to the database. What are the pros/cons and the things to consider when doing so? Your feedback is appreciated. Thanks.

[former_member4998]

Hi

You want the DB access to see the tables and data. Without access you can’t test the data.

Please find the below doc.

http://www.chiboug.org/CHIBOUG/NewsDoc/NewsDoc13008.pdf

[former_member672670]

i do not have direct access to the database, but i have an ODBC connection created that has all the tables. Using a SQL query from the connection, I need to figure out the universe.

[Former Member]

If you have an ODBC connection and select privileges on the tables (and views) that you need, then what more do you think you nned? The only thing that I can think of would be execute privileges on any functions that have been created to support the database.

[former_member182521]

Universe will be created to abstract the Database level information and provide only the Business Context which is understandable by the business user. It is always possible to create universe from the SQL query.

[former_member672670]

Correct me If I am wrong. So, when using the SQL Query to create the universe, I just need to look at the Joins and the "ON" Clause in the query to join the tables in the Universe. What about the other filters in the query? Do I just use them at the report level as a custom SQL?

[former_member182521]

You can still create filters at Universe level

[Former Member]

When you say "using the SQL Query to create the universe", what do you mean?

[Former Member]

If you have ODBC connection and SQL query then just create a universe with all the table used in the query and assign the joins between the table and get all the objects in the select clause in a logical grouping of class. That it self will be your universe.

However if you have some complex logic or subquery in the query then that logic will have to be handled separately.

[former_member672670]

I have an ODBC connection to the database and a SQL query generated from the database that contains the columns/objects required in the Webi report. The SQL query contains aliases, conditional filters, sub-queries and aggregate functions. Based on these facts, I need to build the universe.

[former_member672670]

Below is the SQL query that i have. As you can see, it contains aliases, conditional filters, sub-queries and aggregate functions. 

SELECT

DISTINCT

tc.category_descr AS cosa,

tcv_project.custom_value  AS project_id,

ti.issue_title AS project_name,

tcv_sponsor.custom_value  AS exec_sponsor,

tis.own_employee_nbr AS bmpe_name,

DATE_FORMAT(tis_start.step_dt, ‘%m/%d/%Y’) AS start_dt,

tcv_end_dt.custom_value AS end_dt,

ts.step_descr AS current_phase,

CONCAT(‘(‘,DATE_FORMAT(ta.insert_dt, ‘%m/%d/%Y’),’) - ‘,ta.append_txt) AS status_descr, 

tcv_benefits.custom_value AS benefits,

tcfo.option_descr AS benefits_type,

ti.issue_id

FROM tbl_issue AS ti

INNER JOIN  tbl_category AS tc

ON ti.catagory_id = tc.catagory_id

LEFT JOIN tbl_custom_value AS tcv_project

ON ti.issue_id = tcv_project.issue_id

AND tcv_project.custom_field_id = 126

LEFT JOIN tbl_custom_value AS tcv_sponsor

ON ti.issue_id = tcv_sponsor.issue_id

AND tcv_sponsor.custom_field_id =137

INNER JOIN (SELECT tis.issue_id, MAX(tis.issue_step_id) AS issue_step_id

                        FROM tbl_issue_step AS tis

INNER JOIN tbl_issue AS ti

ON ti.issue_id = tis.issue_id

                        WHERE ti.process_id = 81

                        GROUP BY issue_id) AS tis_max

ON ti.issue_id = tis_max.issue_id

INNER JOIN  tbl_issue_step AS tis

ON tis_max.issue_step_id = tis.issue_step_id

INNER JOIN tbl_step AS ts

ON tis.step_id = ts.step_id

INNER JOIN tbl_process AS tp

ON ts.process_id = tp.process_id

INNER JOIN tbl_step AS ts_in_flt

ON tp.in_flight_step_id = ts_in_flt.step_id

INNER JOIN(SELECT issue_id, MAX(step_dt) AS step_dt

                        FROM tbl_issue_step

WHERE step_id = (SELECT in_flight_step_id

FROM tbl_process

                                                            WHERE process_id = 81)

                        GROUP BY issue_id) AS tis_start

ON ti.issue_id = tis_start.issue_id

LEFT JOIN tbl_custom_value AS tcv_end_dt

ON ti.issue_id = tcv_end_dt.issue_id

AND tcv_end_dt.custom_field_id = 131

LEFT JOIN (SELECT ta.issue_id, MAX(ta.append_id) as append_id

FROM tbl_append AS ta

INNER JOIN tbl_issue AS ti

ON ti.issue_id = ta.issue_id

WHERE ti.process_id = 81

AND ta.type_id = 2

AND ta.append_txt <> “ GROUP BY issue_id) AS ta_max

ON ti.issue_id = ta_max.issue_id

LEFT JOIN tbl_append AS ta

ON ta_max.append_id = ta.appen_did

LEFT JOIN tbl_custom_value AS tcv_benefits

ON ti.issue_id = tcv_benefits.issue_id

AND tcv_benefits.custom_field_id  = 141

LEFT JOIN tbl_custom_value AS tcv_benefits_type

ON ti.issue_id = tcv_benefits_type.issue_id

AND tcv_benefits_type.custom_field_id = 138

LEFT JOIN tbl_custom_field_option AS tcfo

ON tcv_benefits_type.custom_value = tcfo.custom_field_option_id

WHERE ti.process_id = 81

AND ts.step_type_id<3

AND ts.sort_order >= ts_in_flt.sort_order 

[Former Member]

This SQL will need you to thoughtful designing from universe as well as from reporting side to replicate the functionality of the sub queries and inline views of the SQL ... If this universe is just going to be used by one report and no adhoc analysis to be done the. You can just build the universe using a derived table using the entire SQL..

[former_member672670]

There are going to be future reports built on this universe, so I want to create the Universe using the SQL Query. How do I approach conditional filters like - WHERE ti.process_id = 81

and sub queries as given below when designing the universe? Any idea? 

LEFT JOIN (SELECT ta.issue_id, MAX(ta.append_id) asappend_id

FROM tbl_append AS ta

INNER JOIN tbl_issue AS ti

ON ti.issue_id = ta.issue_id

WHERE ti.process_id = 81

AND ta.type_id = 2

AND ta.append_txt <> “ GROUP BY issue_id) AS ta_max

[Former Member]

FOr this you will have to create a small derived table with the filter build in and join that table in the universe..