Share:|

BCO Data Marts and Materialized Views

 

In BCO, Data marts are used to prepare data for use in reports, where a BCO view can be the result of a Structured Query Language (SQL) query or a materialized table.

 

Data marts rely on a basic set of views that hide the BCO data model and expose a more report-oriented view of data, referred to in BCO as “public views”. All the data that can be extracted from BCO is available through public views. BMC recommends that you use public views to access BCO data instead of relying on underlying tables that can change across versions of the product.

There are two ways the SQL statements are processed in BCO relative to data marts.

 

  • SQL statements used in the data mart can be executed on-the-fly as the query is executed (default)
  • A Materializer task is executed on a scheduled basis, creates a table in the BCO database, and populates it with the content of the SQL data mart. This is done when a query would require a lot of execution time as in the case of complex queries, or if multiple reports are accessing the same SQL data mart and so, the generation is performed only once. Materializing a SQL data mart requires storage space, so the process must be handled with care.

 

A SQL data mart allows you to further manipulate the data you have imported into BCO. They are used to for the preparation of data for advanced reports, for the BCO SQL data mart administration, or for an external reporting tool. In particular, a SQL data mart can:

  • Rename columns using more appropriate labels
  • Select only the data series, metrics, and statistics of interest
  • Group results
  • Use grouping objects to apply custom grouping levels and classify measured data
  • Grant access to the data warehouse to an external reporting tool

BCO SQL data marts adopt the naming convention of ER_V<SQL data martname><id> and can be accessed using the database account reserved for reporting purposes, that is by default, CPIT2_REP.

 

Managing SQL data marts

 

You can see what SQL Data Marts are present in BCO by going to the SQL data marts page, where you can also edit or delete them, and also add new ones.  From the BCO console, navigate to:

Administration > Advanced Reporting > Data marts > SQL data marts.  For a Materialized SQL data mart, the Materialize now (the gear icon) will allow you to immediately refresh the materialization.

 

 

In the image above, selecting a row that has a data mart of interest in Name column is hyperlinked, and will allow you to access its detail page. The detail page shows the SQL used for the data mart and as well, you can to edit or delete it.

If you are not familiar with these structures, and want to learn more, see the product documentation page at this link: /blog/ https:/docs.bmc.com/docs/display/public/bcmco95/SQL data martshttps://docs.bmc.com/docs/display/public/bcmco95/SQL+data+marts

 

How to check a materialized view or data mart

 

If things do not seem to be working as you expected with either the out-of-the-box or your in-house developed view,  here are some tips on how you can determine what might be going wrong. In BCO 9.x, you would go to

Administration > Advanced Reporting > Data marts > SQL data marts

 

First, find the row associated with your view/data mart and get the value of its Physical name and ERID. The ERID are the last group of numbers appended to the end of the Physical name. In the example below, the ERID is 2018 (You can also verify this by clicking on the hyperlinked Name of your view from here and hovering over the named icon at the top left side of the frame which displays the SQL used to generate the view, View - id: 2018).

 

 

The examples we will be using here are of an in-house developed view. If you are creating a new view and are having trouble with the view itself, and get and error like:

 

“The inserted view fails due to SQL errors. Please verify it.”

 

From the BCO UI, try to create it from a SQL client like Oracle SQLdeveloper as:

create view mysqltestview as (insertyourSQLcodehere)

 

where you can verify that the syntax is OK.

Things to verify when writing SQL for a BCO SQL view are:

 

  • if the SQL query has an undefined list of columns to extract,

  (select * from XXXXX) and some of them are duplicated /ambiguous

 

(As an example - you joined two tables and are doing a select * from them, this is correct SQL - but not useful to make a materialized view in BCO). Try to explicitly list the columns you want as your query output.

So instead of this SQL….
select * from sys_def d , sys_object o where o.sysid=d.sysid

You must write the SQL like this:

select d.*, o.sysobjid from sys_def d , sys_object o where o.sysid=d.sysid

  • IF your SQL query contains comments using  --   (dashes):

select tableA.columnA,

--tableA.columnB,

tableA.columnD, tableB.ColumnE from tableA, tableB where tableA.columnA = tableB.columnA

 

replace the – (dashes) with /* */ and it will work

select tableA.columnA,

/* tableA.columnB,*/

tableA.columnD, tableB.ColumnE from tableA, tableB where tableA.columnA = tableB.columnA

 

