In this video, we will discuss a very important topic termed as tabular models. This tabular model concept belongs to SSIS. So we are able to successfully do bi, we are able to do successfully analysis by using SSH is why we are able to do this all things successfully because we are technical people. We understand, you know how to create a fact table how to create a dimension table we understand those technical concepts like star schema snowflake, we understand how to build a cube, how to publish it, and so many things. So, because we are professional developers, so, I would like to stress that word professional. So, because we are professional msbi developers, we are able to achieve this.

Now, to do good analysis. We do not really need high five technical Knowledge. Yes, we do need technical knowledge we need to build cubes, we need to understand facts and dimensions. But more than that, you know, we need to first understand the domain, we need to first understand the business. And if you think logically, we are the people who understand business much better. Definitely, No, they are not technical people like you and me, but they are in business.

We're slugging day and night inside the domain who are working inside the business who are actually handling the business. So they do have a great idea of the business and they know exactly what kind of analysis they want. And they also know how to do the analysis. If you see at this moment, you know this professional bi exercise what we have done and users really have not participated into this. So end user who is working somewhere right inside the domain. He feels that okay, you know, if you can Go and do the analysis like this you will get a better report right.

So, if the end user can also participate in creating this cube in building this, you know, whole analysis framework for us or the analysis formulas for us, that would really make our msbi process more better and more nice. And also one thing which you must have observed, when you're working in a company, you will always find you know, those one set of end users or business users who open up Excel sheets who write formulas, who do analysis, you know, they do prediction and also they have some kind of small personal bi with them. This personal bi is lying in that Excel sheet, it is having those formulas you know, which he has thought about, you know, from the domain knowledge what he has. So, at one end, we have this end users you know, who also have the capability of doing analysis. So you can turn this on The personal bi and at the other end, you know, we are the professional bi people you know, who understands dimension fact tables who understand understands, you know these SQL Server Data Tools and so on.

So, definitely if we can take this personal bi, taught personal bi formulas and put it into our professional bi that would really add more value to our professional BI. So now let us concentrate on this personal bi world. Let us try to understand that how does the end user do analysis and what kind of tools to use if you see mostly end users, you know, who are actually working on the front desk or who are actually managers or end users who are not technical most of the time and you'll find that they do analysis inside the Excel sheet. The love that Excel sheets, the last type those formula sum count, you know, put average you know, they love to learn Excel. And then implement and see the output. So you will always see that personal bi by end users are normally done inside the Excel sheet.

Now Microsoft understands the power of Excel and the love for Excel by end users. So thinking that in mind Microsoft created something called as the Power Pivot. Power Pivot is an Excel add in, or I would rather say it sits on the top of Excel and increases the capability power of Excel for analysis purpose. Power Pivot gives that power to the end user, where the end user can take data inside excel sheet and create a personal bi for himself. He can create a personal via for himself, he can create calculations, analysis formulas in his personal bi and once he's done with that, he can then publish this personal bi information into SSIS. And once this personal, bi knowledge The end user is published on the server you know from there then we can take that knowledge create a cube, create reports you know, we are technical people, we can handle that data in a better way right.

So, possible via is a way of capturing the end users analysis thought process and remember that this personal bi lies in the end users computer. So, it is not on a server while a professional bi like SSIS lies on a server from there we can distribute this information to everyone. So, a personal di you know, because the personal bi is done inside excel sheet, it lies in the hard disk of the of the end user. So, the whole point of this tabular model is to bridge this gap between the personal bi and the professional BI. So, let us go ahead and let us start Power Pivot first. And let us first start like a end user.

So what we will do is we will open Excel We will import our customer data what we have inside Excel. And let us do some analysis like an end user. And then later we will publish this analysis to the server. And then we will see how things go about it. So the first step is to get Power Pivot. So you can see that I'm searching PowerPivot in Excel.

