Hi Community,

I'm looking for some guidance on what approach to take in the short and medium term to help address the business requirements I have in my role as PA Lead at a new company.

A little context: We have IBM Planning Analytics on Cloud that is used by the Finance function for both month end reporting processes and for forecasting and budgeting. Where I'm looking for guidance from the PA Community is around the reporting side of things as we have the requirement to produce monthly reports from the PA data. 

At the moment, we have a number of fixed templates that are produced in PAfE using DBRW - they are not dynamic other than some simple dropdowns that vary the time periods and versions displayed in the report. A lot of the structure and calculations are done using native excel formulas. Most views are the same with differing filters applied per division, region, cluster etc but we have many sheets per workbook to achieve this. Each sheet will have rows/columns grouped etc for some degree of "drill down" but this is just done by hide/show of rows using excel feature and not the PA functionality.

The Finance team will update these reports, snapshot the values (or do a paste special values) and save the Excel file. This is then distributed via email or shared location to be merged into various presentations or other report packs.

I know that the maintenance of these reports isn't ideal at all. I'd like to improve things incrementally using best practices so I've been considering Dynamic Reports, updating the model to improve reporting calculations, updating structures in the model to ensure we have the right elements.

My request really is around the introduction of Universal Reports to the ways of working. I'd like to take advantage of Hierarchies in the model itself to really simplify the many confusing alternative rollups we have in a few of our key dimensions but the way we report currently using the DBRW formulas - we cannot switch. Is this the use case for UR and the general direction of travel for the excel reporting side of things?

What would anyone recommend for this use case given we require data to be distributed wider than the licence holders of PA (costs would stop us from rolling out licenses wider than the Finance function right now)? My instinct is to try and provide as much flexibility and dynamic analysis from the toolset as I can for licenced users but still provide some Excel-based analysis/dynamic features.

Any help/comments would be appreciated.

Chris

If this is still available I would suggest starting here. 

https://community.ibm.com/community/user/businessanalytics/discussion/webinar-planning-analytics-for-excel-new-features-in-universal-reports?ReturnUrl=%2fcommunity%2fuser%2fbusinessanalytics%2fcommunities%2fcommunity-home%2fdigestviewer%3fcommunitykey%3d8fde0600-e22b-4178-acf5-bf4eda43146b

And then look in Youtube et al for some of the discussions published by IBM Partners

No doubt there is a long term roadmap for the enhancement of UR's and hierarchy worksheet functions but the detail and timeline are not clear and that makes it difficult for you to build a solid plan. 

There are many too many aspects to your question about supporting hierarchies to be able to answer succinctly but here are some thinking points. 

1) Firstly , if you have rules and you wish to implement hierarchies then you will need to rework the rules - this may be an involved activity. Also if hierarchy based cube views are to be used as data sources for TI's then you need to consider that. 

2) Views and Explorations have always supported hierarchies. Quick Reports supported hierarchies but were very limited.  Universal reports support hierarchies. snapshot of the PA component exists. 

3) Hierarchy reports are currently just datasets returned by a MDX statement - This is equivalent to just a base case use of a dynamic report. So the usefulness of that depends a lot on whether your reporting needs are limited to that. 

4) TM1Val was released I think in December. But it doesn't accept input like DBR/DBRW

5)  TM1Set and DEFINECALC support hierarchies but other than there there are limited worksheet functions to support hierarchies compared to the list of the legacy worksheet functions ELCOMP etc   https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=reference-worksheet-functions

6) Other than hierarchy support there are many things dynamic reports can do which UR's at this stage cannot

7) If you don't need the workbooks rendered as websheets then maybe you can use the existing functions and build solutions using code

8) At least one IBM partner has built their own add-in (With similar functions to dynamic reports) to support hierarchies

------------------------------
John O'Leary
------------------------------

Hi John,

Thanks for you reply and your thoughts!

On using hierarchies there are quite a lot of considerations in terms of modelling etc as you kindly listed in your reply. I guess my thought processes were moving in that direction but only if the end-user consumption methods we choose to use can support them (hence interest in UR).

As a general point - there's not really a lot of content on YouTube, Partner Sites or IBM's site around using PA in terms of what "Great" or "Best in Class" looks like. More often than not, I see videos and demos that show pretty basic functionality/sales demos that don't really go far into what the product can do in the hands of experts.

Videos tend to be "how to create a dynamic report" or "how to create workspace workbooks" etc. where as I'm looking for of the community to really show off what they've done! :-)  Appreciate there's always going to be some intellectual property/data privacy considerations in that endeavour 

Chris

I agree that an incremental approach would work best. Have you considered the Cubewise Slice Excel add-in? It has functionality for report bursting and snapshotting, but also supports hierarchies with a DBHW function. This could smoothen the transition to a new setup.

Hi Chris,

Congrats on the new role! I think you've already gotten a lot of good suggestions around the options for making incremental improvements to your existing PA Excel reporting with PAfE, so I wanted to drill into one other thing you mentioned here related to producing reports (and more) from PA data:

A solution to the problem of Excel / PAfE report distribution & automation that I wanted to suggest looking at for this is ReportWORQ (from an IBM partner, QueBIT, which I work for): https://www.reportworq.com

A few of the things you mentioned are among the features available (absolutely no coding required): 

• Rich emails updated with Excel / PA data (with or without a copy of a static Excel report) can be sent to users.
• Live or Static Excel reports (plus PDF's), can be distributed to network drives, SharePoint, OneDrive, Google Drive, etc.
• PowerPoint presentations can be refreshed based on Excel / PA data.

You mentioned, as well, that today a number of reports rely more heavily on non-PA Excel formulas & features so I'll also highlight that ReportWORQ can refresh practically any Excel formula, plus data in Excel from ODBC sources and pivot tables, if those are used in your Excel reports. 

All of the automation & structure around report creation & distribution that ReportWORQ can provide could give the Finance team more time to focus on the many other important report maintenance & redesign tasks that you're facing. And it will keep working as you make progress on those and ultimately leverage more PA / PAfE features in your Excel reporting.

------------------------------
Michael Cowie
QueBIT Consulting
Tuscaloosa AL
------------------------------

Universal reports were released towards the end of last year and given that there are very few posts on the various forums they are either very simple and easy to get on with or users have not started integrating them yet. On the Product Roadmap we can see that Drill through functionality should be added in Q1, Sandbox support in Q3

Given the new role and taking on a new estate of reports my first instinct would be to review and condense reporting where possible. What is needed and what is not and more importantly this can (hopefully) identify why their are multiple alternative hierarchies and work towards a plan of streamlining those dimensions.

Any time invested in MDX is worth it in my view and looking to de-risk individual reports by bringing reporting logic (back) into IBM Planning Analytics is a good thing. 

In the short term sending reports to users without IBM Planning Analytics access can be achieved in a number of ways, commercial products have been created to fill this gap but if those are not a viable solution for you then alternatives exist,

You can send emails via TI utilising Powershell and this works well.

There are a number of resources available to utilise Powershell/ Power Automate/ Excel Macros/ Python etc.. etc.. to convert excel files to static values.

You don't have to roll your own, (likely) any business partner will happily help take you through the above and get you setup

More interestingly is the medium and long term, v12/ Planning analytics engine gives access to ExecuteHttpRequest to query/ interact with third party sources/ solutions including the Microsoft Graph API. 

In theory you could structure and build a new excel/ powerpoint file and send via email, to a sharepoint/ network folder, to a slack channel, to a teams channel, to any third party software/ platform with an API.

All directly from TM1 with no additional servers or installations required