Want to know The Truth About CPM?

11 March 2013

Australians, So where the bloody hell are you?

Wow, that is a pretty awful tourism slogan, isn’t it?  I figured if you lot inflicted it on the rest of the world turnabout is fair play.  With luck, I will be met with chants of “Yankee go home!”  In the interest of good international relations and not getting pelted with eggs when I mount the podium at the just-about-here-why-haven’t-you-registered-yet ODTUG SP Australia conference, Melbourne 21 to 22 March, let me note that we too have real clunkers like – “Erie, Pennsylvania…Feel the Lake Effect”.  (I’ve been in Erie, PA during the winter and trust me, you don’t want to experience Lake Effect.)  Happily the world shares equally in bad marketing slogans so we can all engage in a little schadenfreude as we read these bon mots.  There, international enough for you?

But there is a point to that now discarded Australian tourism slogan – why oh why oh why have you Australian EPM and BI practitioners not signed up for ODTUG’s totally awesome Seriously Practical conference, Melbourne, 21 to 22 March which is just the end of next week?  Huh?  Why?  Why?  C’mon, give me a good reason.  You can’t, can you?

Don’t believe me?  Check out the agenda

I will note that this Seriously Practical conference has some seriously awesome content.  Again, don’t believe me?  Take a look at the below content.  What are you waiting for?  Me begging on my knees?  You have it, metaphorically.  A fantastic agenda?  Cast your eyes downwards and learn more.

Day 1

Welcome and Opening Remarks, Bambi Price, ODTUG8:45 - 9:00 AM

Keynote: What’s Coming in Oracle BI and EPM, Babar Jan Haleem, Oracle Corporation
9:00 - 9:30 AM
A glimpse into the future of Oracle BI and EPM, delivered by the Director of EPM BI Architecture & Technology for APAC.

SESSION 1
9:30 - 10:30 AM
Fusion Applications and Your BI/EPM Investment,  Debra Lilley, Fujitsu
Oracle Fusion Applications are here today providing the next generation of applications. They are about having everything the user needs in one place, and that includes information. Fusion Applications is a window on Oracle’s Fusion Middleware stack and a very big part of that is BI/EPM and analytics. This presentation will include a small demo of how Fusion looks and is designed to give you an appreciation of how BI/EPM is embedded in Fusion. For anyone thinking of Fusion in the future it will underline that your B/EPMI investment today is an investment in that future and protected.

SESSION 2
10:45 - 11:45 AM
Highlights and Capabilities of the Latest Release of EPM, Charles Pinda, Oracle Corporation
Enterprise Performance Management (EPM) is comprised of four streams to assist the Office of the CFO to deliver predictable financial results. These streams are Strategy Management, Planning and Forecasting, Profitability Management, and Financial Close. In addition, the reporting delivery layer of EPM is supported by Oracle BI to provide further analytical insights into this information. Now the latest release of EPM, version 11.1.2.2, has new functionality to enhance the business process of delivering predictable results. This session will highlight some of these enhancements and demonstrate the new capability.

LUNCH
11:45 AM - 12:45 PM

SESSION 3
12:45 - 1:45 PM
Endeca Information Discovery, Stephen Weingartner, Oracle Corporation
Endeca Information Discovery (EID) provides unique and powerful analytical capabilities that enable organisations to discover insights in information that would otherwise be unusable. EID’s strengths in unstructured data, agile business intelligence, and information discovery will be demonstrated and discussed. Several EID use-cases will be covered to illustrate the wide variety of solutions which have been implemented at several large organisations. During this presentation, a solution will be created in EID, demonstrating how it works in an end-to-end manner. Attendees will learn how EID differs from other business intelligence and big-data technologies and how it has created its own new niche which companies can fill.

SESSION 4
2:00 - 3:00 PM
Exploring Oracle BI Apps: How Does it Work and What Do I Get, Richard Philipson, James & Monroe
This presentation provides an overview of the BI Apps architecture for novice users, with clear concise information presented in an easy-to-understand format. The presentation steps through the aspects of an implementation from conception to execution and concluding with example content for one of the many areas of content Financial Analytics.

SESSION 5
3:15 - 4:15 PM  
Thoughts from the Frontline – Issues and Opportunities Faced When Implementing or Upgrading HFM Applications,
Christine Aird, M-Power Solutions