And most of you can search Power Pivot download it, that's the best way. So the time we do that, you go to the first link of MSDN. And in the first link of MSDN, you would get the small setup, which you need to go and run on your local computer. So you can see that this is the screen. And here you can see this link, download and install Power Pivot for Excel. Once you do that, and you install Power Pivot, and when you open your Excel, you should get this small tab here called as Power Pivot.

So once you get this tab, you know that we You're up and running to use Power Pivot. So let us click on this Power Pivot tab here. And let's click on Power Pivot window. So we can see that it has opened up a new window. So this is Power Pivot. And at the back end, we have Excel running.

This is the Power Pivot window. And this is Excel. So remember that Power Pivot is an add in, it sits on the top of Excel and improves the analysis capability of Excel. Now you can if you go to the Power Pivot window here, you can see that you can import data from various places you can import data from SQL Server, you can import data from Azir. You can import data from lots of other things. And also you can import data from Excel.

So for example, let's say I have a simple data in Excel here like country and amount. Right? So India, and it has a 300. And let's say Nepal, and no 100. Right? So what I can do is I can go and convert row one columns into table by pressing Ctrl T. So Ctrl T makes a data a row and a column data as a table.

So, you can see now this has converted into a table and then I can take this table and put it into Power Pivot. So I can go and just paste this table here seeing this is the country table. So you can see now the data has come here. And you can see in PowerPivot you can see down below this tab here called country table. So in this way, I can invoke import states table I can import facts table, right? So I can import data into this into this Power Pivot from Excel from database, you know, from a lot of other sources, right.

So at this moment, I have imported data from Excel sheet. But let's do like this. You know, because our data, what we want to import is not into Excel sheet, right? We want to import data from SQL Server. So in other words, we want to import Our customer data warehouse, you know, this all data, we want to import into PowerPivot. And give it to the end user so that he can do analysis.

So at this moment our data is is not in Excel. so we can directly use Power Pivot and start importing data from here. But in case you know, some of the end users, you know, they first put data in Excel. And then they would like to take this data into Power Pivot and do things that is also most welcome, right. So let us go to Power Pivot and say that I want to import data from SQL Server for analysis. So where is your SQL Server?

So my SQL Server at this moment is running here on this machine? So I'll do a Ctrl V. And our database name is customer data warehouse. So we'll do a next and the next thing he gives you option saying that okay, choose which tables data you want to import into Power Pivot, so I'll say I want to import country I want to import product data I want to import the fact data I want to import vendor data I want to import salesperson. So I've selected you know two three dimensions and fat data and let us do a finish. So, you can see that it has started importing and the importing is done. If you see now, first thing can see that all those tables have been imported, look at the down tab, dim country dim product, dim salesperson dim vendor, fact customer, so all the tables have been imported into the Excel into PowerPivot.

I will say you know because PowerPivot uses Excel internally. But let me be very explicit by saying it is Power Pivot. So now that the data is in Power Pivot, now the end user can go and start doing analysis on it. So let's go and save this I will save this whole thing. So I'm going to go and Ctrl S and I can see that this is personal, bi My personal bi right. So, in other words, you know as an end user, I am storing this complete personal business intelligence in my machine over here and I can reopen it at any moment of time.

So, now that all the tables are at the disposal to the end users machine, but you know this data is of no use until you know you do not have a proper relationship like a star schema or snowflake, right? I do understand that end users will not understand star schema and snowflake, but the end user would like to have some kind of relationship you know, between this fact table and the dimension table right. So, in order to create relationships between these tables inside Power Pivot, you can see that there is something here called as a diagram view. So let us go and click on this diagram view here. So in this diagram view, you can see that it actually shows you all the tables for you have so You can see that there is fact customer and the fact customer is connected to the salesperson using the salesperson ID.

In the same way I would like to connect the product with the fact customer using this product ID so I can do that. So, even inside Power Pivot, you know, we need to ensure that the end user gets proper relationship so that he can do his analysis better. So using this tab here called as a diagram view, I'm trying to create the relationship. So Vendor ID I don't have your I think vendor was related to products right. So, let me go and remove this for now that is fine. So I will go and remove this DELETE FROM model.

