Transforming Your Data for Enterprise BI Solutions
The Oracle Cloud EPM solution allows you to model and plan across finance, HR, supply chain, and sales, as well as streamline financial close processes. But what if you could take the data you maintain in EPM and blend it with other datasets across your organization to build a super enterprise data ecosystem?
In the following on-demand webinar you can learn how to become your organization’s data superhero by combining Oracle EPM data with other vital enterprise datasets through the use of top BI tools and data visualization capabilities. Topics that we cover in our webinar include:
- Oracle EPM Cloud: Vital datasets & information to add to your enterprise data inventory
- Data Transformation: How to easily extract data and ingest to cloud-based storage environment(s)
- BI Deep Dive Demo: How to effectively present vital business datasets in BI tools (Power BI, Tableau, Looker, et al.) across your organization
Our Speakers
- Lee Whieldon | Manager, Data Analytics
- Nick Scott | Director, Data Analytics
If you have any questions about combining the Oracle Cloud EPM program with BI tools, please reach out to our Advisory & Transformation team.
Video Transcript
Lee Whieldon: My name is Lee Whieldon, and I have with me the Director of our Data Analytics practice, Nick Scott. We are going to spend about 45 minutes of your time today talking through how we’ve been able to transform some great data-driven solutions, extract Oracle EPM into powerful business intelligence tools like Power BI and Tableau and talk through some of the challenges we found with our clients and how we were able to resolve them.
So, as we move forward to the next slide, this is just an overview of the agenda we’re going to talk about today.
- We’re going to talk a little bit about SC&H as a firm.
- Then we’re going to move to the business challenges that we’ve discovered with our clients in terms of how you can extract vital datasets that support a lot of hierarchical information throughout your organization and how you can blend it with other data sets within your organization.
- We’re going to talk through some great ways we found to extract both metadata and data out of the EPM tools.
- How you structure your data and different design patterns that we’ve encountered out in the wild that we’ve recommended to our clients.
- Lastly, we’re going to talk about a BI tool deep dive. We’re actually going to show you an implementation we did with one of our clients within our Power BI workspace environment. We built an app and we’re going to show you some really cool interactive visualizations that we were able to deliver to clients.
Without further ado, Nick is going to kick us off about our group and give a plug about some of the great solutions we’ve been able to provide to our clients. And then I’m going to take over and talk through the business challenges and walk you through the rest of our implementation strategies for our clients.
Nick Scott: Perfect. So, we did some quick intros and Lee did it nicely there. My name is Nick Scott, and I am the Director of Data Analytics and also our Oracle EPM practice. I’ve been working with the Oracle Hyperion Stack for about 14 years now, and about three years ago we did stand up a data analytics practice that’s focused on helping finance and accounting teams get data out of these great solutions into their organizational reporting tool. I’m happy to be here today alongside the star of the show, which is Lee.
Just a real quick intro about SC&H, we will keep this to a minimum. So, we are a Management Consulting and CPA firm focused on helping folks with finance transformation initiatives, really in the space of the Oracle EPM style tools to help bring consolidation data and budgeting forecasting together, and then also for transformational initiatives around enterprise data and reporting.
We have a number of different services that cover the gamut there. We’re on the Oracle EPM stack and then the tools that we’re familiar with here for visualization reporting, we do a lot of enterprise data warehousing work, again, targeted mainly in the finance and accounting sector to help those teams normalize the data that comes out of their ERPs and EPM solutions and make them more available throughout the organization. We work across a number of different clients and industries here. We don’t specify any specific industries, but we do have a niche in the government contracting business which you’ll see a good bit of in the client solution that we are going to show today was for a large government contractor that had a lot of data complexities, but we really do service companies across all industries, which is exciting.
Business Challenges
Lee Whieldon: For the bulk of this presentation, what we want to talk through is:
- Some of the business challenges we’ve experienced with our clients and how we were able to work with them to resolve them.
- How you might also be able to become a data superhero within your organization to be able to extract some of these great datasets out of your Oracle EPM tools and then blend it with other operational data sets throughout your organization.
We have a slide here that just talks about unlocking your organizational data strengths. We’ve seen with our groups and the clients that we serve that there are a lot of challenges with what we call cross-system enterprise data blending. We see that your data is within all of these different siloed areas, whether it be your production manufacturing data sets, your operational sort of ERP style data sets, your general ledger style, or your sales driven data sets, and being able to blend that with an Oracle Cloud tool, oftentimes what we see is this barrier here that makes it really difficult. You know, you’re struggling with how to extract data from APIs, how to then land it in an area that you can pick up and blend it with other data sets that sit everywhere. Most applications, as you see today within organizations, choose a cloud hosted application as a service style implementation. So, there’s just a lot of data everywhere in different pockets without your organization. What we’re going to talk through with you is some of the challenges we’ve seen specifically with the Oracle Cloud EPM Suite and how we’ve been able to extrapolate on important data sets that walk through and blend important metrics throughout your organization, and then be able to build even more impactful, data-driven insights from there.
Traditional EPM FR Report Versus BI Tools
Obviously within the EPM stack, there’s the traditional FR reports. I’m sure many of our administrative users within this conference coming up, you’ve probably built many tabular FR reports and you know that there’s data limitations within the FR reporting functionality really only are gated in to leveraging just a single EPM source. Whereas a tool like Power BI and you can see here buzz is pointing out Power BI has such great benefits of being able to combine disparate data sources together and then build really beautiful, advanced visualizations off of that data, providing additional insights into your data within your organization that FR really is not able to capture for you. So, we wanted to just give that example of how moving your data sets into a single layer like Power BI is really powerful and can help your organization thrive.
Oracle EPM Tree-Based Metadata Structures
Lee Whieldon: A couple of things too about Oracle EPM structures and some of the challenges we’ve seen is:
- How do I get some of this metadata out?
- How am I able to build my hierarchies on the fly?
As you know, in working with the EPM tools, Oracle leverages what’s called a tree structure to be able to store their metadata. A couple of things about why Oracle chooses to implement this is because for the applications themselves this is a really great fit due to the frequency of operations that are flowing throughout the application and storing metadata in this way actually helps minimize a lot of memory and compute limitations or overhead. As you go into a smart view and you want to drill down into your hierarchies, the power of storing data within a metadata structure with the Oracle application is very powerful. But some of the limitations here are that it’s really hard to extrapolate into a business intelligence tool like a Power BI or a Tableau.
As you know, a tree data structure is actually really great to hold hierarchical information. So that’s another reason why you see throughout Oracle applications within their metadata and their dimensions, they store hierarchical information within a tree because this type of data structure is really powerful to be able to give users flexibility to build robust hierarchical data structures that support different types of their business.
We wanted to give you guys some common tree terminology.
- As you’re building the dimensions within an Oracle application, you can see different things like the amount of depth.
- If you look at a chart of accounts, you have many different parent-child relationships within the tree.
- There are different ways you can create tree structures and you can see here through labeling what that looks like.
- You also can see different effective dates.
- Sometimes you see past, present or future looking forward hierarchical structures within that nature.
- Then, obviously there’s a lot of auditing behind those metadata structures, making sure that the data has a lot of integrity behind it.
We wanted to give this out to you guys so you can see some common tree terminology and how this differs from some more tabular datasets that you can come across in the wild.
There are a couple of challenges for tree structures. Why is it so hard to represent tree-based data structures like the metadata is maintained in Oracle in business intelligence tools? Oftentimes the way you see these tree-based structures stored is that it only has the parent-child relationships within a record. Then you have to do a lot of recursive complex operations to unravel it, to get the tree representation you see here on the right. You can see here with this visual, that it’s a lot of execution of recursive operations to basically create that full hierarchical tree. A lot of business intelligence tools are not best served to handle this type of data. They like a nice tabular clean table-based structure so that it’s easier to pull those hierarchies and blend it with other datasets. Whereas the Oracle EPM applications are designed to provide immediate, quick access to intersections of data within something like a multi-dimensional model, like a cube. So, it’s meant for pulling data on demand versus maintaining that data in a hierarchical manner in more of a table-based structure with business intelligence tools love. We wanted to give you just a little bit of an overview of the challenges we’ve seen with some of these tree-based structures in business intelligence tools and what we were able to do to solve for it.
As you can see, business intelligence tools love reporting dimensional models. They love a conformed dimensional style dimension and fact structure. You can see here on the right where you’re storing things, dimensions in a very flat manner, and then they join to measure tables like a fact table it. BI tools typically do not like tree-based hierarchies because of that recursion. We do see that for some hierarchical structures, tree-based hierarchies can be implemented, but it does require a lot of additional complexity. In Power BI, for example, you need additional measures or DAX calculations which can cause report latency. You can see the benefits of being able to recurse and pull out that hierarchy and land it into a more tabular format.
How We Solved the Metadata and Data Extraction Problem
Now that we’ve talked through that, we wanted to go through how we’ve solved the problem for our clients and how you may be able to then build these tools within your organization and give great benefits and show different data blending capabilities and how that works. A couple of things in regard to how we’ve solved for the extraction out of Oracle EPM products, especially as they’re being cloud-based, we’ve gone an approach of doing a flat-file batch process, where we extract the data from the Oracle EPM tools and then we leverage out of the box data export features in EPM. Again, very easy, very standard, and doesn’t require a ton of complex extractions.
Then we pulled both the data and the metadata from the apps at the same time to ensure that the data has strong synchronicity. We’ve also enabled automation by leveraging things like PowerShell scripts to perform these export tasks on a nightly basis, on a weekly basis. There’s some good tips and tricks that don’t involve heavy API exploration. You can just leverage what’s out of the box within the application itself, land it in an environment to then be ingested into a downstream data repository like a SQL Server, Oracle database, and so on. A couple other considerations that we recommend, especially if you’re trying to blend it into more of a data warehouse style where you’re combining all of these different measures and dimensions within a single kind reporting model, we recommend, like anything that you would ingest into a data warehouse, is setting up a single storage area that basically allows you to just grab those flat file structures and then import it in.
We’ve leveraged a bunch of ELT tools for these integrations. ODI is a great part of the Oracle stack that you can leverage. Informatica is another great one. Then one of the most important parts before you actually push it into your data model is isolating this data just as a raw staging layer so that in further down transformations when you load it into your data model, you’ll be able to just have that nice, clean staging area to get the data set up before you process it. Another thing that we found that’s really beneficial is leveraging additional competencies within your organization to ensure that the data that you’re loading into a data warehouse style environment, you’re partnering closely with SQL developers and DBAs. They really should be your best friend within your organization, making sure that you’re really getting that best-in-class partnership and working with these individuals to ensure that the data flows into these environments successfully. Here are a couple of good tips and tricks for how we’ve been able to handle it for our clients.
Flat File & Job Schedules Example
You can see that we’ve just loaded the adapt files and you can see we’ve extracted year-to-date information. We’ve leveraged and pulled metadata out for different examples. For this particular example, it’s for one of our government contracting clients. You can see here that we’ve pulled project data, very detailed, very nested hierarchical structures, internal reporting groups, cost pools, and of course, chart of accounts is a big one. Then you can see in addition to that, we’ve extracted the data itself out into those flat files as well. Looking on the right here, this is just a sample job scheduler that we’ve leveraged within SQL Server. A lot of our clients leverage SQL Server within their data warehouse toolset. You can see here the order in which we’ve loaded things, truncating staging environments that we’ve discussed previously, and then rerunning and loading these flat files into staging tables. Then you can see at the bottom here we load it into more of that conformed dimensional model where you’re really cleaning your dimensional tables and adjusting for new records that come in.
Entity Relationship Diagram Example
Just another example here, talking through an entity relationship diagram or ERP for short. You can see here we’re leveraging a crow’s foot notation that gives you an example of how you can bring your different data sets together from both your Oracle EPM products and then potentially from an ERP tool. We’ve leveraged cost point as a big one with our government contractors. You can see here we’re blending both information from Oracle EPM, specifically the planning you can see:
- DIM plan account
- DIM plan project
- DIM reporting groups
All of that was exported directly from Oracle EPM. Then you can see other attributes that we’ve been able to extract from enterprise resource platforms like Cost Point. You have your:
- DIM employee table
- DIM ORG
- DIM OBS structures
- Your cost point project dimensions
Being able to bring all of that together into a single star schema-based model is what we found has been really beneficial for drill through activities that you see in business intelligence tools, cross filtering, and so on. We wanted to give you a nice example you can take away and see the cardinality between the dimensions and facts and how we’ve been able to bring Oracle EPM tools together to provide that additional enhanced data blending.
Structuring Your Data
Lastly, this is the area where we’re going to talk about different data model patterns. Why is this important? It’s important because different applications within your organization, especially for reporting, like data formatted in a certain way. To really break into your superhero skills, you need to make sure that you’re transforming your data so that the applications, the reporting applications specifically, that you select really like the way your data lives or is staged. We’re going to talk through this concept a little bit.
Enterprise Reporting Operating Model
Before we go into the data itself and structuring it, we want it to provide you with an overview of how we’ve been able to build a true enterprise operational model. You can see here the green arrows talk about some of the power that you can get when you extract data from Oracle EPM and where the areas of, we call it, areas of enablement can really be powerful here. In the blue box here where the EPM metadata and data structures live there, you can push data into your environments and extract plan and forecast data into a staging layer. Then you push it into a nice dimensional model that we’ve walked through here, which then can be consumed by things like Power BI, which you see here. Then of course the presentation layer where you see different areas of the business benefiting from it, whether it be executive dashboards, ad hoc analysis. Power BI has some great self-service analytics processing and they’ve recently deployed even better features to give the data flexibility for self-service. Then of course, you have your executive dashboards and your static reporting. This is just a great visualization to show you how you can blend the EPM datasets into a data warehouse staging environment that then can be pushed into tools like Power BI.
The Secret Sauce: SQL
Then lastly, this is the secret sauce, if you will. As you can see here, and with most kind of metadata that we’ve been able to extract out of Oracle EPM, they have that very tightly coupled parent-child relationship. What oftentimes we recommend is leveraging things like pivoting, also leveraging tools like pre-level nodes. With tree-based structures, nodes are very important. You can extrapolate on the nodes within your data and then you can build a nice clean, flat structure here. Whereas before, you would have, you can see here this is for chart of account style data, an account ID and a parent within that metadata extraction. What we’ve been able to do is recursively pull out all of the levels assigned to an account ID so that in your downstream processes and your downstream reporting, you can then filter off of a top-level account, second-level account, third level, and so on. This really brings a lot of power in being able to recursively extrapolate the entire hierarchical structure into their own columns. Power BI and other business intelligence tools really love this. They really enjoy having the levels of your hierarchy maintained in their own columns that you can filter and drill through accordingly. Just a couple of tips and tricks you can take away, bringing through the code for you to be able to leverage for future use and enjoy.
Common Challenges & Suggestions
There are obviously challenges and some suggestions that we’ve been able to learn along the way with some of our implementations. There are always areas for improvement for anything that you build for a client. So, we wanted to give you this slide that just talks about some of the interesting pitfalls or challenges we’ve experienced. We wanted to bring it to your attention because you may see it out in the wild for your clients.
Big things we see are things like shared members and alternate hierarchies. We’ve been able to address that by leveraging transformations within SQL to be able to truly identify that distinct parent-child relationship, so you don’t have duplicative data, or you don’t fall short and break some of your hierarchies as you pull it out into those levels irrespective of their columns in the account metadata that I presented to you. Another thing we see, too, is depending on the account type, you can have different sign flips. So oftentimes how we’ve resolved that is we’ve worked closely with the admins that support the EPM solutions to understand how Oracle treats sign flips within some of their metadata and their measures. Then typically there’s additional attributes assigned to, for example an account ID, different columns within the metadata that can denote how the sign flips occur in downstream reporting. But then you can leverage in things like DAX calculations or within the dimensional model directly. So that’s something really helpful. Another thing that’s been really helpful too is you have extended dimensional attributes that you can simply add on to your dimensional table, that you’ve extrapolated all of the levels and you’ve made it a nice flat tabular structure. You can simply perform an update statement within a SQL server environment, or you can join that information directly into something like a power query. So, there’s a lot of great features, you’re not limited to just pulling out the hierarchy itself, but then you can enhance your attributes within your dimensional table to support further enhanced reporting downstream.
One thing we’ve noticed and you’re probably wondering, why didn’t she use the rest API? There’s a lot of great features within the rest API. Why have you not shown us that approach? It’s absolutely a worthwhile approach. One thing that we found is there are record count limitations and also sometimes we face some network-based bottlenecking that obviously is involved when you’re accessing cloud-based data within a cloud-based data environment. But we found in terms of overhead; flat files work just as well. There are certainly many ways to approach a problem. If you wanted to look at paginating and pulling out the data, that’s certainly fine, too. But we found, with some of our clients, we go the flat file approach because it involves a little less overhead in development time. Then, of course, your data management job orchestration, I highly encourage you to leverage your enterprise style orchestration tools. There’s a lot of great orchestration tools within Oracle EPM as well as Power BI. In terms of room for improvement, job scheduling is always a great one.
BI Tool Deep Dive
So now, within the last 10 minutes of our demonstration here, we want to do a Bi tool, deep dive. Nick is going to help a little bit to talk through some of the business-related value that we’ve been able to gain from the solution. We’re going to head directly over to the interactive visualization that we’ve presented. Heading on over to Power BI, you can see here, this is our SC&H analytics dashboard. This is just a sample toy dataset we’ve pulled out from some of our demo environments, and this is what’s called a Power BI app.
We’ve been able to create a series of Power BI dashboards. You can see here for this particular dataset, this is from Oracle’s PBCS, planning budgeting cloud service. You can see really cool stuff like actual to plan variance, pulling the data out. I think another great thing too, especially with our government clients, is they have a ton of deep-dive hierarchical structures. You can see here within this business unit area; we have two levels here. This was maintained within Oracle metadata, and you can see here as we click through, this is really easy. The latency isn’t there at all. It’s wonderful, just a nice experience and we’ve been able to transform this just through some efforts that we talked about earlier in the slide deck. This is really powerful, like how speedy it is, almost like the same feel in terms of data retrieval as something like a smart view, but you see it presented in a beautiful visual manner.
You can also see here different areas that you can blend, really quickly being able to slice and dice your data. Then you can see here additional information that we’ve been able to hierarchically pull. Again, depending on your organization, just really powerful visualizations. You can also see here, I always like to give this plug, but currency is a big one. And you can see we’ve been able to very quickly extract currencies, especially for our international clients. You can see there’s a lot of great power in just being able to apply a currency filter. You can go straight between your local or your U.S. currency in this example. Other great features, you can head on over to some of these other great ones and you can see here, that we’ve been able to extrapolate actual data to plan. We’ve been able to do data blending between cost point, in this particular example, this is a cost point toy dataset and then planning data. Really, really cool powerful stuff.
Nick Scott: So, these dashboards, they sit within the client’s Power BI application that sits right alongside their operational dashboards. They go back to their ERP, their CRM system for opportunities and it lives right alongside it, and it gives access to folks that don’t necessarily go into the Oracle EPM app on a daily basis. It’s helping them get access to the data that is derived from the Oracle EPM solutions, but it puts it at their fingertips. Some of the things that Lee was highlighting here, which she’s been able to do on the back end and get this stuff in a position where it can be easily reported. You know, we’re seeing my notes from the PNL here with revenue, TP is trading profit, and then there are the highest cost categories we have here. That’s what they look at on a month-to-month basis to make sure that they’re trending appropriately and looking at the right cost categories in comparison to revenue and profit.
Some of the other things, too, that Lee’s going to touch on are these different slicers we have up here at the top. It’s almost like you have your FR report and have your different kinds of prompts that you can go about and make selections. We’ve brought in the data sets that make sense that we’re looking at in comparing against all these reports with actuals, LVEs, what they call forecasts, and then we have plan variances as well. We have all the pre-baked variance analysis that they want to see on the dashboard, but then we’re able to augment that with, on the far right side, the reporting group. That’s actually an attribute dimension and it has hierarchies in it. For the purpose of this demo, we branded it with a bunch of random characters in here. That is a way that in their Oracle EPM solution, they group a bunch of different projects together that they want to compare. It only exists in the Oracle EPM solution. What Lee was able to do is extract that dimension and rebuild it just like any other dimension, so that relationship was there, and they can easily pivot and click and see how the different reporting groups function between their business units. The concepts that she’s talking about with your standard dimensions, like entity or project or cost center, those certainly can be brought in, and you can create that hardware experience here in Power BI. But the same thing goes with attributes that often get added on to further explain data and the EPM solutions. Attribute dimensions are obviously a candidate to get funneled in here and help augment reporting as well.
I think that’s all I really wanted to cover. I think Lee covered most of it down here. These are additional attributes for different project types, whether it’s a cost plus or a fixed price or TNM project, whatever attributes you use to slice and dice your main dimensions. Again, they can be brought in to show that extra grade of detail along with currency slicers and things like that that Lee is showing.
Lee Whieldon: Yep.
Nick Scott: One good question that we had come in that we can actually touch on now because it’s depicted in this visual around data security and how that can be applied. The specific question was:
“What considerations are given to the staging area data security, especially when we are discussing planning data?”
That’s a really good question. Security is kind of on two different layers there. The first is on the visualization layer. Just like you’re accessing a report from planning, we do leverage security from the source system which flows to the Oracle EPM. We do have row-level security in our dashboards. If you’re a user coming into the Power BI layer, you’re only going to see the business unit, in this case, the business unit of what you have access to down to the business area as well. So, row-level security can be applied on the dashboard layer. I think where this question’s going is, as this data passes through the underlying data warehouse or background data environment, how can that be secure to make sure sensitive data is not getting out there as well? There are a number of constraints that you want to have as far as a data access approach of who can access it and making sure that they are able to see those data sets, especially when you get to workforce planning style data when you have employee salary or PII information in there. Typically, people working in a data warehouse environment, there are certain rights that they have to see, those sorts of sensitive information. But it does need to be heavily monitored and who has controls to access that.
One other technique that we’ve seen work as well is when you’re building out a solution like this and you’re leveraging a dev or a test environment, there are some techniques that can be applied to scramble the data. Maybe you have the correct metadata, and the data is flowing out, but you’re either scrambling names or scrambling the data set and you basically get your testing ironed out with that scrambled set of data. Then as it moves to production, maybe only one or two people have access to do that migration or it’s a more automated migration approach that doesn’t have the ability on the back end to get in and actually see what data exists in the production table. There are a few different considerations there. That’s a great question. It is kind of two-tier, monitoring the back end over the data stored and then the front-end report layer needs to be in line with what is displayed in the Oracle EPM solutions.
Lee Whieldon: Yep, and especially for lower environments like your development and test environments, we highly recommend that data masking methodology that Nick’s highlighting, where you create a process that basically masks the data as you push it down to your lower environments, and you do that on a regular basis. Typically, we’ve seen organizations refresh their lower environments on an ad hoc basis, or they set up a monthly schedule that then triggers that data masking process to push it into your environment. There’s a lot of great ways to safeguard your production data and not expose it, especially those budget style data elements.
Questions
Lee Whieldon: Any other questions?
Nick Scott: I had a question about how often do you keep the Power BI environment in sync with your Oracle EPM application?
Lee Whieldon: Our clients typically perform planning reviews on a monthly basis. But if you have a higher tempo of needing that data at your fingertips, you can leverage nightly processes that can be scheduled within tools like a Power BI, tools like a SQL Server, enabling that scheduling process to occur. If we head back over to the slide deck, this is an example of extracting data into an intermediary layer. You can create a scheduler. You can leverage things like AUTOsist. That’s an enterprise-style scheduler to orchestrate data throughout environments. Trigger that on a nightly basis, trigger that on a weekly basis, however it is important to your organization. Then from there, kicking off something like a job tool within SQL Server as an example, where you can schedule it, or you can have it trigger within those larger data orchestration tools like AUTOsist.
There’s also great job scheduling functionality within Power BI that you can set on a schedule and, and that it can refresh your data as needed. Lots of flexibility again depending on what the business case is and the expedition with which they need data readily available at their fingertips. Another thing we see is we typically kick things off hopefully in the middle of the night. That’s a great time. We always try to make sure that business systems are operational and running smoothly as users have access to it. These processes really don’t take that long to run. So, we always recommend when you do your refreshes or considerations of that nature, always try to do it off business hours, as you know.