This session will walk you through the project lifecycle of an HFM implementation/upgrade and cover key areas where problems/opportunities regularly occur. Using real life case studies, interspersed with best practice processes and approaches, the session will give you an insight into how you could avoid the challenges and take advantage of possible opportunities during your project. The session will touch on how we see HFM being used, will cover some of the misconceptions that follow HFM, and will drill into the potential issues this can cause and what is considered best practice for an HFM implementation.
This session will give you great insight into what you should use HFM for and how to deliver a successful project or upgrade.

SESSION 6
4:30 - 5:30 PM
The Spreadsheet Management System Known as Dodeca, Cameron Lackpour, CL Solve
Business users love Essbase for its unparalleled analytic power. Business users also love Excel because spreadsheets are where data is expressed, analyzed, and manipulated. Essbase + spreadsheets = analytic bliss.
But as soon as you move beyond ad-hoc Essbase functionality, a series of questions arise:
1) How do you handle complex functionality?
2) Code yes, but where, and how?
3) What about non-Essbase data?
4) Workbooks on the web?

What’s needed is a system that:
1) Is spreadsheet-centric
2) Ties easily to SQL
3) Automatically distributes and updates workbooks

Dodeca does all of the above, and more; it is the key to managing complex workbooks so you and your company can focus on the real task at hand—analyzing, understanding, and managing the numbers, not the spreadsheets. This presentation introduces the issues around spreadsheets, Dodeca’s philosophy around managing multiple complex workbooks, and then demonstrations of what Dodeca can do.

Day 2

SESSION 7
9:00 - 10:00 AM
Taking OBIEE to the Next Level, Maneesh Disawal, James & Monroe
Move over pivot tables and charts and incorporate Exalytics with KPIs, scorecards, maps, and advanced analytic functions into your regular reporting. Use the latest infrastructure and visualization techniques to create dazzling dashboards to quickly and directly communicate relevant information. Incorporate data in office communications and deliver reports on mobile devices.

SESSION 8
10:15 - 11:15 AM
Essbase ASO – A Brave New World in Australia but not for the Rest of the World, Steve Hitchman, m-power Solutions
Many of you will be familiar with the concept of Essbase ASO / Aggregate Storage. You’ve probably read about the differences from Essbase BSO, seen a case study or used it on a project but even though ASO has been available for over 5 years adoption in Australia continues to be very limited.  In this session, we will cover the basics of what ASO is before exploring what’s so great about it and how it is being used to help companies in Australia.  For lovers of the more traditional BSO model, we’ll explain the differences highlighting what you can and can’t do in ASO models. This initially seems like a lot but we’ll pass on the tips and tricks that we’ve learned that allow this gap to be bridged including how ASO and BSO can work together.
We’ll highlight the real world stuff that goes beyond the textbook and Oracle marketing to expose how ASO technology is revolutionising what Essbase can deliver.

SESSION 9
11:30 AM - 12:30 PM
Oracle BI and Oracle Essbase: Today and Tomorrow, Stephane Roman, Oracle Corporation
Essbase and OBIEE have come a long way together since they met years ago. Being both strategic solutions, Oracle have continuously improved their specific technologies while making the integration more and more seamless at the same time.
In this session, we will first of all give an overview of the OBIEE & Essbase architectures and explain their typical usages within the enterprise. We will then open the bonnet and look at how the two solutions work together. How is the Essbase multi-dimensional structure (outline) understood by the OBIEE semantic layer (Repository)? How can I federate relational and multi-dimensional data sources through the OBIEE RPD’s logical layer? What are the different ways to import and model an Essbase cube OBIEE? How to work with unbalanced hierarchies? Are all Essbase features available through OBIEE (UDAs, Variables, Levels vs Generations…)?
Finally, we will look at how upcoming releases of OBIEE & Essbase will make the two solutions even tightly integrated, as well as a glance on how both are used with new Oracle Applications.

LUNCH
12:30 - 1:30 PM  

SESSION 10
1:30 - 2:30 PM
Slay the Evil of Bad Data in Essbase with ODI, Cameron Lackpour, CL Solve
Everyone knows that bad data kills Essbase applications. But did you know that bad data can also kill careers, consulting engagements, and company-wide labor agreements? Why is the rule of high-quality data in Essbase honored more in the breach than the observance? This session explains the real-world consequences of bad data, categories of data quality, and tools and strategies for ensuring that your Essbase database has the right data. A complete solution in ODI will show one path to salvation in the never-ending quest for quality data.