I think the vendor table was related to some other table and then that table was related to the fact customer. So that's fine. Let us take two three tables let us not go into complications for now. So I will take DMCA country and country again has a relationship right. So there it is. Creating relationship.

So, in other words, even though you are doing things inside Excel or inside power people, still your end user would like to still have some relationship so that he can do his analysis better right. So their relationship is done. So let me now switch from diagram view to Data View. So remember, Data View diagram view, if you go to the diagram, you see your relationships. If you go to the data view, you see your data. So now that you know the end user has set up his fact table, his dimension tables, he has defined relationship.

Now you'd like to go and view this and do analysis. Also, one thing I would like to point out here, Power Pivot will not be used by simple users, they will be used by power users, power users or those users you know, who are not really programmers programmers who are not like highly technical people who can do msbi and SQL Server Data Tools, but yes, you know, they know formulas you know, they know how to go do a sum count. So, Power Pivot will be done by power user sexually. So, Henceforth, you know in this video I will say that this will be used by power users simple users are those people who know Excel you know, but they do not have that knack of doing analysis they do not know how to write formulas. So, they are okay to do daily work. But you know, they are they are simple users you know, who can perform task, but not like power users.

So, mostly Power Pivot will be done by power users. So, now, our power user has set up the dimension table or fact table he has set up the relation and also need to announce one point we need to remember the power user you know, when he goes and he sets this diagram, we do not expect him to understand that this is a fact table and this is a dimension table. So for him, you know, this is just relationships so You know, do not expect that the power user you know will really understand the terminology of OLTP of OLAP right for him you know, this is just a simple relationship. So that you know, he can get data from these tables right. So, right. So, just you know, wanted to clarify that, because when because the time we talk, we talk about personal bi, and we talk about taking this personal bi into SSIS do not use lot of technical words with the power users, you know, it will probably confuse them.

So, the our power user has set everything he has set the tables now, he wants to go and view this, and he wants to do analysis. So, for that we have something called as pivot table here. This pivot table helps you to visualize your Power Pivot data. So, let us click on the pivot table. He says that, okay, should I use the existing worksheet and show you the display or should I use a new worksheet? So I'll say okay, use a new worksheet.

You can see that the pivot table has displayed Excel pivot here. So at the right hand side, you can see these fields. And you can now go and choose those fields and see the data. Now, why has Excel chosen pivot to display data? If you think about analysis, data analysis data are mostly multi dimension. So if you remember our multi dimension cube, what we created, it has multiple dimensions and it is at the central we have the fact table, right?

So the best way to display data in Excel sheet when the data is multi dimension is by using pivot table. So I think it's a very nice thing that they use pivot table. So now what I can do is, I can go and say country name. So you can see the names have seen here, then I can say, customer amount. So there it is. So you can you can see now the end user can now go and start using this personal bi, right and start doing analysis.

Now at this moment, this factor is displayed as a summation. But if the end user wants, he can go and say that submit by count or by average, by mean and Max and a lot of other things. But now our end users would like to go and add some extra fields, you know, he wants to go and put some calculations he wants to go and write his own formulas and he wants those from formula applied field to be displayed here. So let's go back to our Power Pivot. Remember, this is the Power Pivot and the one which I was seeing here is the display right. So remember, this is the Power Pivot, this is Excel, Power Pivot uses Excel internally right.

So now let us say the end user says that okay, this is good, you know, but I want to go and add some extra fields here. Let us say that I want to add one more field over here, you know where I would, I would like to say discount. So you can see that there is an Add Column here, right? So I can go and click on this add column. In this column, so, let us go and name this column as discount. So we will say that okay, on this column, how is the discount calculated, so I'll say okay, by default, you know, you'd like to just give a flat discount calculation.

You can see that that is this column discount. So I'll say that on this discount column, I would like to go and take this data what I have here, right, and I would like to say that minus 100. So there is a discount, and apply. So once I apply, we can see that the complete discount calculation is applied here. So you can see 600 and you can see 500 for that, then there's 400 minus hundred 300. So you can see the end user can go and apply formulas on this.

