The Death of the Star Schema

Transcript: 

Emily Lewis: hi everyone, welcome to our webinar today death of a star schema my name is Emily Lewis and i'm in court as VP of marketing i'll be your moderator for today i'd like to introduce our speakers Claudia imhoff is an internationally recognized expert on analytics.
Emily Lewis: Business intelligence and corresponding architectures.
Emily Lewis: Claudia imhoff has co authored five books and more than 150 articles for technical and business magazines she's also the founder of boulder bi brain trust.
Emily Lewis: a consortium of internationally recognized independent analysts and experts.
Emily Lewis: Matthew holiday is a veteran software engineer and a data analytics expert he co founded in court and 23 after more than 15 years at Oracle and several years managing products at Microsoft.
Emily Lewis: With over 20 years of experience, developing products and taking them to market Matthew has served in several key roles across the company.
Emily Lewis: playing a hand in nearly every aspect of in quarters growth and product development without further ado i'll hand it over to Claudia to kick it off.
Claudia Imhoff: alrighty well let's get started, then i'm going to talk about four things today and i'm really excited to.
Claudia Imhoff: To be here thanks to in quarter for inviting me to to the game and i'm looking very forward to hearing what Matthew has to say as well.
Claudia Imhoff: These are my for agenda items i'm going to talk about the license starts with a depth of the star schema and the benefits of eliminating the star Stevens.
Claudia Imhoff: And then, something that I hope is useful to everyone and that's just a little bit of about getting started all right, let me keep going then.
Claudia Imhoff: let's talk about the life of the star schema as an 80s party yeah we're back at the at.
Claudia Imhoff: The genesis of the star schema from Ralph campbell's work and these him his associates, the data warehousing era began.
Claudia Imhoff: It really did begin in the 80s, the idea of integrated data from multiple sources, the sole purpose was decision support that's what we called it back in the 80s, it then morphed into business intelligence and then on into analytics but the idea was that the.
Claudia Imhoff: relational db, and this is where the King right the date the date cod rules for.
Claudia Imhoff: Data design third normal fourth normal so on and so i'd most efficient way to store that data.
Claudia Imhoff: Certainly it was, but it was unfortunately the least effective way or efficient way to get any kind of performance when you talk about multiple join.
Claudia Imhoff: And that's where the star schema came in the star schema quite bluntly, is a physical instantiate of the multiple joints that were needed, for example, the business community sort of speaks in dimensions, if you will, they want to know the the store.
Claudia Imhoff: Whatever how well the stores doing what products did well in the store or what customers visited store and so forth, and so on.
Claudia Imhoff: So it allows the business community to answer any number of questions, it was it was really quite good to to get that kind of of multi.
Claudia Imhoff: faceted type of questions you get a reasonable response on because you've already physically dad she did all those join, so it was a wonderful, it was a wonderful idea and it works very, very well to answer those known questions for business users.
Claudia Imhoff: However, it also had some interesting problems with it certainly a significant amount of data D normalization was needed and that's what this star schema shows you there.
Claudia Imhoff: The fact table in the middle in the multiple dimensions all around it answering many, many different kinds of questions but it's certainly a D normalized.
Claudia Imhoff: model, it is nothing like the codon date rules, you know normalization great way to conform multi dimensional analysis, but only multi dimensional analysis.
Claudia Imhoff: As long as the analytical processes never changed it was a very stable model, as long as the analytical processes or data never changed now, we all know what that means yeah.
Claudia Imhoff: As long as the analytical processes or data never changed, but they do, they do all the time.
Claudia Imhoff: New data comes in, we create new dimensions, we we shake up the dimensions that we already have those things that were conformed dimensions, they get changed all the time it's a very unpredictable very fluid environment and they're always changing.
Claudia Imhoff: The results were those the bane of all of our lives we're slowly changing dimension and the maintenance, the need to constantly form new.
Claudia Imhoff: New stars, because new questions were needed, and so forth, it also meant that there was a lot of redundancy.
Claudia Imhoff: If a dimension didn't exist in a particular star schema the fastest solution was to duplicate the star schema and add the dimension in well now we've got all this duplicated data, and of course it's a pretty big loss of flexibility here.
Claudia Imhoff: So the analytical environments themselves became pretty complex pretty difficult to maintain and the business community, they were not amused let's put it that way.
Claudia Imhoff: So let's talk about the death star schema and see where we go from there.
Claudia Imhoff: All right, they must be a better way, and the answer is fortunately technology has marched forward so rapidly that there is a better way now, we do have incredible technologies that allow us.
Claudia Imhoff: To have our star schemas but have them in a much more flexible way, and let me talk about the three legged.
Claudia Imhoff: stool, the first one is the cloud storage of data boy that's made a massive change to all of our designs all of our costs everything.
Claudia Imhoff: We also have in memory capability very good in memory capabilities and they do not cost an arm and a leg, which is kind of Nice, the cost of memory has gone way down.
Claudia Imhoff: We also now have query engine didn't have those 20 3040 years ago query engine query engine that allow us to find the data and be able to query it.
Claudia Imhoff: As is but let's go through those in a little more detail the cloud storage data can be stored in the cloud in something like parquet a wonderful technology now.
Claudia Imhoff: And what it means by having things in the in the cloud is that we can reduce our costs the cloud is expansive or contracted.
Claudia Imhoff: it's either expanding to handle new data our new queries or ramp up for new users and then, when all that is no longer needed it can make the contract back down.
Claudia Imhoff: So that we can save a significant amount of money by having storage on demand, if you will, or expansion on demand the data storage orchestration is also very different over many different storage format, for example, like I mentioned random access memory hoo ha.
Claudia Imhoff: a wonderful invention a wonderful thing to have we have thought solid state drive now we want to have.
Claudia Imhoff: Really fast access to data and then, of course, we still have the hdd the spinning disk themselves, but we have all of these at our fingertips.
Claudia Imhoff: And we can place data in different formats, depending on what we're doing with the data and then not last but certainly not least optimization.
Claudia Imhoff: That has improved the performance dramatically by better io the usage of those query engines and, of course, the columnar and in memory storage of data big change there.
Claudia Imhoff: The second leg, as I mentioned, we see in memory wow it's so cheap now.
Claudia Imhoff: Compared to where it was 20 years ago, it means that we can that data can now reside there, rather than on disk and in memory is a whole different ballgame isn't it think of how how performance, that is.
Claudia Imhoff: It optimize the performance for many queries by eliminating the request for the disk storage data, and it certainly improves the scalability now that the cost has gone way down.
Claudia Imhoff: We also have as the third leg here new query engine, and this is an important part here the query engines, the engines that provide real time join between complex data tables, in other words.
Claudia Imhoff: We can create a star schema on the fly from the data that resides in the database we don't need it to be physically in the star schema it can be virtually in a star schema.
Claudia Imhoff: They can the engines themselves can create the needed aggregation at the same time.
Claudia Imhoff: Tremendous savings in terms of maintenance and everything else, it also gives us incredible flexibility if something changes.
Claudia Imhoff: Then simply the query engine changes the data that it brings together.
Claudia Imhoff: it's a tremendous boost to this entire thing, so instead of physical wising the joins between the data, the data.
Claudia Imhoff: We can now virtualized joins and make it look like a star schema when in fact the underlying data is still in the same format that it was originally.
Claudia Imhoff: So it really does sort of eliminate that part of the of the confirm dimension maintenance of slowly changing dimension.
Claudia Imhoff: doesn't mean that the star team is not needed, of course, it is it's still a very popular design it just means we don't have to physical eyes and that's wonderful.
Claudia Imhoff: So with all three legs in place the star schema can be replaced easily we can tear down the virtual view and rebuild it it's a lot easier to ingest the data integrated.
Claudia Imhoff: On the flies stored in memory, if we want to atl processes get simplified by removing the need to create the star schema or the maintenance of that star schema.
Claudia Imhoff: And the data from many complex tables can be quickly joined, we are not limited to just that one star schema we can create others on fly.
Claudia Imhoff: For example, a fact joined to effect has always been oh boy don't do that that's a big, no, no.
Claudia Imhoff: In the star schema world right it'll take forever to do something like that almost impossible to do.
Claudia Imhoff: Which is the reason why we just simply created a new fact table and a bunch of new dimensions, because it's easier to do that, and it was to join to fact tables together.
Claudia Imhoff: But with this improve performance that we just talked about this is now possible we can create these new star schemas, if you will, on the fly it's a really it's a very different world than what we had in the 80s.
Claudia Imhoff: let's talk about some of the benefits here and there are many i'm going to touch on just a few.
Claudia Imhoff: I like the slide the benefits of the star schema less environment, it means that we have opened the doors to the data it's, not just for star schemas anymore.
Claudia Imhoff: It can be individualized you know, a unique tool, if you will, to that person, they can have whatever kind of design, they want.
Claudia Imhoff: They can spin up or school upstart schemas on the fly, it can be industrial strength we can have some noise known star schemas that are mapped already and perhaps cashed in our memory and we have them ready to go it means it means that the data is very reusable for any kind of.
Claudia Imhoff: inquiry that we can think of even the artistic ones are the experimental ones I love the experimental school, by the way, i'm not sure that would work, but the experimental tool.
Claudia Imhoff: We can say what is, what is this did this ever happened, what if I do this.
Claudia Imhoff: It does this ever happen Oh, it does well, let me drill into that a little more in more detail, and so forth, and so on, we now have opened up the world of our stool too many different shapes of this tool, and it can be done with ease.
Claudia Imhoff: So flexibility agility boy.
Claudia Imhoff: taught us anything, it was that our world can change in a heartbeat and everything about it can change in a heartbeat.
Claudia Imhoff: All of our business goals all of our business decisions all of everything in 2020 got thrown out the window in March.
Claudia Imhoff: We shut down, we had to change how we do business, who we do business with where we do.
Claudia Imhoff: so forth, and what that taught us is that decision support systems were more critical than ever and they had to be flexible.
Claudia Imhoff: We had to have the agility to change on a dime business users had to be able to ask impromptu questions.
Claudia Imhoff: With unlimited dimensionality not limited to the stars theme and physical star schema is so they can certainly do much more complex and detailed data.
Claudia Imhoff: All while receiving that really good response time maintenance think about that those of you that have been maintaining star schema for years, you know that maintenance is.
Claudia Imhoff: really difficult sometimes very complex design sessions can be greatly reduced, we don't have to design everything around a question we just bring in the data that means that atl is also simplified and maintenance too hot is less than it's much less than it was in the early days.
Claudia Imhoff: Okay, so.
Claudia Imhoff: data storage requirements they get reduced I lose a lot of the redundancy that I had to have the four.
Claudia Imhoff: star schemas that we're almost identical, but had slightly different dimension, I don't need that anymore column or storage compressing the data itself, making the storage even smaller we don't need index let's pause for a moment and think about that for a second we don't need index.
Claudia Imhoff: wow think of what that means developers you're freed up to do more valuable activities than simply maintaining the star schema universe.
Claudia Imhoff: You can focus on many different things, maybe did more difficult data problems increased availability volumes of new types of data, all kinds of things you can do, maybe more advanced forms of analysis, how do you support your data scientists now or your advanced business analytics.
Claudia Imhoff: And then you can re evaluate the entire process or the entire set of schemas and maybe think about things that you hadn't thought about before, and now you can't do it's it's an eye opening experience to be freed up from this.
Claudia Imhoff: Alright last little bit and then i'll turn it over to Matthew for his DEMO which I am most interested in seeing Matthew Matthew who has the coolest backdrop of idiot, as I have this great white void, he has this cool car truck or.
Claudia Imhoff: What we will hear from him shortly and I can't wait to see that DEMO but let's talk about getting started okay.
Claudia Imhoff: Many of us many of us have legacy data warehouses, many of them are based on star schema design, so if that's true what do we do if we have an existing environment.
Claudia Imhoff: How do we What steps do we take to migrate to a star schema lists type of environment, well, first of all start looking at your email process your transformation process where are the star schema bottlenecks.
Claudia Imhoff: Where do you have your your worst problems decide which star schemas are particularly burdensome those that change a lot those that are hard to create maintenance is tough and so forth, I would pick them as my first target for migration, the ones that cause the most problems.
Claudia Imhoff: Then you can group them by business problems they solve, for example, these are the ones that are surrounding customer profitability or.
Claudia Imhoff: Product profitability or campaigns or whatever it is this little domain miniature domain, if you will.
Claudia Imhoff: You can group to start schemas by the business problems by those business domains and prioritize those problems stars.
Claudia Imhoff: As to their criticality, in other words, this one is being hammered on a lot, maybe you don't want to immediately rip that one out maybe you want to have a little bit of time to get some of this under your belt, so you understand what you're doing and where you're going.
Claudia Imhoff: Each grouping can become its own project you're going to re evaluate it you're going to redesign it, you have a look at the underlying data and see if it's good and so forth, and it will give you a good path forward.
Claudia Imhoff: Then three you begin analyzing the detailed data from which the star schema was developed.
Claudia Imhoff: The data can add even more flexibility we always talk about the grain of a star schema well let's get to the lowest grade and take it out of a star schema design and then build up from there.
Claudia Imhoff: You may discover all kinds of things errors and previous implementation data quality problems things you can fix immediately.
Claudia Imhoff: it's a quick win for the developers and it's a quick win for the business users as well, they get access to this expanded data set and they can mix and match and build and do whatever they want ask whatever questions they want at that point.
Claudia Imhoff: Then number four what's your migration path.
Claudia Imhoff: He moved to set of star schema data for each business problem into the new environment according to your priorities schedule okay we're going to move this set of star schemas first the next in the next and that's your quick when isn't it.
Claudia Imhoff: And then five expanding your data acquisition horizons, if you will.
Claudia Imhoff: there's data that you might have thought would be on your development capabilities.
Claudia Imhoff: take a good look at that again maybe it's not beyond your development capabilities, maybe you can now bring it into this new environment.
Claudia Imhoff: So take a look at some of your past requests that you said nope nope impossible can't do it can't do it was a desktop of those because some of them yeah you can't do.
Claudia Imhoff: But again, your data volumes will not be a problem query performance times with delivery all these things that were so problematic become less of a problem now they're not as big a problem.
Claudia Imhoff: Number six life is good.
Claudia Imhoff: If you remove the star schema creation and maintenance that burden of the design creation and maintenance, it means that there is much more time.
Claudia Imhoff: freed up for development and again go through that backlog start reducing the backlog for analytical requests Now you can do some of these things, and you can do a much faster than you could pass.
Claudia Imhoff: And let me talk about if you are one of the lucky few that might happen to have a green field, in other words you have no analytic or.
Claudia Imhoff: Minimal analytic capabilities, you still need to understand the business users needs, I would again develop those little domains of expertise and questions business problems group them all together by the business domain.
Claudia Imhoff: And you can still determine how much data and data quality processes will be needed.
Claudia Imhoff: So you've got a wonderful opportunity to start fresh start from the beginning, building something that is incredibly flexible and really fast and useful to the business community.
Claudia Imhoff: Unfortunately i'm going to leave Matthew to talk about new applications and analytics that he has up his sleeves here at headquarters he's going to talk about that in the next session section.
Claudia Imhoff: Alright, so as a summary wow life is good, given the advances in our analytical technology, it is time to rethink how we build our environment.
Claudia Imhoff: The data warehouse design and processes, maybe we can read retool those think of them in a different way, yes, you do need repositories of analytical data.
Claudia Imhoff: And you still need et al, or some form of data, integration and data quality processing, but I think you're going to find that it's a much easier set of processes that it has been in the past that we have a lot of flexibility and agility and how we create these environments.
Claudia Imhoff: You do still need to perform maintenance on the store data but you're doing it on a very flat set of data that columnar in memory stored data.
Claudia Imhoff: there's much less of it no index, and certainly much simpler data schemas themselves.
Claudia Imhoff: So now yeah sit back relax you can solve many of the past difficult problems by bringing in this newer technology, this new way of approaching something.
Claudia Imhoff: A new way of making of bringing decision making into your organization giving much higher flexibility much more agility to the overall environment.
Claudia Imhoff: So life is good, and with that I think i'll turn it over to you for the DEMO Matthew i'm very excited to see that it should be a very good one and i'll stop sharing my screen, so that you can you can show us the DEMO.
Matthew Halliday: Great Thank you very much, Claudia spread to hear from you, as always, and so what i'm going to show today is exactly how does this manifest itself inside of the core platform.
Matthew Halliday: And so what's i'm going to go through as an example of how we can take not just one system, but how we can take multiple systems and get a true complete picture of our customers so we're going to look at.
Matthew Halliday: Customer data that's coming from an earpiece system in this case is Oracle and and another case we're going to be bringing jira tickets and will also bring in.
Matthew Halliday: Some models around what we're seeing with reduce churn and potential churn of a customer and bringing all those things together, to be able to better understand exactly what's going on.
Matthew Halliday: With our customer environments now, if I were to go and connect to an oracle instance in.
Matthew Halliday: In the Oracle world that data is in such a way that you're going to Oracle would recommend that you have a.
Matthew Halliday: starsky man, so they would recommend something like this, so if you say i'm going to get some receivables here's all the star schemas that exist just for receivables.
Matthew Halliday: And so, this would be the structures that you'd have to build you'd have to have atl map take the structure from the relational format that it's in and was built.
Matthew Halliday: and bring it into this star schema format now, the problem of the star schema format it's great I guess once you actually have it, which can take months to build and load and kind of all those challenges.
Matthew Halliday: But one of the big problems is is it's 10 discrete views and being able to look at a factor fact join, for example, you cannot do it.
Matthew Halliday: Right, you have to get can join the dimensions between facts you can't actually have.
Matthew Halliday: The transactional detail and these fact tables are the really big tables, so the big table in the middle here might have you know, a billion rows and then these tables around the outside might have 10,000 rows.
Matthew Halliday: And this will perform, and one of the reasons we did this nice to work at Oracle, and this is what we would do is, we would try to minimize the joints we've tried to reduce the complexity and then join, so that the dashboards will actually perform.
Matthew Halliday: So you can actually get to some query performance so many ways a lot of people think sequel tuning is all about just refining your sequel statement in reality it's not.
Matthew Halliday: A lot of the times, your sequel performance tuning is actually changing the underlying data model, in most cases, that means removing joints and could be.
Matthew Halliday: d normalizing the data so bringing it down flattening it reducing the joints you need to go through, because those things.
Matthew Halliday: add a lot of complexity and we all kind of know this inherently because this has been around long enough to remember the days when we used to call up.
Matthew Halliday: For our orders and get statuses on orders would call up and invariably you'd give your order number and the thing you knew like clockwork which is going to come next is oh wait.
Matthew Halliday: Give me just a few moments here my systems, a little slow, this morning, as if the computer not had a cup of coffee or something.
Matthew Halliday: It was kind of ridiculous, but what it was doing his info just one transaction, it was trying to stitch together all these joins behind it, because even though you were giving one order.
Matthew Halliday: It actually might have been looking 40 tables to actually bring all of the information about that order the product, the shipping address the payment.
Matthew Halliday: Details all of these things were coming from a lot of different tables, which was stored in the source application.
Matthew Halliday: But you can't do analytics on that that's been the reason why the star schema came into being, like you just couldn't run it everyone says like it'd be great if we could just run analytics on top of.
Matthew Halliday: The application itself, yes, you don't get slowly changing dimensions, you don't get historical things in those models, but there are ways around that.
Matthew Halliday: Now the encode away is a little different.
Matthew Halliday: What we're going to say here within quarter is we don't believe you need to do that we're going to say we're going to take the data structures, as is.
Matthew Halliday: So i'm going to run through a few examples here and build this out, but I want to start with just showing you how easy it is to get data from your source application like an oracle EBS.
Matthew Halliday: or Oracle Dr P system and have a party representation of that data and have it to always reflect your database and do this without having to do a line of code.
Matthew Halliday: And so, this is a, this is actually pretty cool if you've ever tried to do this before and keep parquet which is traditionally a pen only if you need to do any updates.
Matthew Halliday: How do you handle that because our transactional system is getting updates all the time, and so we can actually handle that for you as well, we can also handle things like.
Matthew Halliday: You know what I want to keep a version of this record every day, so I want to put in addition to the primary key i'm going to add the date, and so I want a delta record and what we call this as.
Matthew Halliday: As sparse snapshot in not dense snapshot in where you take a full capture of everything, but we can actually on the fly.
Matthew Halliday: bring back and replicate this stuff together so i'm going to go go in and kind of just show you some of these pieces and walk through this.
Matthew Halliday: So i'm gonna start off with my data wizard here, this is my quarter platform and my home screen there's a number of things that I can do, I can learn about the the the capabilities of the Platform, I can go to some dashboards.
Matthew Halliday: But here i'm going to focus on getting the data into the system and so here.
Matthew Halliday: I can set up a new connector we have a lot of connectors available here that you can connect to, and you can extend these we have an SDK available for that.
Matthew Halliday: But in this case i'm actually going to use an existing data source that we have already in place so i'm going to collect an oracle EBS instance.
Matthew Halliday: And so here i'm going to look through, I can see here's all the schemas that are in my Oracle instance in this particular case i'm gonna look at payables.
Matthew Halliday: And so i'm gonna look at all of the objects contained within payables now, this is all the tables that are behind.
Matthew Halliday: Oracle payables and so you can see there's there's a ton of them right and see me scrolling.
Matthew Halliday: This is no small feat right and everyone starts with this, this is our starting point, regardless of if you go into star schema or if you're going in quarter, everyone has to start with demystifying and understanding this.
Matthew Halliday: Now the great news is that in quarter for a lot of these business applications we've actually demystified it for you, we give you what we were just blueprints but really these analytical applications purpose built.
Matthew Halliday: To take over the objects that you need in order to build together, something that you can interact with now i'm sure, a lot of people going by Matthew.
Matthew Halliday: star schema is a good, not just for performance they're also good for simplifying because, like these tables are overwhelming, how is anyone going to know and i'd say.
Matthew Halliday: I absolutely agree that you do not want to put this level of detail in front of the people, building and we'll get to that in an area we've heard with business schemas.
Matthew Halliday: where you can create virtual metadata semantic layer abstraction layers how you want to think about it, sitting between the business user or the data analyst who's looking at the data and actually what's behind now.
Matthew Halliday: So let's go ahead and just maybe pick a couple of these objects, just to kind of show the flow so come to the big main tables.
Matthew Halliday: In payables is the headers, this is the header information for that transaction and then i'll just bring in the lines.
Matthew Halliday: So i'm just bringing two tables, is a very basic, but these are generally two large tables that you would see.
Matthew Halliday: Most of the time you would create these as different facts you wouldn't join them together.
Matthew Halliday: Because there's details that are on the header that are not and measures that are on the on the lines and so forth, with distributions as well, but let's just proceed with us.
Matthew Halliday: So you see here i've got you know add columns i'm going to bring in let's give it a name here, so I just call it, you know my ap DEMO and we'll just go ahead and load this data.
Matthew Halliday: And so now it's going to create that schema for me and it's going to start loading it's going to start extracting that data from that Oracle instance.
Matthew Halliday: instantly and it's bringing it across exactly is the same shapes you can think of this as replication.
Matthew Halliday: So we're replicating the source tables out of the source system because honestly if we were to do any type of query processing that did any of those joins on the source system.
Matthew Halliday: We know it wouldn't work, it would be dead slow and we'd have a lot of trouble, so my data is already loaded This is just some DEMO data to kind of give you an example, I want to draw jumping now into a little bit more of a.
Matthew Halliday: larger volume in a moment, but again, this is just the process that we go through and it's the same exact process, even if this is billions of records and these tables.
Matthew Halliday: i'm going to go ahead and create a join, though, between those two tables I didn't choose to use our blueprints if I had done that I would have had all this joint information provided for me, but.
Matthew Halliday: I just brought them in as independent objects, they could have been anything in quarter this point so sick gnostic to it, you could have gone to salesforce you could have gone to netsuite.
Matthew Halliday: it's just data that resides in tables so i'm going to go ahead and create a joint here.
Matthew Halliday: And so here's my two tables you see my data is loaded going to create a new join and the child table would be my lines and i'm going to say the.
Matthew Halliday: header ideas, that is, the parent and then i'm just going to bring that in and say there we go so quit my join it's done and now what I could do is just start to build a very basic you know report on top of this, so I can go ahead and save my changes.
Matthew Halliday: And now I can go and explore the data and build right on top of this right, and so let's just say, I want to bring in my entered credit amount i'll drop that in i'll change this over to me a aggregate a table.
Matthew Halliday: And then i'm going to drop down here, and maybe get from my head is table, I want to bring in just the.
Matthew Halliday: period name so let's bring in the period name and then instantly I get for every period name what's the total enter credit amount.
Matthew Halliday: And so very easy bill to pull those in and run queries instantly against those kind of.
Matthew Halliday: data sets now we haven't had to flatten that we have to change the structure is just using two tables at this point very basic, let me show you a more complicated example.
Matthew Halliday: What i'm really showing here again just to reiterate is the simplicity of a no code bring your data from your source application could be thousands of tables, we have customers with.
Matthew Halliday: You know 10s of thousands of columns up to a case of hundreds of thousands of columns that they're bringing in.
Matthew Halliday: And then any point a business user can have access to those columns a lot of customers, we say probably only using about three to 4000 columns every day.
Matthew Halliday: But those columns can change so if you were to come and say this is great now I still have access to all of these columns if you said, well, can we look at this based on.
Matthew Halliday: You know, a currency, you could just drag and drop the currency, even if that was something that you hadn't asked for before you have that behind the scenes.
Matthew Halliday: Now, again, this is, this is our analyzer This is where we build out and there are visualizations you can build using this particular part of the product, but for this point i'm going to move away from here for now.
Matthew Halliday: So i'm going to go ahead and cancel out of here.
Matthew Halliday: And i'm going to scroll back to my home and actually i'll show you some different schemas so let's go into the schemas here, so these are some.
Matthew Halliday: Little more larger heavy hitters, so to speak, so let's go ahead and look at our EBS a schema.
Matthew Halliday: And so, this schema has a little more volume in here, you can see right we've got the are a customer transaction lines all again, this is the source table from your source application in this case it just so happens to be Oracle EBS.
Matthew Halliday: it's a good example because it has a lot of tables and it has a lot of data, and you can see here we've got 470 million rows here we've got hundred 38 now payment schedules.
Matthew Halliday: it's a 2 billion record data set this is not a star schema so if we look at the stars, the diagram for how are these things joined to each other.
Matthew Halliday: We can very quickly see that this is not a star schema by any stretch of the imagination and if I.
Matthew Halliday: drill into these objects, you can see these actually are objects that are joining to other parts of the product as well, so within EBS I just don't want to look at payables in isolation, I want to look at it with these other things like purchasing blanket.
Matthew Halliday: orders headers purchase orders, I want to build a look at the party here information.
Matthew Halliday: You can look at you know all all the different components and if anyone is familiar with these then recognize these tables has been the source application tables.
Matthew Halliday: And so there's a lot of you know, data here that's all interconnected and again blueprints bring you this out of the box so for a lot of these applications, whether it's netsuite or salesforce.
Matthew Halliday: Oracle guide wire different lots of different applications SAP that we've brought together this level of detail that's brought in.
Matthew Halliday: One, this is in I can now run queries against this, I could build directly on top of this, in reality, I don't want to the right because.
Matthew Halliday: I don't remember which column comes from which table and, ultimately, it could change Oracle might change the data model.
Matthew Halliday: And if that happens, I don't want everyone have to rebuild their dashboards so that's where we bring in business schemas so business schema is a nice way of creating a very user friendly view on top.
Matthew Halliday: The good thing about these business schemas is they're not materialized there's nothing behind the scenes, apart from metadata is literally a list of this.
Matthew Halliday: Around how that can be connected and so here if we look at let's look at we've been looking at ar so let's look at a up transactions.
Matthew Halliday: And so here if I open up these these three views you can start to see these are all coming from different objects within UBS ar right because my transaction lines all payment schedules.
Matthew Halliday: And then you can put in descriptions and labels and then I can build directly on top of this, so I could come in and just explore this data, for example, and I could just bring in like let's bring in all the key measures from here.
Matthew Halliday: And then run against that and be able to look at all of that data that's coming back and be able to then.
Matthew Halliday: understand exactly you know what tables we use behind the scenes so looking at those objects, I can start to look at the query plans behind it, so I can come into here and start see here actually the tables that were being utilized and used as part of this particular query.
Matthew Halliday: i'm going to jump over here now and show you something that we built using this schema so i'm going to pick the receivable schema here and we have.
Matthew Halliday: Something called customer churn, and so what we've done is we've taken that receivables data and we've extended it we've said let's you know combine it with some information from jira so we have a jira in here if I search I think for jira.
Matthew Halliday: We can see you've got some service desk tickets coming from jira I can go ahead and look into this and i'll see there's a whole bunch of objects that have been brought in from jira.
Matthew Halliday: So I can look at that if I go back to my schema listing here and look at my customer churn i've also been able to put in a notebook so i've got some customer activity over time.
Matthew Halliday: But then I actually created here materialized view.
Matthew Halliday: And so, in this materialized view it gives me the option to do some data science work, so I can create now a notebook experience on top of this data and start to build out some classifications look at tickets creation look at.
Matthew Halliday: You know what's what's actually been going on in terms of the in terms of the experience, yes, you can see right here we've got the customer churn we got some gradient forest.
Matthew Halliday: trees that we have here, we got some boosting classify as we go through a process of being able to.
Matthew Halliday: enrich this now, these are sometimes part of our blueprints as well.
Matthew Halliday: And that you can leverage these, but these are the things that you can build on top of this data, so if you want to enrich it make it much more valuable you have this whole spark capability, as part of the Platform.
Matthew Halliday: So what becomes super powerful with this is that not only now do we have this extensive ability to do things like much more modern technologies like pie spark and.
Matthew Halliday: scala On top of this we can actually go ahead and bring in.
Matthew Halliday: The data very easily and we don't spend all the time just preparing data we can actually get into this and and be kind of.
Matthew Halliday: Just dragging dropping the columns creating a flattened data set to feed into a model now has never been easier I don't need to go through details and think of you know.
Matthew Halliday: The grain of record and figure out all of that it's much, much easier for me to always move it and change it, as I need.
Matthew Halliday: So I have that in place and now let's go and look at actually how does this look.
Matthew Halliday: And so, all of these things I showed before joined together here, I have a dashboard This is my customer 360 dashboard and it has some metrics in here around customer retention, so I can see.
Matthew Halliday: Some information here around you know the tickets that have been logged.
Matthew Halliday: The churn probability against the account revenue, I can see here the year view of growth.
Matthew Halliday: For the customer, the growth rate for the bottom accounts can see here the year over year what my churn probability percentages again using that materialized view using a notebook with a data.
Matthew Halliday: machine learning model behind it, and I can come in here, and I can just say Oh, this is great, I can look at things of course you can drill down and filter and everything will be connected and drill down.
Matthew Halliday: Accordingly, but I can also search so in here, I can come in and just start typing you know staples.
Matthew Halliday: And I can click on staples and then be able to see this dashboard specifically the staples.
Matthew Halliday: And that could be anything that could be down to a something that's on the transaction on the lowest level, it could be something on the highest level, it could be a dimension value.
Matthew Halliday: You have all of that great capability to to dissect and jump in and move back in and now it's almost like you can look at 100,000 foot view and then go two inches off the ground.
Matthew Halliday: and be able to go through that and all these aggregations of being done on the fly using the relational model that's an existence, however it's sitting in residing inside of in quarter and in quarters able to do those joints for you in a very, very efficient, effective way.
Matthew Halliday: So you can see here I looked at this particular account, I can see the churn rate is a little lower, but I want to dive in and see exactly what's going on with staples.
Matthew Halliday: And so, here we can go ahead and drill into a a customer account dashboard so i'm going to look at.
Matthew Halliday: A dashboard specifically now for staples you can see the filter has been applied and i'm looking at this specifically for staples.
Matthew Halliday: I can see here, you know revenue mount my issues, I can see the organization information stuff that's coming from salesforce.
Matthew Halliday: bringing this, all together, so I can better understand exactly what's going on with this account.
Matthew Halliday: I can look at you know the issue statuses the types of interactions where the contacts reside.
Matthew Halliday: And, but here I can see that you know I want to drive in and drill into some of these staples issues that we have here so jira.
Matthew Halliday: So I can drill down even further and just keep on navigating through this and these drill paths.
Matthew Halliday: are literally not hard coded they're just literally just hey bring up this dashboard pass this filter the very easy to configure and set up.
Matthew Halliday: And, but then it refines the data that you want to look at and again if I was to remove the filter it the customer tickets across every single customer.
Matthew Halliday: So in here, you can see, again, we can look at statuses the various things you'd expect.
Matthew Halliday: But again, we didn't have to start with a star schema will literally taking the model and building those queries on top.
Matthew Halliday: I can look here and see there's a whole bunch of you know tickets in there, I can start to say you know here, there was something around the limiters.
Matthew Halliday: Okay there's a limited file issue here, I can just search click on it and refine it and then see exactly what that issue is, be able to even open up.
Matthew Halliday: The jira specifically for that to get access to more detail so really, really powerful in the way that I can can move around my data.
Matthew Halliday: And I don't need to stop thinking about what are the business requirements up front, I didn't have to think about when I build out these dashboards and when I work with the data.
Matthew Halliday: what's the data I need what's going to be my dimension values what's going to be the grain of record what am I star schema structures.
Matthew Halliday: What happens if you need to navigate across them and they'll have those connected, this is where it gets really, really interesting and important platform, because we give you those capabilities.
Matthew Halliday: To be able to do it without being tied down to all of the limitations of the star schema design, which I believe is completely antiquated.
Matthew Halliday: The reason I say, that is, I remember my very first sequel class that I was in.
Matthew Halliday: And if I had gone to my first equal class and then never done any more, if I just don't sequel one on one in school and never kind of attended what had happened later.
Matthew Halliday: I would have thought that I could run these types of queries just directly on the Oracle database, like, I would just go ahead and.
Matthew Halliday: Say yeah you know I can look at the screen and you have revenue information you have zip to location for bill to and someone said, can you just give me the revenue by zip to.
Matthew Halliday: By zip code and do an aggregation on it i'd be like sure I can write that I know that joins i'll just read it out and run it.
Matthew Halliday: And then it'd be like running and running and then hours later it's still running and then all of a sudden, I get a snapshot to old error message and that wouldn't work.
Matthew Halliday: Well, that same principle works on a quarter.
Matthew Halliday: So what used to be the most difficult thing that you would ask a database to do we purposely built in quarter around solving that core problem, which is where the where the problem was preventing us.
Matthew Halliday: Or why we even have to go to start schemes in the first place is because we couldn't get these things to perform in many ways it was.
Matthew Halliday: A work around performance issue, yes, there are some other benefits, you know things like the simplicity of room looking at a star schema.
Matthew Halliday: But it doesn't get simple when you want to have a holistic 360 degree view when you want to look across everything.
Matthew Halliday: So i've covered quite a bit here normally this, we could have we drive into a lot of detail here could easily spend two hours unpacking everything and building it and showing how easy it is to navigate through this.
Matthew Halliday: But hopefully you kind of got to see the complexity of the structures that in quarter can handle without the need for a star schema add scale of like 2 billion records to do aggregations on the fly.
Matthew Halliday: without having to predetermine what those questions are going to be so, if someone comes to me said Matthew.
Matthew Halliday: Can we add a new value well in our business schema we could just go in and Edit that er transactions and just edit the schema and add.
Matthew Halliday: add a new value to it, so I could just go and find what it is drag and drop it in I don't have to go, say, well, I got to rebuild my email structures have to rebuild my star schema after go reload it.
Matthew Halliday: Just one last point that I would probably want to bring up around things like slowly changing dimensions, this is a common one, we get asked about honestly.
Matthew Halliday: Is yeah but slowly changing dimensions if you're always replicating what's in the source system, how do you handle that, and so the way we do this is.
Matthew Halliday: When the data comes in, you can do incremental loads so we don't have full loads you know full refresh your data, every day, so you can do the refresh because it's mapping directly on based on the replication type style.
Matthew Halliday: What ends up happening is you can do refreshes a lot more frequently not you know, probably the best i've seen that any kind of large enterprise with a star schema is.
Matthew Halliday: For every four hours or so that's the best they could get to certain areas and they normally only did that during critical moments of their business, such as like quarter and close.
Matthew Halliday: Within quarter because we're mapping directly to the delta just what the what has changed in the last 10 minutes can be easily flown into in quarter and then you can get queries back in seconds.
Matthew Halliday: So you're looking at really bringing in analytics much closer to operational analytics and this model as well you're able to now not just have operational reporting and then have analytics on star schemas.
Matthew Halliday: we're able to kind of merge those two experiences together and give one view of all your data, so people can make more informed decisions at the moment of making those decisions.
Matthew Halliday: So, if we look at like an EBS as table now let's say you want to say, look at this transaction lines table.
Matthew Halliday: And in here, you say Matthew I we we don't want this to be always reflecting the database, we want to look at historical information, we want to see how things were changing.
Matthew Halliday: We can go in, here we can create an incremental query and in that incremental query you can see, here we can.
Matthew Halliday: select from that table, and then we have this creation date now what we can do here is actually set this up.
Matthew Halliday: To have an incremental column, and then, when we go through that we can then go ahead and say, create a version, and so what will end up happening is for every day.
Matthew Halliday: If there's an update your credit version so let's say you update the file on the Tuesday or the record on a Tuesday you'll end up getting.
Matthew Halliday: record for Tuesday, if the transaction was not updated you'll have the record from when it was last updated let's say that was Monday so.
Matthew Halliday: Then you have one on Wednesday, but the other two are like Monday was the latest version Tuesday was the latest version so it's kind of like.
Matthew Halliday: A time machine kind of approach with us like your MAC and you go to time machine, you can pick a date, and we can reassemble What did the view look like, because we can just say, give me the latest version for this particular transaction ID.
Matthew Halliday: Based upon the last updated date so and it can't be higher than this version, so if I want to see what did it look like on Tuesday.
Matthew Halliday: I would see the transaction for Monday the transactions from Tuesday, but when this transaction will not be visible.
Matthew Halliday: So that's what I refer to a spouse snapshot because that's only incrementally are being stored.
Matthew Halliday: it's a really effective and efficient way for us to give you a really great slowly changing dimensions Type two dimensions.
Matthew Halliday: without having to kind of be bogged down into creating a lot of copies and duplications of data so we're literally just keeping the efficiency of that and so.
Matthew Halliday: that's that's kind of stuff I wanted to show today that hopefully helps bring what Claudia was talking about into kind of a little more tangible reality, and I think now i'll hand back to Emily and see if we have some questions.
Emily Lewis: Great job we have a lot of questions so really lively chat, thank you for everyone for participating i've tried to pull out a few questions.
Emily Lewis: But if your question didn't get answered in the chat please put it in the Q amp a section, and then we can address it there, so first off we've been talking a lot about star schemas does this also apply to other animals analytical designs.
Claudia Imhoff: Do.
Claudia Imhoff: yeah actually does there has to be some underlying data, but, for example, if you have a date of all congratulations that's probably a pretty good underlying.
Claudia Imhoff: set of data from which you can virtualize anything you know you can that semantic layer can be used with a with a date of all designed quite nicely if you have a mix of things.
Claudia Imhoff: You need to go back to the basics back to the the lowest grade and bring that data in so that you can begin to mix and match and.
Claudia Imhoff: The data model does serve a purpose it's a wonderful source of metadata for people to understand what's out there.
Claudia Imhoff: So I don't think you want to just dump data into into any database, whether it's a quarter or somebody else's without at least understanding what the data is.
Claudia Imhoff: So there is there is some kind of thought that must be given into how do we document the data, but basically you can recreate any database design in a semantic layer as long as the data is there right and that's the lowest grade that for from which you, you can build that.
Claudia Imhoff: Matthew do you have any other comments on that.
Matthew Halliday: No, I think that's good, I think we can move on to other questions, too.
Emily Lewis: One of the reasons we started with star schemas is to eliminate complex joins in aggregations by removing the star schema and going back to join aren't we going back to the original situation.
Matthew Halliday: We are yeah I would say, we absolutely are.
Matthew Halliday: But without that's assuming right there there wasn't any innovation to fix what was that root problems and understand why did we go down this path you really need to get to really agree and understand that.
Matthew Halliday: It was a performance limitation based upon joins there was no way you could join billion records to another billion record table to 800 million.
Matthew Halliday: and expect that to work at that scale, it will not work, you need to apply filters to a very, very granular level like a single ID or a group of ids you need to really bring that that, and you can just wide open go for it.
Matthew Halliday: Which is why you know if people say well okay put visualization on top right a lot of people say, well, I can put power bi or tablo directly on top of EBS.
Matthew Halliday: But it won't work, because the Oracle database cannot feed that data it's never going to work so incredible take that data replicated across but can actually make tablet you can connect tablo and power bi and micro strategy to in quarter.
Matthew Halliday: and run that and get that performance and because we can handle those joints and so really in reality we are going back.
Matthew Halliday: But we're not going back to the same world it wasn't the 80s, because you know what the innovation has dramatically gone forward, but a lot of the solutions are still based upon the methodology that was.
Matthew Halliday: which was created, and people are still holding to to some degree, even though the technology has moved on and really questioning is that still the right thing to be doing.
Emily Lewis: Great another question, how do you eliminate slowly changing dimensions in a virtual star schema if the source doesn't save point in time, historical perspective.
Matthew Halliday: yeah so that's where it does not matter if the source system does it because the source system will capture that point so if you were to run an incremental update within quarter, like every day.
Matthew Halliday: You could say, I want to keep a version of that transaction every single day so every time is an update will keep that and will stamp it with the day so in quarter can become.
Matthew Halliday: That that historical snapshot of what it looked like because every day, you will look at your data and they'll say oh here's a version of the data i'll keep it.
Matthew Halliday: The nice thing that within quarters it doesn't have to keep a full set of the data it literally just keep a set of what changed so if you can think you've got a million transactions.
Matthew Halliday: Historical maybe 2020 years worth of business if you're seeing 10,000 insert and update today.
Matthew Halliday: it's 10,000 a day that you're looking at for every day that you want to keep that snapshot it's not.
Matthew Halliday: I got to take a billion records and then another billion and then i'm going to work out my snapshot is on a quarterly basis or a monthly basis, you could have a snapshot on a daily basis and then re orchestrate and connect it and create it.
Matthew Halliday: Depending on how you want to look at that data, so I should remember very powerful solution for handling that, especially when source systems do not have that historical information.
Emily Lewis: Great somebody's asking does incorporate expose its data model can I connect to this model using tablo, for example, Matthew we got a lot of questions in chat about all of you know connecting to various goals.
Matthew Halliday: yeah so while encoded does have a visualization and.
Matthew Halliday: It does have definitely say has its benefits a lot of customers love it because of the integrated nature, the fact that it's very We made it very simple and easy to use for for a lot of people who maybe are not.
Matthew Halliday: Experts in those other visualization tools they do tend to gravitate towards and quarter, however.
Matthew Halliday: We do have an official tablo connector if you go to tablo calm and go to their extension gallery you'll find there's an official.
Matthew Halliday: connector for in quarter, that you can connect tablo to in quarter.
Matthew Halliday: We do make it available the schemas can be made available what's nice about this is, you can make the business schemas available, we can control access as well to who has access to what.
Matthew Halliday: So there's full access control I didn't get into the security parts of this, but one of the benefits to here is that you can put down record level security and and quarter, so you could have a tablo dashboard, for example.
Matthew Halliday: And you can only see the transactions and you should have access to the source application.
Matthew Halliday: Because we can mirror because we've got the instructors looking exactly the same.
Matthew Halliday: So if the structure is exactly the same it's easy for us to model exactly the same security model as the source application inside your analytics if we transform it to a star schema good luck, it becomes really difficult to try and secure it to the same degree in level.
Emily Lewis: Great robbie's asking what about the cases when there are multiple sources for one type of transaction, for example in the DEMO for ap there are multiple sources, how will that be handled.
Matthew Halliday: So there's multiple ways, you can do that, and we have a number of customers, where they have.
Matthew Halliday: actually grown our very first customers have been super well in this area broadcom with massive acquisitions over the years, they were originally fargo and then can broadcom.
Matthew Halliday: But they've acquired many different the rp systems applications.
Matthew Halliday: and being able to get a single like consolidated financial statement for them was really critical and we're able to do that, that massively expanding the team or doing expensive VIP migrations.
Matthew Halliday: They were able to do that effectively in quarter, the way that we do that.
Matthew Halliday: Is you got a couple of ways, one is you can bring the data into a quarter let's say it's SAP and ABS and you'd create an EBS version with the data and Sep.
Matthew Halliday: What we can then do very easily is create to queries against in quarter, they give you what the consolidated view would look like maybe its revenue.
Matthew Halliday: chart of accounts period name that you want to bring together and you create those same queries and same way as I was creating creating an analyzer.
Matthew Halliday: You can create those and then feed the output of both of those with with engineered calculated column say this came from the EBS instance.
Matthew Halliday: and be able to then load it into a single table.
Matthew Halliday: So then, you can have one table that has a consolidated view what's nice about this, as you can join that consolidated view down to the drill the details, so if you want to say.
Matthew Halliday: What was behind this, how does it connect to the actual source SAP system, you can easily navigate across and see it, so it becomes a very interconnected.
Matthew Halliday: way of one being able to validate not just blindly trust the.
Matthew Halliday: numbers are correct, you can go in look at them drill down and again they should always match exactly what you see in your system because we're doing a replication of it, one for one.
Matthew Halliday: record for record column for column table for table, so you definitely get all of that, at your disposal fingertips, to ensure that the data you're seeing in that environment in that use case is correct.
Emily Lewis: Great.
Emily Lewis: And one other person is asking Matthew our isn't quarter cloud or on Prem.
Matthew Halliday: So we do have a cloud offering you can go on today to incorporate calm and you can sign up and experience that get started for free and and experienced that we do have customers who are on premises as well, so depending on what works for our customers, we we do support that as well.
Emily Lewis: Great david's asking, and he says, you know, perhaps your address this kimball made a case for not just physically using the schema but also for defining business intelligence needs.
Emily Lewis: That is the dimensional model describes how an organization wants to evaluate itself, do you still see utility and using these models, at least for that purpose.
Emily Lewis: Even go ahead.
Claudia Imhoff: Oh pick me pick me for that one.
Claudia Imhoff: yeah Do you still see utility and using these models, at least for that purpose, even a physical Isaac Devon has become you know a thing of the past.
Claudia Imhoff: What a great question David and I will tell you I would keep those star schema models, even if they're on paper.
Claudia Imhoff: or in a data modeling tool, because they can serve as kind of a roadmap for a person who does want to use that kind of a schema it's kind of the metadata Meta Meta data I guess.
Claudia Imhoff: It will help them in the long run kind of understand okay here's the data that I want now let me go to this.
Claudia Imhoff: This new world and Let me pick out that data, and when we make sure that i'm recreating what I think I was recreating or what I had in the past it's just a safety check and it's probably not a bad idea to keep them around.
Emily Lewis: Great we are at time so I will end with one last question on ravi us how does the cloud version of incorporate connect with on Prem data.
Matthew Halliday: yeah I love the question so there's a number of ways, you can do that what we have done loading quarters we've created a product called the data agent, which is.
Matthew Halliday: A lightweight connector that you install inside of your firewall.
Matthew Halliday: And then we just create one connection between that data agent and the cloud solution and then from within your firewall it can then be communicate to all your internal systems.
Matthew Halliday: And so it's a very good way to transfer data and protective protocol.
Matthew Halliday: Secure protocol and be able to take that data from your source applications, without the need to open them up to our cloud instance to all of your databases internally so.
Matthew Halliday: That then we'll look inside of encoding just like a connector now you're able to use it, but it actually will be be navigating through the data agent.
Matthew Halliday: Which number of our customers i've actually seen improved performance customers who moved from on Prem to cloud i've actually seen.
Matthew Halliday: a boost in performance, most of us think that moving to the cloud potentially would be slower.
Matthew Halliday: But with obviously some of the advantages we've talked about today with elastic compute and with storage and some of the.
Matthew Halliday: improvements that have been made along there we were actually able to accelerate the performance of replicating data into the point that some of our customers actually using in quarter as well for.
Matthew Halliday: disaster recovery, where they might have an on Prem solution than they actually are replicating the data into encoder cloud, so that they can always just easily switch over to that.
Matthew Halliday: If something was to happen internally.
Emily Lewis: Great well, thank you both Claudia and Matthew this has been fantastic, thank you for everyone for participating in such a lively chat it's great to see that kind of level of engagement.
Emily Lewis: If you had asked a question and didn't get an answer, which I know there's a lot of you, we will definitely be following up but yeah Thank you everyone, and we will see you next time have a fantastic day.
Matthew Halliday: Thanks.