Collaborate on Spectrum Bill Sample for Organizations with Ease Using airSlate SignNow

Spectrum bill sample for organizations

okay hello and welcome to Ms Dynamics world.com FBI webcast series I'm Jason Gumpert and joining us today for this event is Christy Nicola of Dynamics bi.com chrisy is a Dynamics Erp veteran and he works with many Dynamics customers on selecting and implementing the RBI solution anything from an Excel based uh solution up through a wider R wider arrange array of specialized packages christe's also a frequent presenter um on the topic of bi for Dynamics and we are really glad to have him with us today so uh as we get started I just want to add uh we invite you to add your feedback and ask questions during during the presentation and there are a couple ways for you to do this first you can use the the Q&A or chat blocks that you'll see to the right of the screen um and uh you can use those at during the session or anytime after we'll also be tweeting during the session at with the hash msdw bi to uh share what we're learning also try to hear from you with your questions or comments so uh without further delay please allow me to welcome chrisy Nicola and hello are you there yes hi can you hear me we can okay hi everyone um as I was introduced my name is Chris Nia and we will be talking about uh making a looking at BI Solutions for the Dynamics Erp product and some of the things that we should consider through that process um as we um as we go through this product um I mean my background is mainly on the Dynamics and AV part but everything that we'll be talking is applicable to any of the Dynamics Erp product so it's purely targeted towards pi and the various uh the various options that are available we will start you know in we will start our presentation with uh discussing uh with discussing first uh what you know try to introduce the concept of why should you even consider bi in the first place and then once you know once we cover the topic we will move on to sort of what what is a bi solution made up of in terms of its components and therefore what you should consider in trying to evaluate one so without uh further Ado I guess we will start with our presentation um obviously everybody's familiar and whenever you with the D anemic CRP products everybody's familiar with reporting it is the first thing that we use to get information out our system so um that's obviously one option versus uh business intelligence and that's normally where every customer starts with so we wanted to sort of put into perspective what we feel that the place of reporting is and therefore by comparison webbi would actually fall into it um so if we move if we have a take a look at reporting so there are some prone pros and when we consider reporting and then there are some cons pros uh when you use a reporting tool generally then you get a direct access to that database that means basically um that means basically you have access to any table and any field within the database and also any report report that you're running will actually provide you live instant access to the latest uh information present in your database so basically if you're running say an open order report you will be getting the latest orders in your system if you're running a uh invoicing report you'll be getting the invoices as of that moment in your system uh usually reporting as I mentioned before something that comes there is an option the default option that comes with your Erp system uh basically the standard the reporting tool within the product or some of the SQL reporting services or everything like that um this these are some of the pros also in terms of cons when we consider reporting basically one of the cons is uh direct access to the database so why is that both a pro and a con you know Pro because it gives you sort of flexibility if you have the knowledge for it but also basically as you as you dat as the amount information that you're reporting on grows the performance of those reports goes down usually U usually RP systems are optimized for reporting and not for for writing not for reading uh generally when you to the reporting the design of the report usually requires technical knowledge um basically requires you both since you accessing directed database it requires you to understand a little bit about or quite a lot about the structure of the underlying tables and Fields or underlying structure of the application um also depending on the tools set they using it you know let's say if you were to use SSRS or even one of the reporting tools embedded within the within the actual DRP you would need to know some coding uh for the that whether it's nav coding or whether it's um whether it's um ax coding so that definitely usually people that design those reports are actually technical people um no one version of the truth so since with reports you're basically building reports and you're starting all with reports generally start from scratch even if you are copying something from a previous report you've done normally the concept is you have to start bringing together the table or tables that you're trying to to build as part of your reporting and then Define what uh Define what are the uh what is the information from there that you're trying to put together uh the problem with that is basically you could end up having two different people trying to build reports that are supposed to show the same kind of information however by them not selecting the right filters or selecting the right data tables or the right Fields you could end up with different results um I think one example that I gave in the realm of Dynamics nav is that basically for instance to be able to get the sales amount you should be going to the table value entry you should be know you should know that filtering on an entry type sale uh you should be looking for the sales amount actual not sales amount expected and you should be filtering on Source type customer and you should know that Source number field represents the actual customer number in that particular context um obviously not something terribly complicated but something that you know obviously takes some time of understanding the underlying tables within the Dynamics and a structure uh and then more important basically anybody that and two different people that are trying to build a report for that sales a month will have to basically make sure that they use that exact same set of information to end up with the same result also the other thing we're reporting usually is just the single Source reporting so basically reports usually are built against your Erp or against I don't know your ax data or or your access database um so but usually it's pretty much one single uh point of contact in terms of where the data is being taken from so this was a just a sort of a quick overview in terms of uh what we should consider in terms of reporting so in terms of reporting tools when the Dynamics na we have the division reporting tools we also have SQL reporting services this management reporter and then within the vision uh space we have G reports or Express or Essentials this is some of the options um however if we basically at this point now that we frame the reporting concept we can move on to basically the bi Concepts as I mentioned before normally Erp databases are optimized because obviously it's a system that is meant to uh record your data and record that help you with processing are optimized for writing data not for reading data and generally within a relationship database you will always have a tradeoff between um optimizing for writing or optimizing for reading obviously reading requires supporting structures like keys in you know and flow fields in the nvision in the nvision space whereas writing requires the least amount of supporting structures it makes it faster and reduces the amount of Deadlock and everything else so uh bi attempts to solve that attempts to solve that by basically creating two different two different sets that you will use for one for you will keep keep you the Erp system just for writing and then basically you will create a different set of data structures just for reporting this way each of them can be optimized for its intended use so this is sort of a stylized philosophical concept of what you know how does bi uh actually how what is the bi process so the starting point for us basically is the row data this is our dynamic system uh you can have a lot of tables you can have a lot of fields the connection the relationships between they'll lead to a complex data model that is pretty pretty hard to visualize and definitely quite hard to work with so the first step the first step is basically to uh go and create a separate separate SQ database data warehouse or this can be as we will see later on can be created just in memory but create a separate structure that basically just what it does it takes from the entire data simply the relevant data so basically we will take only the tables and the fields that are actually relevant in terms of reporting there are obviously in egrp system there are a lot of tables that are supporting for processing that really have absolutely no bearing in terms of uh in terms of the uh reporting and result uh we try to do a simple organizing of those things by subject within that data warehouse and also we try to clean it up and sort of make it consistent across the various the various subjects that we work with and then as a final finite result we basically create uh we basically create uh cubes using all app technology from Microsoft and in cubes basically what we do is we create dimensions and measure meur es uh and basically those will be the only two elements that we will be handling within the bi framework um the the cubes are basically optimized for Speed uh and are simply meant to be something that you read often and you write very rarely to them um basically um the dimensions represent the uh categories that you would be analyzing your datab Bas I mean Dimensions would be um customers salese geography um item item numbers and date basically whereas basically measures will be what what would you want to measure within so how would you how would you want to put together what you're analyzing so basically that would be your sales amount your cost amount your jail balances Gil budget uh quantity of item sold number of contacts made to uh to a particular Prospect or a particular customer and so on um so basically we sort of go from a a lot of data and a very complex data model to hopefully a and a very technical and a very technical scenario through this sort of scrubbing process which is called the ETL extract a process called ETL extraction transformation loading we get to a hopefully simplified model that will use only a business language that will be relevant to users um one of the things that I wanted to um highlight in terms of the advantages of using a data warehouse basically as I mentioned the process of getting the data out of the sources and putting into the dataw called ETL ex transformation and loading the other advantage of having a data warehouse and therefore different structure from yourp system is that versus the reporting it allows you to have multiple data sources so in this in this uh screenshot you can see that the Erp system is one of the things that is being brought into the data warehouse for the purposes of all app analysis reporting or data mining afterwards but you can definitely bring other things in so for instance you can bring your CRM data if you had an access an access database with some other some other sort of offline if you will uh information that you haven't brought into your RP that can be brought over as well any kind of flat files any any kind of other information sitting around that you normally sort of cut and paste into Excel spreadsheets to finalize your reports and get everything you need in there they can all be brought together within the data warehouse and therefore having one s single place from a end user perspective when they do any kind of analysis for them the reporting concept will be transparent versus having to say okay we'll run a report against this we'll run a report against this against this and against this and then put results get to get a final result so this is this is one one other advantage of the bi approach if we do create the data warehouse um so I guess to sort of summarize the uh bi Concepts uh you know business intelligence it's a translation from technical to business language so what does that mean if we were using reporting or if we were using the Erp as our sort of basis for building any kind of analysis we would have to talk most of the time in terms of tables and Fields if you hopefully if we the bi solution is done the proper way we only have two elements D dimensions and measures and the dimensions and measures we'll see further down in some examples should be framework in the business language that should be relevant to users uh business intelligence is a decision making tool so the goal of this being you know turning the information turning turning data into information that can be for intelligence so U basically you should be able to use this tool for the ability of quickly answering you know what are my top five customers what are most performing or least perform per in what are my top sales people and so on uh the goal of bi basically use the proper way is to start at a high level set of information uh basically try to figure out Trends summaries metrics and then basically try try to use that high level information to reveal decision points quickly and figure out what your next exaction is so to start with because basically the normal the normal standard of reporting within Dynamic CRP system tends to create long uh documents with a lot of data in them whereas basically here you would want to start with a high level and then based on the trends or based on specific uh uh specific points of information that are outside of the norm you would try to sort of drill down into them and try to find the details so this way you can sort of make a an action plan um one other thing that uh basically we will sort of this circles back to our discussion about uh reporting so one version of the truth um I said the business intelligence will make a transition from a technical language to a business language so basically um that gives us a um sort of a new language to use which is dimensions and measures um I sort of I sort of compare the um dimensions and measures if you will that are provided with cubes as the Lego pieces within the Lego system so basically those are the building blocks that then you can use to build whatever your users are looking for without necessarily having to interact with them but the the good point is that each building block basically has its own definition that it will be consistent regardless of the number of users that use them and regardless of what they use them for so basically uh to to I guess to use a specific example if you defining if you're defining your cost let's say to be um whatever the direct cost of the product is plus say 10% if you were doing reports you would actually have to basically in each report make sure to add that 10% and then if at some point that changes or you want to make it new on to say is being driven by the vendor you getting you V you're getting a product from which location is being stored into and many other factors that can go into the decision all of the sudden you'll have to go and re redefine all those reports and basically you'll never know that it's it's basically the the same result across all of them whereas basically if we are building our Lego building blocks with the bi solution the cost definition is what they use to build the reports whatever they use it for to Define profit to Define any kind of Matrix that are relevant to them behind the scenes it's a blackbox approach so basically if we go in just on the back end of the bi we change the definition to be instead of 10% something else with a new formula that will instantly be updated across all the reports that are using the building block without the user having to do anything and also being then this obviously would still be a transparent process to them okay um so basically this is kind of a overview of uh sort of bi Concepts versus reporting to frame it against reporting so you know uh I know I can a little bit about uh on the bi more than on the reporting but basically what I wanted to say is um reporting versus bi I mean both report NBI should be used in an organization to obtain a compl complete picture of your business um obviously you know before bi has become a more more used uh concept within the dynamic system everybody was using just reporting and obviously a lot of the information that you're looking for can be provided maybe not in an efficient manner but in one or another to a certain extent can provided with just reporting um and also bi should not be you know and thebi can obviously take over a lot of those tasks and provide a much better framework for it however bi is not meant to be a replacement for reporting so as I mentioned before when I was talking about the philosophical points about bi that you know bi is not meant to generate long strings of data so let's say if you're trying to if you're trying to build a daily invoicing report I mean or a customer listing U basically those are things that are probably still better suited for a reporting concept simply because you're just trying to provide a static list of information while some of it can be provided through bi is probably not the best use also um as we will cover later on the process remember there's a there's a delay because we're moving the data from the RP into um into a separate data structure um because it's not 100% live most of the time you know this is not meant to be for instance for bi you shouldn't be using it for an open order report you know basically an open the report the information changes you know hourly by the minute with the new system um and it's basically sort of uh transitory information anyway so um you know from a bi perspective since you're usually trying to do analytical stuff or analytical um analytical information and look over TR try to find out Trends and everything else obviously an open order report would not be something that would be best suit forbi so obviously keeping in mind what we covered I think I always recommend to my customers to basically just consider uh both options and basically figure out which fits where in terms of the needs in and for information all right so this was basically the philosophical concept of uh why you should consider uh bi and what what it's actually what are the problems that is trying attempting to solve uh the next thing we will concentrate on what is the structure of a bi solution and basically that should give you a little bit of information in terms of uh the the elements that you should consider and the things they should look at when when making a decision and actually even more important when implementing it so in terms of the Microsoft corporate IBI structure we have our Erp system and then we have our bi solution so the bi solution is basically B formed you know not necessarily officially but this these are the two components that you should you should think about there's a bi back end and the bi front end um as you can see here the RP system is Microsoft Dynamics you know ax and a GP you can have one or more companies one or more databases different versions and so on then the back end is the part that creates whether in memory or through those data warehouse and Cube structure that we mentioned they creat a data warehouse and LF Cube so this is basically the behind the scenes uh back end obviously of the solution and the front is the viewer for this for those structures is the way you interact with them so I guess the way you can think about this in terms of a Dynamics product is basically the back end is all the code everything behind the scenes all the logic business logic and everything else front end is basically let's say the r tell a client is one way you can interact or I mean basically just the web client SharePoint client and so on so there are various various ways with interacting with that business logic or in our case with the bi back end each of them with its own its own pros and cons so if you if we are breaking this out then basically we should consider you know when you consider you should realize that there's two parts to it and basically you should take it you should consider different there's different elements to consider when you choose this versus this so how to build a bi solution so bi solution is as I just showed on the other side on the other slide there's two parts to it there's the bi back end which always called the engine of the car so basically this is what makes the car runs so you know obviously without a good back back end basically it doesn't matter how nice and and goodlooking it is if we don't have a good solid foundation then the B front end is really the shiny red convertible that is basically what normal end users get to see which is basically the end result with the visualization tool put on top of it so basically usually that's what they sort of consider in terms of the end result um in terms of choosing a back end uh basically one thing you know at least in my opinion is something that should be considered there is no one siiz fitall there is no one single product out there that is going to be the right solution for every body uh basically depending on your need your budget your internal technical uh knowledge um that there are different there are different solutions that make sense for different customers uh in terms of selecting a back end I always say that to a certain extent the selection process is similar to selecting your Erp in the first place I mean basically the all the all all the backend Solutions basically will provide you with the starting point which is based usually on the standard Dynamic CRP product out of the box so through the through your Erp process you basically looked at the standard product and then after and and after that you you you do a gap analysis basically what is that and how much more is you know how much more do I need to uh do I need to put in to basically make it fit my own business needs same thing with bi I mean the bi will give different BI Solutions will give you different starting points different backend Solutions will give you different starting points to which basically that point you have to figure out you know how much more do you need uh to add to it to make it fit your needs uh so under that concept basically I think to me the number one thing they people should consider when they're looking at the backend part of the solution the depth and the breadth of the cubes delivered basically the cubes normally with Solutions cover different functional areas within uh within that that match usually the same functional are as on the Erp side cells receivables GL inventory and uh you know AR AP and so on so you know from that perspective basically you want to you want to make sure if you're using manfacturing uh in my mind you need to make sure that basically you have something that actually already covers manufacturing even if it's out of the boxing you might need to modify it afterwards um you know it definitely that definitely gives you a leg up the same way as you could easily buy na out of the box or ax out of the box and then must modify uh modify to basically make your needs there's a lot of add-ons out there in the Erp space that basically will do a lot of the things that you require without you having to do it C customization just for yourself so the the smaller that Gap is when you start better and the more likely that you will have a successful implementation and after that basically I mean I think the second thing obviously that people have to um have to consider is the cost obviously um you know comparing once once you sort of narrow down the solutions look at a cost of it which is an obvious uh which is an obvious point and then last thing um I guess depending on your internal depending whether you try you will the back end development you will consider taking it in house or not you know at that point you can also consider how to customize that solution basically some solutions use the Microsoft biack and at that point basically um you know if you have internal SQL knowledge either just general SQL or even SQL plus integration Services analysis services and all the all those tools that come along with the SQL package uh you know it may more make more sense to to stick with something that follows the standard if you don't necess have those knowledge that knowledge or basically let's say you you you would rather use something less technical there are third party tools that make the process of building the back and easier for non- technical people and um you know that could be a that could be a consideration if you're choosing to take this inhouse um in terms of the front end uh basically I think here this is there's a large availability of options um basically what what we normally me what I normally mention is basically the Microsoft tools since that's the stack that doesn't mean that basically they are the best tools out there or anything but you know basically this is kind of you know what we can you know reliably mention from the Microsoft side um in terms of using using as a front end to connect to the cubes you can use Excel 2010 or 2013 you can use SharePoint 2010 I'm not really sure if there's a new version coming out right now um these two connect to a cube and I'll make a quick demo in a second uh they interact with a cube through a pivot table interface uh in 2013 there's also another sort of component called Power view which allows for building more visually pleasing uh presentations on top of the cubes um and then basically in terms of third party tools that basically allow you to uh take the visualization concept to the to to its fullest potential as well as take it to mobile and everything else and we'll cover a little bit what's the comparison between Excel and third party the list of products here it's endless and I mean here I mean honestly whenever I work with my own customers I basically recommend that that's simply a matter of uh preference whereas sometimes on the back end I may have a recommendation or two to make to my customers here it's simply a matter of uh every customer what they prefer I mean basically because the visualization tool some visualization how easy it is to design the reports and everything else that is such a subjective concept okay so this is kind of the I think the two you know the two categories and some of the ideas that you should consider um and I guess basically as I mentioned before what what I'll do is I'll show a couple of things that are available within within the Microsoft space uh and then after that I'll come back and sort of talk a little bit on the visualization side what third parties have to offer so the next thing is look at the Excel bi features and how far they can get you uh the usually usually the reason we start with that is just simply I guess you always say that Excel is free um for you as a customer I mean normally that's not I mean it's not necessarily free but you've already purchased it the same way as the SQL tools um so the first thing that we're going to cover basically sell service bi uh this is sort of probably something that it's a good way to get started for you know basically if you want to do something on your own or at least to understand the process of building a data warehouse so sell service VI what that means it's basically usually this ends up being a um you know something that Microsoft started offering with power pivot as well as a data I think it works very well in my opinion with o data web services and they are available in navn ax I know actually o data web services are available in CRM as well uh and I'm pretty sure they probably will add it to CRM if not if they haven't already so the power pivot is a free addon that in 2010 you basically doesn't come with excel20 but you can just download it from Microsoft website and just it along with 2010 in Excel 2013 is already embedded in the product as you it um and the and for powerp but basically this is in memory bi so that means all that process of data warehouse and cubing and uh making things faster and optimized for reading it's all done in memory instead of being done into a separate data structure um and for that I will basically switch to uh my computer hopefully everything is going to go properly okay I hope everybody can uh see my screen right now um so basically the the two things that I wanted to sort of illustrate in first just a simple um first the old data con concept uh in I will be using nav but uh we can definitely we can definitely sort of it would be the same concept on the side as well um so in um in 2013 NV there's an object called query where basically what you can do you can publish to outside world through the web services or through the s data web services which is what we will be using pretty much a sort of query like query like concept uh from your NV Vision database so what I've set up to illustrate the concept of power pivot I set up a uh list of the salese so basically I have the sales people in in the in my database C their code and the name and Comm percentage uh I've also set up a list of customer information so I've taken only three different you know from the customer I've taken three different things taken the customer number customer name and its state as you can see here um and then the last thing I have a um sort of I have another published query which is basically the sales details which already has some of this information but I'll illustrate how it can be used without necessarily having all this connection so basically here it's a it's sort of an in joint between sales person customer and the sales invoice details so whenever you whenever you actually publish these things to the whenever you publish the queries to the um to the outside world they become available basically via sort of web service sort of web service interface so um to connect to power pivot basically as I said it's something that you download from the Microsoft website I think it's actually if you go power.org now it used to be Standalone website now I think it direct you back to Microsoft um so you can just go there and download this so you have power pivot uh in uh Excel 2010 in 2010 when you click this it opens up a different a different uh screen it's sort of slightly different thing from Excel but it's connected to it so what you can say here one of the option is from data feeds so this is basically the address that gets published as you can see from Na and this is my salese so I can go in here and say I want to bring in my salese and these are my three salese in the database um so let's say we're going to rename this tab to be salespeople okay then what we will do is we'll bring over the customers the customer information as you can see gets published as sort of an XML file that you can access over the web um if you and that's actually another that's actually another strength I think in my mind for data it's not simply something that you can use just for this but you can actually use it for accessing data outside outside of your database system but actually still have control over it in terms of what what actually gets published to the outside world so here this is the list of customers within my database so these are the standard demo customers with my States and everything else uh we'll call this customers or something close to that and then the last basically address is this one for the sales information so if we do this basically we can add sales information as well um and we get it all we get it on inside the Excel power pivot so these are my this is my sales information which as you can see already has all this other parts but I mean basically really the relevant part is here I have my document number line number the item number how much quantity I sold what was the total dollars and the posting date so basically what you can do within uh within power pivot is you can actually Define uh relationships between so let's see if we Define this as sales uh you can Define relationships which is you know which is at the very lowest level a similar process to building the data warehouse and the cubes in the fullblown bi so I can say that basically in my sales my salesperson code is really linked to the salesperson and the code from that table so that's a connection that I'm I'm going to make um I can create another relationship which basically says my customer uh my customer number from here is linked to the customer over here and that's another relationship I want to make so that's a definition uh further you can add columns with calculations and I mean there's a lot of there's a lot of calculations that you can do and there's actually an entire set of uh language functions called Dex um that basically allows you to do some really powerful stuff with the information that you already have but I mean you know that's not the scope of this uh Dem presentation once you have the data put together the way you you interact with it is basically by going back with a pivot table that throws you back into Excel so at this point basically as you can see here we have all the three tabs this is sort of the pivot table concept you have all the three tabs over here so obviously in theory you could have just brought over the sales information so I could have easily built my sales quantity and amount here by just my sales people right but I mean what I wanted to illustrate a point is by doing the linking between this and the other reference uh you know first of all I can actually use the salesperson from these other things so I don't necessarily have to have these other three fields in here that's one thing so now all of a sudden I can do this but also all of the sudden you've built in some other references so for instance I can sort of look at my reporting by the commission percent or for the customer for instance I can build my uh reporting by state without necessarily having the state in this particular feeld so at this point I can look at my sales by state so by adding all these links together it's actually a pretty good way to build a more advanced concept than just simply connecting to one single table um also you know in terms of keeping keeping in mind potential to this um as I mentioned to you basically CRM you can publish the data from CRM as an old data feed as well so I mean one one way I can see this besides within each Erp itself navix I can also see something where you could bring some of the data from nav and then some of the data from um uh from CRM