Now remember that these formulas what you see over here are definitely they look like Excel, but they're applied at the column level. So basically, you know, I did not go Copy this formula across, I just selected this column, I applied it, and it was applied to the complete column. So you don't need to go and apply to a cell there is nothing called a cellular these are actually Power Pivot Tables. So do not mistake these as your normal Excel cells. So you don't have to copy the formulas and apply it to all the rows, you have to just go and apply it on the column. Now, one thing the power users should remember is that these are not Excel columns like that is customer name, customer code sales date, they are not Excel columns, they actually belong to Power Pivot Tables.

So there is a concept of tables at the back end. So very quickly, I have shown that previously as well. So if you see down below, you can see this down below. Let me just make this like this. So if you see here, down below, you can see that there's dim country dim product dim sales fact customer so you can see that these are Actually tables. So, one of the good practice you know when you're referring a column would be that you refer by the Power Pivot table name for example, this customer amount belongs to fact customer right.

So, rather than writing a customer amount like this, you should be writing fact customer amount minus 100. Right. So, with this, if you have lots of tables, Power Pivot Tables, your formulas would be understood better, right? And these formulas, you know, what you type here. On the top, they look very much seem like Excel. For example.

In Excel when you want to type a formula you will say equal to and then the formula name, for example, sum or count or whatever, right. But remember that you know, these formulas are not Excel formulas definitely PowerPivot users internally Excel, but it also adds a lot of analysis function of its own. So these formula will type here are termed as DX tax that is data analysis expression. So, do not call these formulas will type here as Excel formulas call them as DAX expression or DAX functions. So, now, let us go to the pivot table let us see that if our new calculated field what we put here discount you know is seen or not. So, the you know when you put a new field here and you put two formulas, this this field is named as calculated field.

Now, this discount field what we have here the formula of this field is applied at the row level. So, in other words you know if this is 500 you know that means the value here is 600. So, it takes this value and then applies the calculation on on that row value what what we have, so, it is more using the context, but sometimes you know you'd like to go and create a calculated field, which will apply in general for example, I would like to go and do a sum of the customer amount. So, those kinds of calculated members are termed as explicit calculated members. So, the one which you put here is implicit, and the one which I'm going to now go and add is explicit. So, to add an explicit member, a calculated member, what you can do is for example, I want to say sum of of amount and then I will say colon equal to say sum.

So, what do you want to sum out of some the customer amount of the fact customer table. So, you can see now we have this calculated member which is explicit. So, this will actually apply depending on what is the context. Let me go and see this data so, you will understand more better, what is the difference between this field and this calculated field So, let's go to the pivot table here. And let us see the difference. So, for example, over here now, let us say I go and select our explicit calculated field you can see this explicit calculated field is is marked by this icon here right.

So, if I say sum of count, so, you can see that sum of amount, you can see that it actually shows me the sum of amount, but now, if I go and say this is country name, then what it does is it takes this value of the roll label and makes a sum of that accordingly, but now, if I go and take let us say my discount you will see that this discount is getting applied at the row level. So, basically explicit Calculated Fields what they do is they apply you know across the complete data And they apply as per the relevant context. So, remember we have explicit calculated field and we have implicit. So, you can see now that I have explicit failure sum of count, I can see the amount here, I can see the product name, right and everything good. So the end user, our smart power user, or our smart end user, takes this power p vote takes all of this data, he spends Dan night and creates this wonderful analysis inside this Excel.

Now, we would like to go and take this analysis what he is doing in his desktop and publish it to SSIS because at this moment, whatever he is doing is stored into his hard disk. But it would be great you know, if this analysis what he has done, can be taken on the server in the SSIS. And then from that we can do reporting in our words we would like to convert this personal bi to a professional BI. And one of the things you know, which is very noticeable about PowerPivot is it has a lot of similarities with this SSRS project. So, when we are creating our SSIS cube by using our professional bi our Power Pivot personal bi is looking very similar for example, this diagram view over here, which allows you to go and build your model is nothing but in your SSIS project, it was actually this which actually helps us to define our fact table and dimension table right.