SESSION 11
2:45 - 3:45 PM 
Growing with Business Analytics - Keeping Updated and Informed, Paul Anderson, Oracle Corporation
What's the fastest way to find out about product certification and compatibility? Which social media channels are available for EPM and BI products? These and many more questions are often asked in the busy and growing business world; incorporating the Internet highway into our lives.
There have been recent changes in both significant improvements and new implementation of ways in which customers can communicate with Business Analytics support.
This presentation will run through changes made by the Oracle Business Analytics Proactive Team to areas including MOS Communities, knowledge management, and social media.
This session will show the way you can ask questions and find answers about Product Lifecycle, how to interact with Business Analytics support in a simple and efficient way, how to keep updated with news on areas such as patches and documentation, and will provide a demo of the new translations being introduced into My Oracle Support.

SESSION 12
Closing Panel
3:45 - 4:30 PM
Do you have a question about BI or EPM? Your BI and EPM speakers are here to give answers. This session is moderated but is expected to be freewheeling and open. Try to stump us!

Whew, is that enough?

What oh what oh what are you waiting for?  Read what’s on offer – that is great stuff, packaged for, and (mostly) delivered by Australians (hey, I have incredibly distant relatives somewhere in Victoria so do I get a partial pass on the Yankee Go Home protests?).  That means the content is targeted to your needs and your market.  What more could you possibly want?  Sign up today.

Be seeing you in Melbourne.

06 March 2013

Stupid Planning query #12 -- Calculation Manager Rights

Introduction

Arrrgh, this one really got me going. Actually, I have noticed that I have never written a query against the Planning tables unless I am unable to get whatever it is out of Planning easily. And I suppose that sort of makes sense. And I also that means I am always annoyed and that missing Planning reporting features are my opportunity to increase my SQL skills, such as they are. Of course writing the query took way longer than I thought it would. Read on for the reason...

With that preamble, have you ever wondered what security is assigned to Calc Mgr rules in Planning? You basically have to go into each rule and edit the security to see what user or group has been assigned and what rights are set. Annoying, isn't it? And not practical when there are many rules. Here's an example of what it looks like:

I can see that the group PLN_CalcTest_Consol has Launch access to the rule AggAll, but what about AggPlan, CalcRev, etc., etc., etc.?

So yes, this is yet another opportunity to query the tables. And oh yes, I am using this as a teeny part of the Planning presentation I am giving with Jessica Cordova (hi, Jessica) at Kscope13.

NB – One other note, I was inspired to get around to this query in response to “vaio” and his Hyperion Business Rules security query from this Network54 thread: http://www.network54.com/Forum/58296/thread/1362011042/Export+only+Webform+security I figured if world+dog had it for EAS’ business rules, we needed it for Calc Mgr as CM is all there is from 11.1.2.2 onwards.

The reason this query drove me up the wall

Would you believe that deployed Calculation Manager rules do NOT have an object type in Planning? Would you believe that I spent more than a little bit of time trying to find it?

Oh yes, both statements are true. The latter one you are going to have to take on trust. The former I can prove.

Here are the Object Types in the Planning app schema:
SELECT DISTINCT
OT.OBJECT_TYPE,
OT.TYPE_NAME
FROM
HSP_OBJECT_TYPE OT
OBJECT_TYPETYPE_NAME
1
Folder
2
Dimension
3
Attribute Dimension
4
Calendar
5
User
6
Group
7
Form
8
FX Table
9
Currency
10
Alias
11
Cube
12
Planning Unit
30
Attribute Member
31
Scenario
32
Account
33
Entity
34
Time Period
35
Version
37
Currency Member
38
Year
45
Shared Member
50
User Defined Dimension Member


And here is the OBJECT_TYPE that goes with Calc Mgr rules:
SELECT
*
FROM HSP_OBJECT
WHERE
OBJECT_TYPE = '115'

See the 115? I only know that because I did a search on the name of one of the rules and thus figured it out.

Why would you care about OBJECT_TYPE 115?