1) Get the query’s DDLs definitions, using the Physical name from the UI:
select dbms_metadata.get_ddl('VIEW',ER_V_FCASTING_0104_2018','CPIT_OWN') from dual;

From this output you could see something like:

DBMS_METADATA.GET_DDL('VIEW','ER_V_FCASTING_0104_2018','CPIT_OWN')
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "CPIT_OWN"."ER_V_FCASTING_0104_2018" ("SYSID
", "DAYSBACK", "DT", "AVG_80TH_PRCTILE", "MAX_60TH_PRCTILE") AS
SELECT "SYSID","DAYSBACK","DT","AVG_80TH_PRCTILE","MAX_60TH_PRCTILE" FROM ER_V_FCASTING_0104_2018_G

Note that the output from the query above has “_G” (yellow highlight) at the end of its definition. This indicates that it hasn't been materialized successfully yet.

 

2) This can be verified if  “ _G “ is still a view:
select dbms_metadata.get_ddl('VIEW','ER_V_FCASTING_0104_2018_G','CPIT_OWN') from dual;

DBMS_METADATA.GET_DDL('VIEW','ER_V_FCASTING_0104_2018_G','CPIT_OWN')
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "CPIT_OWN"."ER_V_FCASTING_0104_2018_G" ("SYS
ID", "DAYSBACK", "DT", "AVG_80TH_PRCTILE", "MAX_60TH_PRCTILE") AS
select sysid,daysback,trunc(ts)as dt,avg(Percentile_Cont_AVG)avg_80th_prctile,
avg(Percentile_Cont_max)max_60th_prctile from(SELECT distinct pm.sysid,sdd.ts AS
ts,daysback,PERCENTILE_CONT(0.80)WITHIN GROUP(ORDER BY sdd.avgvalue ASC)OVER(PA
RTITION BY pm.sysid,trunc(sdd.ts),pm.sysmetricid,daysback)as Percentile_Cont_AVG
,PERCENTILE_CONT(0.60)WITHIN GROUP(ORDER BY sdd.maxvalue ASC)OVER(PARTITION BY p
m.sysid,trunc(sdd.ts),pm.sysmetricid,daysback)as Percentile_Cont_max FROM cpit_o
wn.pv_sys_data_day sdd,cpit_own.pv_sys_metric pm,(select(trunc(sysdate,'MM')-1)-
add_months(trunc(sysdate,'MM'),-3)as daysback from dual)an_period WHERE pm.metri
c='CPU_UTIL' and sdd.sysmetricid=pm.sysmetricid and sdd.ts between add_months(tr
unc(sysdate,'MM'),-3)and trunc(sysdate,'MM')-1)group by sysid,daysback,trunc(ts)

 

3) When the view is materialized successfully,  the _G disappears,  and name of the based view changes to something similar to ER_MV2018_20130611105002 (below green highlight), containing a materialization date timestamp:
DBMS_METADATA.GET_DDL('VIEW','ER_V_FCASTING_0104_2018','CPIT_OWN') from dual;

From this output you could see something like:

DBMS_METADATA.GET_DDL('VIEW','ER_V_FCASTING_0104_2018','CPIT_OWN')
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "CPIT_OWN"."ER_V_FCASTING_0104_2018" ("SYSID
", "DAYSBACK", "DT", "AVG_80TH_PRCTILE", "MAX_60TH_PRCTILE") AS
SELECT "SYSID","DAYSBACK","DT","AVG_80TH_PRCTILE","MAX_60TH_PRCTILE" FROM ER_MV2018_20130611105002

You can double check this with the query below:
dbms_metadata.get_ddl('TABLE','ER_MV2018_20130611105002','CPIT_OWN') from dual;

 

4) If you want to be able to re-force the materialization of a valid view, you can do a manual delete:
delete from er_props where erid=2018 and name like '%er.materializer.last.date'

5) You can also check the view definition:
select * from er_props where erid=2018

6) To check the view properties:
select * from er_def where erid=2018

 

7) An Index on a view column can be created from Administration>ADVANCED REPORTING>Data marts> SQL data mart>Edit  SQL data mart>, in Advanced mode as in the image below:

 

Which presents the following frame…

 

 

          8) If you want to create an index manually, outside of the UI,  on the TS column for a test, try this SQL:

          create index x on ER_MV2018_20130611105002(ts)


           9) You can verify existing indexes on materialized views:
           select * from user_indexes where table_name='ER_MV2018_20130611105002'

 

I hope you will find these tips useful. The tips included in this article are also available as a Knowledge Base article, How to debug Materialized SQL Views / SQL Data Marts at:

https://bmcsites.force.com/casemgmt/sc_KnowledgeArticle?sfdcid=000106688

 

As always, your comments are appreciated and comment if there is something you would like to see in an upcoming Pulse.

Pulse atrophy?  Get Pulsed up at BMC TrueSight Support Blogs

Thx, timo