For example, now this calculation field you know, where we were adding which we can go and add here is nothing but your calculated your explicit calculated field here. Right and To display this data in what we have here, we're using the so if you remember, to display this data, we're using this browser tab in a way we can drag and drop and display data over here we were using pivot table for it. So basically, if you see, you know, there are a lot of similarities between personal bi and our professional bi that is SSRS project, right? And the similarities is but natural because both of them are business intelligence tools. One is for the common users, the power users, and the other one is for for professional people like us. So this personal bi what we have here, if we can take this and put it over here into this SSIS project, it is available to the whole corporate, isn't it?

So how do we go and take that personal bi and put it here? So for that, we need to go and enable tabular model in our analysis services. Remember, whenever you are creating a project so when you do a file new project for SSIS if you remember we had two options, what we get one is SSIS professional or I'll say multi dimension. So, if you remember in business intelligence, when we clicked on SSIS, we had two options, one is multi dimensional option and the other one is tabular. So, this multi dimensional is nothing but our professional bi into where we go and create a cube, where we understand fact table dimension table. So for people like you and me, and this tabular model is for for publishing such kind of Power Pivot.

So, in order to publish this format, or in order to publish this personal bi information on to the SSRS you need to have tabular model enable into your SSIS remember Our cube model the one which we did previously, which we have been doing previously is multi dimension the one which we want to publish from this personal bi onto a server, then inside SSIS we have to ensure that we have a tabular model. So, remember, tabular model will ensure that your personal bi or Power Pivot can get published while multi dimension is for cubes is for professional BI. Now, in order to upload this power of this personal bi or the project created by Power Pivot unit to ensure that tabular instances enable in your SSIS for multi dimension project, when I say multi dimension means projects which you have created by this template right that means, by this template multi dimension for those kind of projects, so, let me say professional bi you know, the the one which you see over here the one which we have which we have been doing Till now, we'll get uploaded into multi dimension instance we can see this small cube icon which we have normally seen, this is where the multi dimension projects will be uploaded.

You can see one more icon here this blue one, this is indicating tabular model. So when you are doing the installation, ensure that you create instances for tabular also or as you won't be able to upload your Power Pivot project. So, you can see you know, when I go to connect to my analysis services, either I can connect to tabular model or either I can connect to multi dimension model. So you need to have both instances enabled or else you won't be able to upload the projects properly. Right So, we have the instance here the instance is running on the server name slash SQL 2012 P. Now, we need to go and upload this personnel bi model what has been created by the end user into this instance over here. Now, this whole Power Pivot project, you know, I have saved this Power Pivot project.

So this complete Power Pivot project is saved into this file called as my personal bi, right? So what you need to do is in order to use this so I'm going to go and close this Excel sheet first. So let us go and close this Excel sheet on it to save this. So this Excel sheet, my personal bi, which is lying in my document at this moment, I'm going to go and upload it over here, right. So in order to upload the document let us go and create a project of tabular right. So either you can go and create a project of tabular and then import it or you can say you can see this one more projects in import from Power Pivot, right.

So you can see at the right hand side it says creates a table a project by extracting the meta data okay? So I'll say okay use this project. So I'll say input into SSIS you can see the next box it says that basically where is your tabular instance running? So yes, my tabular instance is running into this SQL 2012 tea right. And I will say Okay, so, the next thing it says is that Okay, can you tell me where is your Power BI Power Pivot project or personal project, so it is your with the name My personal bi you can see now, the complete PowerPivot project is imported into your SSIS project. So, if you go to your solution explorer, you can see that there is a model dot m file right.