Well, once you (or I) knew this, you (or I) could write this:
/*
Purpose: Calculation Manager security report by rule, group, and user
Modified: 1 Feb 2013
Notes: Common Table Expressions make joining mostly disparate objects
relatively easy.
NB -- Calc Mgr rules do NOT have an OBJECT_TYPE in HSP_OBJECT.
The OBJECT_TYPE seems to be 115.
*/
-- I am in love with CTEs over subqueries
WITH
-- CTE for Calc Mgr OBJECT_ID, Plan Type, and Name
BRName (CMID,PlanType, BRName) AS
(
SELECT
CMR.ID,
CMR.LOCATION_SUB_TYPE,
O.OBJECT_NAME
FROM
HSP_CALC_MGR_RULES CMR
INNER JOIN
HSP_OBJECT O ON O.OBJECT_ID = CMR.ID
),
-- CTE for Calc Mgr user OBJECT_ID, Calc Mgr OBJECT_ID, and Launch rights
BRAccess (UserID, CMID, Launch) AS
(
SELECT
AC.USER_ID AS 'User ID',
O.OBJECT_ID AS 'CM Obj ID',
--O.OBJECT_NAME 'CM Name',
CASE AC.ACCESS_MODE
WHEN -1 THEN 'No Launch'
WHEN 4 THEN 'Launch'
ELSE 'Unknown'
END AS 'Access'
FROM
HSP_ACCESS_CONTROL AC
INNER JOIN
HSP_OBJECT O ON O.OBJECT_ID = AC.OBJECT_ID
WHERE
O.OBJECT_TYPE = '115'
),
-- CTE for user OBJECT_ID, user name, group name
UsersInGroups (UserID, [User Name], [Group Name]) AS
(
SELECT
--O.OBJECT_ID AS 'User ID',
O2.OBJECT_ID AS 'CM Obj ID',
O.OBJECT_NAME AS 'User Name',
O2.OBJECT_NAME AS 'Group Name'
FROM
HSP_USERS U
INNER JOIN
HSP_OBJECT O ON O.OBJECT_ID = U.USER_ID
INNER JOIN
HSP_USERSINGROUP UG ON UG.USER_ID = U.USER_ID
INNER JOIN
HSP_OBJECT O2 ON O2.OBJECT_ID = UG.GROUP_ID
)
SELECT
BRN.BRName AS 'Calc Mgr rule',
BRN.PlanType AS 'Plan Type',
BRA.Launch AS'Launch',
UIG.[User Name] AS 'User name' ,
UIG.[Group Name] AS 'Group name'
FROM
BRAccess BRA
INNER JOIN
UsersInGroups UIG ON UIG.UserID = BRA.UserID
INNER JOIN
BRName BRN ON BRN.CMID = BRA.CMID
ORDER BY BRN.BRName, UIG.[Group Name], UIG.[User Name]

And then you (or I) could run the above query, and get the following:
Calc Mgr rulePlan TypeLaunchUser nameGroup name
AggAllConsolLaunchTestPlanner1PLN_CalcTest_Consol
AggAllConsolLaunchTestPlanner2PLN_CalcTest_Consol
AggAllConsolLaunchTestPlanner3PLN_CalcTest_Consol
AggPlanConsolLaunchTestPlanner1PLN_CalcTest_Consol
AggPlanConsolLaunchTestPlanner2PLN_CalcTest_Consol
AggPlanConsolLaunchTestPlanner3PLN_CalcTest_Consol
CalcRevConsolLaunchTestPlanner1PLN_CalcTest_Consol
CalcRevConsolLaunchTestPlanner2PLN_CalcTest_Consol
CalcRevConsolLaunchTestPlanner3PLN_CalcTest_Consol
ClrBSConsolLaunchTestPlanner1PLN_CalcTest_Consol
ClrBSConsolLaunchTestPlanner2PLN_CalcTest_Consol
ClrBSConsolLaunchTestPlanner3PLN_CalcTest_Consol
ClrFinalConsolLaunchTestPlanner1PLN_CalcTest_Consol
ClrFinalConsolLaunchTestPlanner2PLN_CalcTest_Consol
ClrFinalConsolLaunchTestPlanner3PLN_CalcTest_Consol
ClrTrgtsConsolNo LaunchTestPlanner1PLN_CalcTest_Consol
ClrTrgtsConsolNo LaunchTestPlanner2PLN_CalcTest_Consol
ClrTrgtsConsolNo LaunchTestPlanner3PLN_CalcTest_Consol


Isn't that pretty? And useful? I (or you) think so.

The sting in the tail

Would you believe I spent a good half hour poking around in the Calculation Manager tables?

Would you believe that the CALCMGROBJECTACCESS table in the Calc Manager schema is completely empty? I have no idea what it is for, but it isn't for rules deployed to Planning. Terrific.

But the good news is that with a little poking about writing a simple (well, CTEs aren’t totally beginner’s stuff but they are so easy to read and work with) query, you (or I) can easily look at who and what kind of access planners and groups have to deployed CM business rules.

Be seeing you.