Hello this is Greg Marsh with Aptitive, we’re a data and analytics consultancy with a strong focus on helping manufacturers to access and analyze their data. Today we’re going to be showing how to analyze J.D. Edwards Data with Snowflake and Power BI.
So what’s the problem?
J.D. Edwards, if you look at the back end, is extremely hard to analyze and that’s because they use tables and structure that was developed back in the 1980s and you end up with tables like F4101 or 4111. The solution we’ve developed for this is that we’re going to load all of this raw information into Snowflake and apply the necessary business logic to be able to talk more effectively with our data. In today’s demo I’ve taken a couple instances of J.D. Edwards. One that’s an old legacy version of XE. Another that’s a newer instance of 91 and we’re going to load it into an external stage up in the cloud. I happen to use Azure but Snowflake also functions with AWS and now, as of June 2019, Google Cloud Platform. Then we’re going to copy that external stage into Snowflake. Apply all of our magic and the result is a great analyzable enterprise data warehouse that you can connect your analytics tools to.
One thing I’m not going to show you is the ingestion from your original source up into the cloud. There are a bunch of ways to do that. You can use Fivetran, Alooma, Kafka, or Azure Data Factory if you’re on Azure. It’s really going to depend on what tools your development team is comfortable with. If you don’t have a way to do that yet, Aptitive has done that quite a bit and we’d be happy to help you with that as well. After we’ve loaded to the external stage there in Azure, we can copy from the external stage which I created here into Snowflake’s raw layer. Again there are ways to get from an external stage without doing a copy into command but this is just what I happened to use. I’ve just brought in five or six tables but we would bring in whatever tables that you want to be able to analyze. Today we’re going to be focused on sales history and being able to analyze that sales history by product and organization.
I brought in all of these tables that, as you can see, are not very clear. Here’s our sales table here’s our product table. Here is our customer table. Here’s our address table.
But at this point, because JDE is what it is, it’s extremely hard to see that. Next, since I brought in two instances of JDE. I decided to unify the two schemas into kind of single tables. So rather than having two versions of say F0006, which is the cost center table, I’ve unified them together into a single view that I’ve called the JD unified view.
Now I only have a single F0006 table. Since 91 has columns that don’t exist in XE for the XE part of the view, I’ve left those columns that are not relevant as no value columns. Now that I have kind of this unified raw layer it’s time to build out our business layer. In our business layer this is where the rubber actually hits the road. This is actually where Aptitive has invested a significant amount of time and energy into taking the underlying metadata tables from JDE and translating all of these columns into their actual meeting. So for example here’s the customer table. This translates our tables like F03012, which is the customer table, into its corresponding values that actually makes sense.
Here I have a customer who has a number, has a parent customer number, an organization, territory I.D., etc. I then did this for two other tables for product and sales transaction build history and loaded that into what we’re calling the business layer.
So finally as a way to create a dimensional star with sequences and primary keys and foreign keys. I physicalized our business layer into what we’re calling the EDW. And as you can see here I ended up with five tables. Here’s the centralized fact which I’ve called fact sales transaction build. And then four dimensions around it.
Finally, I connected a visualization tool, this time Power BI, and pointed at that to our enterprise data warehouse that we created in Snowflake. As you can see, I created this dashboard to show how much revenue we billed by various dimensions and you can see these are dimensions that live in our EDW. By different customer attributes or different organization attributes or different product attributes. I can see now enterprise wide over all time in this data. We have 4.3 billion dollars. If I am only interested in my 91 instance of JDE I can see that was only 1.9. If I’m only interested in this account manager AM9, I can see they had a billion dollars in sales over this amount of time. I can ask “In June 2016 how was that revenue spread across all these different dimensions?” What’s also great about these discovery tools is you can ask permutations of questions like “how much revenue did organization 2 generate in May of 2016?” and we can ask more and more detailed questions and ask those questions at the aggregate which is something that’s extremely difficult to do when trying to ask the questions of the original source system. The reason we do this is we are able to now easily analyze and visualize all of this underlying JDE data that is otherwise extremely hard to talk to and is extremely hard for somebody who is outside of IT who is part of business to understand what the underlying columns mean.
Aptitive has been working with JDE and building out these analytical platforms for years now. We have modules not just in sales, customer and product but also in everything from account and account receivables to taxes and vendors. Here is a list of all of our areas. We also are not stuck on the specific tools like I said you can stand up your Snowflake instance in any of their three clouds and you can use any visualization tool that you may already be using. So if you’re a Cognos shop or a Power BI shop like I just showed you now or a Tableau shop. It doesn’t matter because all the real work is building that business logic, building that business layer, and allowing the end user to actually understand what the different columns and JDE mean.
Thank you. And if you have any questions please contact us.
Greg Marsh is a Data Engineer Manager at Aptitive. In his role, Greg facilitates the discovery of business insights from data. From “Big” data like IoT streams or classic relational ERP information, Greg helps companies to unlock the power of their data.