So, you can see that there is a model dot bin file and in this this project has been imported right. And you can see that whatever was done in that Power Pivot file, all things are displayed here for example, look at the tables dim country dim product dim sales fact customer look at the formulas you can see that even the the sum of amount you know that formula has also been uploaded with the formula here. So, remember that, you know when you inside SSIS when you create a tabular project, it is nothing but it is a reflection of the personal bi project. So, in other words, whatever you have created using Power Pivot is completely senior. So you can go as a technical person and modify that in case you want and then take it ahead and deploy it. So, first the end user will go to Power Pivot and create his personal bi, you will actually import it in your project, it comes into your format that is the BIM file you can see at the right hand side this bill file is which helps you to open that personal bi project into SSRS project, you can then look at it do a review and then you can go ahead and publish this.

So let us go ahead and deploy this project. So right click and say deploy. So yes, I want to deploy the project. So once you go and deploy this complete project, it should be seen over here. So once everything is fine, over here, we should go and see our project. So you can see my project name at this moment I have kept it as import into SSIS.

And you can see that we do have an import into SSE as in installed into our tabular model instance right. Now I can go to this fact customer and I can see my data. So what I can do is I can open the new query window. So you can see that at the top, we have our multi dimension project running. Inside the tabular, we have our Tableau projects running or personal vi running. Remember, both of the instances are different because of the data structure what they have, right?

So I can now go and start firing query on my tabular project over here or the tabular model over here. Now in order to go and query tabular model, you can't use MDX or SQL you have to use something called as the DAX query. Remember, this thing what you see is some of some of the fact customer this is called as DAX formulas. So, in the same way we have something called as DAX query to query the tabular model. So, now, let us say that you want to go and see the data in this fact customer table, which is in the input into SSA as tabular models. So, for that, you need to go and write the DAX query something like this, we will have a separate session on DAX and MDX queries.

So, for now, just to get a get give you a glimpse, you have to say evaluate fact customer and then executed. So, once you do execute you can see that he is now displaying you all the records in a with the data in the fact customer. So, in order to go and get data from a tabular model, you need to go and use DAX query. So, remember you have two kinds of projects in SSIS one is multi dimension, which is professional bi and one is Tableau projects which is possible Bi or you can say Power Pivot. Now one is that you can go and import Power Pivot into your SSRS project. And the other thing is if you wish you can go and you can create a project from scratch using your Visual Studio.

So using your SQL Server Data Tools or Visual Studio, you can go and say okay, I want to create an analysis project, you can then say, okay, and yes, your SQL Server tabular instance is running here. And you can see that you'd get the same kind of screen What do you have in Excel when I say same screen means it sends the same theme. So you can see all your tabular data will come here and below you can go and write your formulas as well. So let me go and import data. So let us go and import data from SQL Server. So my SQL server is running here.

Now remember that this approach will be rarely used by your msba developers means msba. Developers, they would love to use the multi dimension approach. Because they are sound technically, they understand a lot of things, right. But you know, it is possible sometimes, you know, you want to probably create a project from Tableau from here, right. So most of the time, you won't be using this template. If you are a professional bi, most of the time, you will be allowing the end users to take the control, do that personal bi and then do an import.

But in case you know, if you wish to start from here, you can you can start from here because many times what happens is you start from here and then you give the control to your personal bi users. So both of them are possible. So you can see now I'm going here, I'll say I've selected the table. Next, select tables. So let me select To three tables from here finish. So, there you can see the data has been imported.

So, you can see that the same kind of a theme what we have in Excel is here, all the three tables are down below the media dim country fact customer, if you wish you can go and write formulas here. So, for example, you can go here and say add a column and you can write your DAX formulas here like some right in case you want to go and write explicit computed columns you can go and right over here you can say equal to and then say sum. So, basically, whatever you are doing with Excel is also available in this template and then from From here, you can go and create a tableau project. So remember, Tableau project and multi dimension projects are two different things Tableau project actually the present, personal bi, bi multi dimension project will Present professional BI. So that brings us to this end of this video.

So in this video we were trying to understand what is PowerPivot what is personal Bi? What is tabular model, and how tabular model is different different from multi dimension projects. Thank you very much