Clarity

Advance Reporting:  How to get time phased cost plan data from Data Warehouse?

I see that the DWH has summary facts for cost/budget plans such as total planned and actual cost.

How can I pull the data per time period?  I do it in transactional database by going into the time slice db and pulling it by dates there.

I don't see a way to accomplish the same thing in DWH.

SO, for example, if I want to know that in the Budget Plan of Record for MARCH the PM planned $10k capex cost type and the actual was $5K capex (by cost type), how can I extract that from the DWH?

Hi Bradley,

The information you need will be located in the following tables:
DWH_FIN_PLAN_PERIOD_FACTS
DWH_FIN_PLAN_SUMMARY_FACTS

If you look in DWH_FIN_PLAN_PERIOD_FACTS, you should be able to see the information you need per period.  PERIOD_KEY will map to the period_key in DWH_CMN_PERIOD.

Hope this helps. -Nika

Hi Nika,

I don’t see these tables in the DWH documentation field descriptions spreadsheet OR in Ad hoc views. 

I'm trying to build an ad hoc view, but the field names you gave are not the names that show up in Ad Hoc.

Is there a query you could share that shows how to pull the data requested?

This is a really sore spot for users... the documentation is horrible, support has no idea, and we are being told we are not allowed to have VPN query read-only access to the DWH to explore this for ourselves...

So, sorry to be grumpy about it in a forum, but I really need the full query.  Thanks!

BG

not sure if it helps, but CA said to use calendar period measures when you want any time scaled data.. do you see anything under the calendar period measures that would meet your needs?

Thanks, VTLeogal2, I appreciate the suggestion, but I'm not sure where one looks for the calendar period measures or how they apply to the issue?

I mean, sure, we could stab around at it and maybe guess right at some point...  but (ahem, hey CA) FULL documentation that shows how the DWH works and how we discover these answers needs to be provided, given that we can't just go run a query to learn it for ourselves.... (a better plan would be to give us VPN read only access to the DWH so we can).

Brad

I am specifically referencing Ad hoc reporting when I write this, as I use Ad hoc to report from the DWH tables -- not writing a query.

all the DWH tables are posted in the documentation.. it is a large Excel sheet.. have you seen that and you don't see your desired fields? If so, remember that not all fields were put into the DWH. (Advanced reporting domain field descriptions)    or if you have not, this link has the different versions: CA PPM Advanced Reporting and Database Schema Index - CA Technologies   if you are indeed trying to write a query, then that may help???

to use an Ad Hoc (if you build the Ad hoc, you can then get the query using the View SQL query button)
Example:

Using the project domain, the fields (top section) > calendar periods: you would use Month Start, for example, as a filter and a row (assuming crosstab).. Filter on is between Month and Month + 6 to get the current month + 6 month view, for example

under the measures (the bottom section) > calendar periods> you have various measure choices.

You may need the financial domain, but I haven't played with that one enough to know what it all can do.

if it help, Rego has done several really good google hangout/webinars that you can view as well.  You can google it and you can find them all.

Sorry if I am not helping.. just trying to give some other avenues to use to perhaps get the information you are seeking.

Hi Bradley,

I didn't realize you were an On Demand customer that doesn't have access to run queries on DWH.

In that case please do the following and see if that helps:

1. Go to Create New Ad Hoc View
2. Select Financial management domain
3. In Fields, choose the fields you need, hit OK. Make sure you choose one of the Fiscal Periods such as Fiscal Month.
4. The view will look similarly to this:

I think this would be the best way to get the Ad Hoc view as you want it. Please try it and let me know how it goes.

Here are also the links to useful documentation:

1. Advanced Reporting Overview
https://docops.ca.com/ca-ppm/14-3/reporting
2. Advanced Reporting and Database Schema index:
http://www.ca.com/us/support/ca-support-online/product-content/recommended-reading/product-related-technical-information/ca-ppm-advanced-reporting-and-database-schema-index.aspx
You may use it to take a closer look in Data Warehouse Database Schema, here you should have the full table information.
3.  On the same page as in point 2., there is a Jaspersoft Report Development Guide for SaaS
ftp://ftp.ca.com/CA_PPM_Jaspersoft_Report_Development_Guide_for_SaaS_r14_3.pdf
This guide can definitely help you to develop your reports, it's written specifically for On Demand customers, and describing how the JS Studio works.

Please let me know how it goes.

Kind Regards -Nika

Yes, I know that method..  I had done that before I posted the first inquiry... I just don't know what I'm looking at or which fields to choose...

Which of those columns tells me that for Cost Plan 1, Time period March, Cost Type Operating, the Planned dollars and Actual dollars?

(and, yes, I have all those docs, they are dense and difficult to read and get practical info from...)

OK, I was able to follow my nose enough to get what I needed using Nika's note as a starting point.

You were very close, but I needed a couple of different measures for mine. Thanks!

(BTW, this is way harder than it needs to be... we REALLY need that documentation, or query access to learn the DWH. Please tell anyone who asks!)

Thanks all!