OCI DWH (7.1) Report on storage array with historical data

[INGO_REPINZ]

Hello,

I'm trying currently to build some reports with historical data:

• 1 for the SAN-Storage for a specific host which have a size bigger than some value x
• 1 for tha discovered Storage Arrays

Both should be able to present historical data up to the current date.

What would be the best datamart(s) to be used? The Inventory, which holds only current values is it not. I was looking into the package "Storage and Storage Pools" but there I'm somehow not get the data like I want. (Either it is already aggregated in some way, or I do not get the historical data). Unfortunatly the Cognos Error-Messages are not really helping to look for the Issue.

For the first Case, I did find a solution with a hand-crafted SQL, but there I can't generate a graph out of it, I'm stuck with 'No Data found'.

Do you have any ideas?

Thanks

Ingo

[hotz]

Hi Ingo,

I would start with the chargeback datamart, specifically with the dwh_capacity.chargeback_fact table.

This is something you should be able to simply drag&drop (see screenshot attached).

For adding the data items to the charts, make sure the aggregare function is something other than "None" or "Automatic". "Total" should work.

Hope thos helps.

Regards,

Gerhard

[INGO_REPINZ]

Hello,

Thanks for the reply! I have too much volumes in, because I have also some OS-Volumes in the total. But with this I can live.

I would like also to limit the values I display to only the last 18 months , I have tried it with

[Full Date] > (_add_months (maximum([Full Date]),-18))

But it looks like the clause did not effect the result at all.

This would be great for the first report and the second as well.

BR Ingo

[INGO_REPINZ]

Additional info to the second report:

The Report for the storage arrays should provide a historical listing of the raw space and the consumed space at the time. Also the total of provisioned volumes is required.

Here I'm stuck a bit, because I did not find any way to get data from the past and to connect more packages (which is not a easy tasl in Cognos, as I figured out)

thanks Ingo

[hotz]

Hi Ingo,

sorry, I didn't have much time today to look at the specifics of the filter you're trying to set.

But basically the more complexity you'd like to have in your report, the sooner you'll reach the limits of what is possible with drag&drop.

If you feel you've reached that point, your choices are:

1. Acqire decent SQL skills.

2. Define the speficis of your requirements and have the report you need custom tailored for you by our PS services.

I'd love to help you out if I can find a bit of time, so please feel free to shoot me a mail directly (I have a feeling you already have my contact details).

Thanks & regards,

Gerhard

[moechnig]

A couple of thoughts on how you might troubleshoot this: 

Make sure [Full Date] is a data element in the query to which you're applying the filter. Use the query editor screen to set it up, if you're not doing so already. 

It's going to be difficulut to get maximum([FullDate]) to do what you want, or at least it has been for me.  You might try specifying an "over" clause on the Maximum function, otherwise it will calculate maximum([FullDate]) on each line so every line will be included.  If you can't get that working, use the current date instead of maximum([FullDate]).  If you're reaching back 18 months, this should be close enough. 

[INGO_REPINZ]

I found some solution for the setting the timeframe back 🙂

I set a filter that looks like that:

_age([Full Date]) < 010601

One remark: you have to set the processing to 'local'

Now I have always only the last 18 months as moving timeframe. It is a bit odd, but it works.

BR Ingo