Welcome to lap 43 tabular. Now if you see the past two or three releases of SQL server that is from SQL Server 2012 you will see that the biggest change in msbi is SSIS and specifically in SSIS it is tabular. And if you see our videos at this moment in our videos, we have done a lot of videos on MDX SSIS that is multi dimension SSIS but we have done only one video on tabular and I feel personally that this is like like quite an injustice to Tableau right. So what I'll be doing is in the coming three, four videos, I will be going and recording like tablet one tablet training two tablet training three. So I will be recording like three or four videos, and each one of these videos will be like one one hour. So in other words, I'll be recording like three to four hours training only on tabular so that we can get understanding of tabular.

So this is the first video of Tableau and recording tablet training one and the goal or the agenda of this video. Is that for First I will explain you the installation of tabular then I will talk about something called as x velocity and vertie pack. Then we will look into the bsm and thought process and we will see that how does it map to the old MDX and we will also look into cube creation partitioning, KPI, translation, DAX and so on. Right. So this is a one hour of training video. And in this one hour of training video, we are going to go and cover these eight agenda points.

Now here is a humble request. This complete tabular training, whatever we are doing, right it maps to MDX. So, my humble request here is that please go and see all the CSS videos of MDX so that you can do this one to one mapping and you can understand tabular very easily. So let's get started. Now, before we deep dive into tabular, let us first try to understand how to install tableau. Now, MDX and tabular have a very, very different architecture.

Tablo does a lot of processing in memory. While MDX you know uses a lot of IO. So because you know the architecture is different because the theme is different. We cannot run MDX and tabular in a single instance, you need to actually run them on different different instances. In other words, when you do the installation, you need to run the installation twice. Once you need to go and install the server mode as tabular and give a different instance name.

And then you again need to run the SQL Server setup and say that Okay, now I will install MDX. So, you need two different instances on which both of these technologies need to run so you can see on my UI on my on my screen out here, I ran the setup twice. So when I first ran the setup, I selected SSIS MDX as my instance you know, that's where I installed the MDX instance. And you can see that the is one of the ssps you know where I've installed tableau. And you can also know you can you can also see, the icon of tabular is like this blue sign here, and the icon of MDX is this ello sign and this this one here is the RDBMS right. So, to install tabular, you need to go and run twice the instances twice the setups.

First one you will go and you will install MDX and then you will install tableau. Also, let me dedicate one or two minutes you know to talk about the history of tabular, because I feel that if you do not understand the history of tabular, you would come across a lot of vocabularies like x velocity or Deepak engine in memory, and it would be very difficult to grasp, you know why these keywords are there. So, first thing in 2012, LLC prior to 2012, there was an initiative started you can turn this as an initiative You can turn this as a group of technologies whatever it is right? But there was an initiative started and the goal of this initiative was to make SQL Server bi faster. So the to make the Analytical Engine faster to make the data warehousing faster, right. So this this initiative was termed as x velocity.

So this x velocity, there were two main things, you know, which were, which are produced, you know, the first one was, there was something called as the columnstore index. So, from SQL Server Data Warehousing perspective, you know, they created something called as columnstore index in the columnstore index, you know, rather than going and spreading data across multiple pages, you can compress them in one page. So, in case you are new to SQL Server, you can go and watch this video, you know, where I've explained what exactly is a columnstore index. So from the data warehousing perspective, they launched something called as columnstore index, which can help you to fetch the data As fast as possible, and from the analysis side, you know, they created something called as the in memory processing engine, we'll be back. So, from the SQL Server analysis side, they launched two things in memory for Power Pivot and in memory tablets.

Now, this in memory Power Pivot is for people who are using Excel for analysis. So, in Excel, you do not have a lot of data so you can take all of the data in memory and process it. But if you're talking about huge data, like how are MDX processes, then you cannot take all the data into the memory right? So you need to do something in memory you need to do something in paging and so on right? So that's where they launched something called as tabular. So, this tablet is nothing but it is an vertie pack in memory, Analytical Engine.

So in tabular, rather than processing from the hard disk, rather than making an IO into hard disk, you Take the data in memory and you try to process it in memory and when you process something in memory it is faster because you are not doing too much I O. So, what is tabular? tabular is nothing but it is an analysis services engine which processes data in memory again I will say when you have huge data it does not take all the data into in memory it It also does some paging right for example, for PowerPivot in Excel right the data is quite less right in a sheet. So, that can be taken in memory, but in case of tabular model you know where we are today where we are actually targeting professionals software, we are talking targeting big projects, enterprise projects, in this case a lot of things happen in memory, but definitely there is a limit to the ram right.

So, there is some paging also happening. So, tabular is nothing but it is two things. One is it does a lot of processing in memory. So that's why it is faster and second is it is very, very simple. Simple as compared to your multi dimension model, you know where you have to do a lot of things. tabular is very, very simple anybody can do tableau, right.

But now, with this new tabular coming in, we cannot neglect our old faithful, big army of MDX developers who have been creating cubes for years together. And these MDX developers have been taught a specific thought process. So we have taught them that yes, when you're doing modeling in MDX, please follow the star schema design or the snowflake design. When you actually query and cube, we are going to use the MDX query language. When you actually try to do data access, then you can use various ways of doing it by using m OLAP. Our OLAP edge OLAP.

So this old faithful army of developers have been taught a specific thought process. And now that we are bringing this new tablet in, we should ensure that we align this old army industrial import process or we ensure that we have a semantic mapping or that is a semantically that should be things which are same between MDX and Tableau and that's where you know Microsoft has introduced something called as bi SM business intelligence, semantic modeling. So, this bi SM is nothing but it is not a product it is not a technology, but it is a thought process, you know, where we are making the MDX people comfortable telling them that you don't have to change your thought process, the thought process is same, but for MDX you know, these are the semantics and for tabular, this is the semantics. So, this semantic modeling thought process, we can divide into three layers.

The way we are doing modeling, the way we are doing querying and the way data access happens at the back end. So, in modeling, we told to the MDX people that yes, you can use star schema snowflake if you don't use it. Your cubes are not great but in that boiler, we tell them whatever data is designing team you were following till now or techniques you were following till now, it can be used here. So here, you can just have simple tables and you can create a good cube. From the query perspective, we were using MDX. In this new tablet, we have something called syntax.

So I'll be covering DAX in the coming videos. So DAX is an easy query language. In a way you can go and query your cube created in tabular. In data access, in MDX we had m OLAP our OLAP edge OLAP here we have something called as multi pack which is the in memory processing engine or quitting engine or if you want to just query directly from the source, we have something called as direct query. So basically this bsm is nothing but it is a semantic modeling thought process, you know, where we try to align the current MDX guys in the same thought process. You know, what they have With tabular so that they can migrate to Tableau easily or they can start thinking in tabular easily.

So, what I will do now is, I will go ahead and I will create a tabular cube. And while I'm creating the tabular cube, I will always go and refer that how it was looking at MDX in MDX. So, with that, you know, you can have that semantic modeling in your mind very easily. But definitely, if you're going to go and see this video, you need to go through all the SSIS videos what we have till now, if you're not seeing all the SSIS videos, then you do not know how this mapping is happening right. So my request here is that please go through all the SSIS videos what we have till now, so that you can understand the semantic modeling easily. So let me go ahead and let me create a cube in tabular.

But while I'm creating in tabular, I will tell you that how it looks in MDX, so with that, the whole bi SM can fit into our mind. So let us start so the data base from which I want to go and create my tabular model is approximately the same database structure, which I've used for our our MDX labs. So, for our for our MDX labs, if you remember, I had created a fact customer, a dim country, right. So approximately it's the same database structure. So, you can see here, dim country dim product, dim sales dim states, dim vendor product fact customer, our time table, right. So, if you remember, you know, for the old MDX we had this database which we were using to create the cube and the same I have used here So, if you remember let me just rearrange this So, okay.

So, if you remember we had this fact customer, this fact customer had a dim country, it had this dim salesperson dim states inventor product demand are the many to many relationship in product right? And the time dimension so what I've done is I've taken the same database structure is approximately the same database structure, there can be one or two fields here and there and I will explain you why I change those names, but the same fact customer surrounded with dimensions and on this, if you remember, in those example, you know, we had created this sales dimension cube you know, wherein we had the fact sales we had dim states dim salesperson right? If you remember we had also done the many to many relationship dim vendor product right? And on this, we had gone ahead and we had processed this cube. So if you remember we had done this whole thing.

So this cube was processed and this cube we were able to see on the, on the browser, right? So, so yes. So what I'm gonna do is I'm to go and take the same database structure and on that I will go and I will create tableau. So already I have created this MDX cube out here, right. And I'm keeping it in the same project you know, so that we can differentiate, we can see that how the new tabular looks different from this MDX project, right. So, you can see here this is the facts.

This is a sales right. I have your this is the country name sales right? To execute right, so, I have this dimension project out here and I want to now go ahead and create on the same database data warehouse structure I want to go and create a tabular model right. So, let us get started. So, we have the database structure, we also have a sample cube here which is created using the multi dimension. So, you can see in the Solution Explorer, we have this sales multi dimensional cube in which we have created using MDX in case you have you have not gone through the old videos I would suggest to please go through the old videos for this exercise it is the basic prerequisite because here I will not be teaching you how to go and create an MDX cube.

Already I have done that in the previous part of the videos right. So, in the same project, I will go and now add tabulous right now, please note that might tabular model at this moment is running on this instance. So, if you remember I said that for tabular model you need an instance right. So, my multi dimensional cube is running on SSIS MDX and my tabular is running on SSIS. So, if you see this Object Explorer, you can see right over here this small blue icon out here right. So, this is where the tabular model is at this moment the instance okay installed.

Also let me go and connect the MDX as well, right. Sorry, the analysis services. So, let me go and connect to my RDBMS engine. So, remember this is my DBMS engine. This is my tabular model and this is my MDX cube, which is deployed right So all the three out here. So so the MDX you will already done the project is already done.

And we want to go ahead and create the tableau project, right. And the tableau instance we need to go and install in this instance, that is SAS. And the multi dimension we install in this SSIS slash MDX and the RDB ms at this moment is in the SSIS. Right, the RDB ms means your data warehouse, your normal SQL Server tables. So, let me go ahead and let me start. So first thing is, we need to go and add a tableau project.

So as I've said, the tableau architecture and the MDX architecture are very different. So the templates are different, the instances are different, right? So let us go ahead and create a new project and we will add it in the same solution. Okay. So you can see that this is Analysis Services, tabular. So I'll give a name here.

Sales, tabular model, right? So the first thing it will ask you is that will say that Okay, tell me, where do you want to go ahead and create this project. So remember I said in the localhost MDX that is my computer name is SSIS. So in SSIS, slash MDX it is for multi dimension and only SSIS is tabular right. So, I will be going and I will be creating the project in this workspace. So it is asking for a workspace server right.

You can see that at the top there is something called as integrated as well. So, I'll talk about both of these approaches later on. So, at this moment, I'm selecting the workspace server. Later at the end of the video, I'll tell you what is the difference between both of them right. So for now, let us go ahead with the flow and let us try to create a tabular model right. I don't want to talk about something else at this moment.

But I will talk about the differentiation at the last part of the video, right? So I'll just say okay. So there it is, it is going and it is creating the sales Tableau project. So, the first thing what you will see and which goes with the slogan of tabular is that tabular is simple. So, if you see a multi dimension project, if you remember you had so many things out here, and it was very complicated, but if you see out here, you know, there is only one file here the model dot BIM file, okay. And when you open this bin file, you will find here so this is the bin file out here.

And these are the old SSIS. So, I will just so if you see this bin file, it is just empty at this moment, there is nothing, but what I'm saying is that the, the whole goal of tabular was to purpose first thing is to simplify MDX as far as possible, right. And the second thing is to do things in memory to improve performance right remember the x velocity thing, which I discussed some minutes back, right. So, first thing is, this is multi dimensional, lots of things out here, and this is tableau, just one BIM file. Okay. Now, point number one in your project, you can just have This one the one BIM file, you cannot have multiple BIM files, so you cannot go ahead and add a new item here and you cannot add one more BIM file, and in this BIM file your whole project recites right.

So, whatever modeling you do whatever database structuring you do, whatever measures you create dimensions you create is all in this one BIM file, right? So, the first thing what we need to do is we need to go to this database right to the our data warehouse and import this structure into our SSIS project. So, if you remember, in our multi dimensional model, how we did that, in our multi dimensional model, we provided first the data source so we right click here, we provided the data source, then we provided the data view and then we created the cube right? So over here, in case of tabular everything is that on these menus out here, so you can see at the top these menus model table column, right. So first thing is I will go to model and I will say import from data source Right. So remember in tabular, it was from here add a new data source, while sorry in MDX it was add a new data source from here, while in tabular, it is at the top model, import from data source, right.

So I'll say import from data source yes that the database is SQL Server RDBMS. If you wish you can also check for other databases like Oracle, Tara data and so on. But for now, I'm using SQL Server. And my database at this moment resides in my localhost right and the database name is customer data warehouse. Next. This is the processing account we need remember when we were adding our MDX as well, we need to give a processing account name, which will help you to process your cube.

So I will give my account by which I'm logged in at this moment. And the next is that we need to go and select the tables which will participate in Making. So remember that in your database, you can have a lot of projects a lot of tables, but must be few tables on the participant to create a queue for example, in in this database, you know, I have some tables like TBL sales TBL customer says diagrams, address lookup, these are not participating in cube making, right. So, I will go and just select the dim country dim product dim salesperson, so, this screen out here helps you to go and select only those tables, which will participate in cube making. So, I will select the necessary tables and I will say finish. So, you can see now, it is going and importing the design it is going and importing the rows, and if you see in your BIM file now, so if you go and watch your BIM file, first thing down below your BIM file, so right down below your BIM file, you can see this dim product dim salesperson, so everything has come like a simplified excel sheet.

Right? So how we have an Excel sheet look and feel so We can see here it has it is so simplified right? And if you remember your MDX right in that like we used to think see things like this so I used to go and click on them stateside like I used to go and click on them salesperson, right. But over here everything is in one bin file, everything is coming like an Excel sheet. So at the down below, I can see my table names right. And at the top I can see the data as well.

Right. So dim salesperson that is the data of dim salesperson, dim states dim vendor dim and the product and my fact customer. So, remember, the main slogan here is twofold simplification and in memory right. So in case on case of MDX this data source and data source view were two steps. But if you see at this moment, when I did tabular, both of these steps are completed in one go. So with this small wizard out here when I said table, sorry model import from data source, he Not only created my connection, but he also went ahead and created created my view or the necessary tables very important right.

In our case, you know, if you remember the data source view was a separate process. So, we had to add a new data source view and select the necessary tables right, if you remember, but in this case, if you see this was done in one go, so basically this was done, this was done you know, when I said model import from data source right. Now, definitely like over here, the view what we are seeing here is like separate tables, right? You'd like to also go and see a full view with the you know, with the design with the primary key with the foreign key with the relationships, right. So for that you can see down below right over here, there are two icons one which says that this is the grid view, grid view means it is showing you the table name and the data. And this is the diagram view.

So if I click on this diagram right out here, it shows me you know the relationship you know what is provided in the data warehouse. So, you can see that that is a diagram view. So, remember, you can always switch between the table view and the diagram view right. So, if you compare again with MDX right, I need to go here I go to this data source view and see the diagram right. But over here it is just a matter of switching between the table view and diagram view. Remember, again, those words are very important semantic model bi SM right.

So, whatever I do in MDX, I should be able to do it in tabular and the semantics should not change, right. So, you can see that, as I'm parallely comparing it, you must be getting an idea that how the old MDX is looking at new tabular right, good So, we were able to import the data source we are able to see the database design view, we are also able to see the the GridView or the tables. Now, the first thing Bottom thing you will see out here is that the tabular model did not pressurise me for creating a star schema or snowflake, if you see, he is assuming that every table which are imported in this BIM file is just a table so you can see down below, he is not differentiating that this is a dimension and this is a fact. Now, because we have put our table names as fact and dimensions.

That's why I'm seeing your dim product in fact customer but as such, you know, from his point of view, he is not differentiating, that this is a dimension table or this fact table. He's just saying that this is table tablets. So, when you create the MDX design, when you actually go and say that you want to go and create a cube in MDX, right, you will see that it forces you to follow the star schema and snowflake snowflake design. So if you remember right in the previous part of the video, when we will creating a cube in MDX it actually tells you Okay, so tell me which exactly Is your fact table or the measure group table? And then it tells you Okay, tell me which exactly are your dimension table. So, basically in MDX you know, we need to design our tables using star schema or snowflake or else it becomes very difficult to create a cube.

While in case of you know, the the tabular model, it says that everything is just a table so you can see everything is just a table and you can go ahead and create measures on any one of these tables. Okay, so now if I want to go and create a measure, in case of in tabular, what I need to do is you can see here, whenever I click on a table, there is a white area right down below or a blank area down below, right so you can see when I click on dim vendor product, there is a blank area down below so when I click on dim vendor, there's a blank area down below so you can go and write DAX expressions DAX formulas in these blank area. So for example, I can go to a customer. And I can see here that I want to create a measure called as the sum of the sales amount.

So I can go and say yeah, I can just go and write equal to, and then I can say sum. And then I can say, you know, whatever is the column name, right? So we can see, you can see at the top, you know, just like how you write and how you write a formula in Excel sheet, you can see that I clicked on this and then I can say, equal to, and then I will say sum. And I can see some of the sales amount. We can also go and browse to that column. So we can see a fact sales fact customers sales amount, right?

And so if you see this first part out here, this is the formula. And this this part out here is the name of the measure. So I'll say that this is sales amount, some. So this is the name of the measure. So, we can see here, you know, creating a measure is so easy in tableau, then that you need to just go ahead and write simple DAX formulas right. And if you remember in your MDX you know, you had lot of restrictions.

Like you know, for example, if you remember in MDX right, if you want to go and create a measure, then you have to actually click on this cube out here and you can only create a measure on the fact tables. So, example you can see that I you know, whenever I say I want to create a measure, then it was only out here, so, new measure, but I cannot go and create measures on dimension tables right. So, here I did not have anything called as new measure right. But in case of, you know the the tabular model, you can write measure on any table because it does not differentiate that there is a fact table and there is a dimension table. So, for example, I can go to dim vendor, and I can also go and write a measure out here. For example, I can say okay, I want the count.

Right? I want the count of count of the vendor name. So, so I can go and write your own vendor name. Right. So you can see now I've created a measure on the vendor on the on the on the on the demand as well. So, name vendor, the vendor, the vendor dot, let's say, Vendor ID.

Okay, so you can see that I've created an account on the dim Vendor ID right. So basically, in case of tabular, you can go and put measure on on any tables because everything is table for it right. But in case of MDX, you need to only go and create a measure on the fact table. It does not allow you to create a measure. One more important thing, which I want to point here is about the measures formula flexibility means what? For example, when I go to MDX and when I say I want to add a measure, I get a drop down here and whatever formula is in this drop down, I can use it, but I cannot go and create my own formula.

So, you can see that I can use some I can use count max mean an average and something, right. But let's say if I want to go and write something customized, right, I cannot do right. But in case of tabular, you know, now, because we have the flexibility of the flexibility of the DAX formulas, we can go and literally write anything, for example, I can go to this sales amount here. So, it's not only some average, I can start with the if condition and do something, isn't it? So basically with DAX formulas now, we have more power, in what we can do more flexibility in what we can do. Right.

So at this moment I've created a measure I have the tables out here. Now, let us go ahead and process the cube. So if you remember in your old MDX you know when you want to process the cube use to hit this process button out here, but in case of tabular, you need to go to model here and you need to click on process. So, I'll say process all for now. So, process all so there it is, it goes and it processes my cube and this cube we can then see to the SQL Server Management Studio right. So there it is, it is processing in progress.

So once this processing is finished, then in my SQL Server Management Studio in the DAX instance, so wherever is our DAX instance, right, we should actually see our cube so over here, so let this processing finish first stinking time. I don't know why. So that you can see that the processing has been done. So now if I go to my SQL Server here, remember our name of this project was sales. So this was multi dimension. So the name of this cube here, just close this.

So this cube name here you Solution Explorer, slow slow. So this was multi dimension cube and this was sales tabular model. So somewhere in my tabular instance right out here, so I'll just go and do a refresh. Remember this blue icon, again, I can zoom in. So please note that this is tabular. This is MDX.

This is your RDBMS where you have your data warehouse, right. So in this, I should go and see that sales tabular so I'm going to go and click on this and we should see the sales table is created. Okay, so there it is, you can see sales cube, sales tabular. Okay, this is great. So you can see here we have a very big name here. I'll talk later on why this big name we have, let me go and delete the other sales cube Tableau so that we don't get confused because I've been doing a lot of demos of Tableau recently.

So it can just confuse us. So I'm going to just go and delete all of them so that we don't get confused. So the first thing is, you can see that the sales tabular model has been installed. Right? It has been, it is out there, but it has a very, very big name with a geo ID right. So I'll talk about the geo ID after some time.

But at this moment, you know, you can be rest assured that your sales tabular model is right out here right. Now, the way in multi dimensional we had MDX queries, in tabular we have something called as DAX. So if you want to go and write a query out here, you will say new query and you will say DAX so remember MDX form multi dimension and then for tabular we have something called as tax. Remember, we are again trying to follow the same semantic model. So, whatever is there for MDX we should also have for tabular. Now, the DAX queries the DAX formulas are a bit different.

So, for example, if you want to go and fetch some records for example, so, here is my table and let us say I want to go and fetch fact customer, so, I need to write this DAX queries as follows, evaluate, evaluate, and then whatever is my name like I'll say okay, fact customer, right. So, I can do an f5. And that does you can see that he has fetched the records, right. So the DAX queries form the structure is again different we'll have a separate session on DAX queries later, right. So for multi dimension MDX, for that for tabular DAX now The way we were able to view data in Excel in MDX So, if you remember in MDX, we need to click over here you can see my mouse. So over here, we used to click and say, display this data in Excel.

So in case of the tabular model, we have the Excel here, right? So we can go and click here and we can see this same cube in Excel. So that my Excel sheet has opened. So now I can go and say okay, show me the fact sales amount, right? Show it country wise, or show it product wise and so on. So you can see at the left hand side, we are seeing the Excel display.

Now, in multi dimension, when we actually go and see the view, for example, when we actually go and see the view out here, you can see that this view is very awesome, because it tells me that what are my kids What are my dimensions? What are my roles? You know, what are my assemblies, what what is my data source view. So it actually gives me a very nice view. But when I look at tabular, it just should be one physical beam file, which is not very interesting. And in order to go and see things like you know, whatever I have created, I have to go to each one of these menus out here, which is very tiring, isn't it?

So, for that, you know, they have given a model view of this physical BIM file. So what you can do is, you can go to all windows here, other windows, I'm sorry. And you can go and click on this tabular model Explorer. So you can see right out here, you can see this tabular model Explorer. So, if you click on this tabular model Explorer, it gives you a very nice model view, where you can go and see, okay, what are your measures at this moment, at this moment, we have only two measures. At this moment, we have the following relationships.

I'll come to relationships later on, what tables we have, what are the data sources we have, right? So basically, this model view Gives you a view of how your tabular model at this moment looks like. Right? So this is just a physical BIM file. But you know, if you want to really go and see the internal model, then you have to click on View, other windows and tabular model Explorer. In the previous lab, we talked about something called as perspectives.

So perspective means that you want to give a simplified view to an end user, isn't it? So, in that lab, you know, I showed you the demo of perspective using multi dimension. So if you remember, you know, we clicked on the cube, and to create an perspective, we clicked on this tab, and we created an perspective here, right? So in case of tabular, if you want to go and create a perspective, then you need to go and click on this top menu right out here perspectives, and say create a manager perspective. So let's say a new perspective. And I'll see This is for a very, very simple user who just wants to see the country dimension, right?

And he wants to see the fact customer and save it. And after that, you can go and you can process the cube. So remember again, to process the cube, click on model, process and see process all. So there it is, it is processed. So now we can go. So let's say we can go and we can connect using Excel.

Right? So let's go and connect using Excel. Now, over here, right? A very important thing. For example, one of the things you know, what we had here is in the multi dimension is we had this browser, and this browser was really awesome to go and browse our cube right. In case of multi dimension The, the way to browse to a cube is via this Excel.

So click on this Excel and I will say at this moment, I want to go and see from perspective of the simple user, right? And I will say okay, so because I've connected using a simple user, I should see only two dimensions we can see that I see only two. I see only this fact customer, I see this country name. And because I selected this fact customer, I'm seeing even the fact customer table, right? So at this moment, I'm not interested. But you can see I don't see the other dimensions.

Right. So remember, to create an perspective in tabular. You have to click on this model perspectives create and manage perspective. in one of the previous videos of MDX, we talked about something called as KPI key performance indicators, right. So if you remember, in multi dimension, we created an KPI by using this tab out here. So we went here, and we created an KPI.

What is an KPI a KPI rather than going into details of a value it actually gives you an overall view of the value for example, you say that okay what is my profit? So rather than saying okay the profit is 100 point 23 and 100 point 24 it says that is the profit good? Is the profit okay? Or is the profit bad? So it actually gives you two or three status and by using the two or three status you can decide that what is the current state of that value, right. So for for creating a KPI in case of tabular, you need to right click on this and you need to say that I want to create an KPI.

Okay, so I'll say I'll create an API. And now the first thing it says is that so tell me that this KPI you want to use which measure and on which you want to create this KPI so I'll say I want to go and use this measure. And I will say compare this measure with an absolute value at this moment, we can have an absolute value. So I'll say that the absolute value is, let us say 10,000. So if my sales reaches 10,000 it is good, right? So at this moment, you can see my measure is showing 4000.

Right. So this is 10,000. So now I can see that, okay, so if the value is from zero to three 2000, it is very bad. If the value is from 2000 to let's say 9000. It is very good, right? And anything about that is awesome, right?

So you can see how KPI over here, rather than going into the in in detail of the value, it is giving me three views bad, okay? Or awesome, right? So I'm going to go and press OK. Once I press OK, we can see that this small sign out here, which indicates that there is an API. Okay. So let's go and process the cube and let us see this KPI in Excel. So I'm going to go and say process all and now let us go and see this cube in Excel.

Right so Excel Okay KPI, key performance indicators. It tells you to give a very generalized view of your value, right? So especially when you're doing management reports, you know where the management does not want to get into nitty gritty, they just want to see from the top view that how is the overall thing performing right? So you can see over here I can select sales amount, and you can see that there is something called as an KPI out here so I can say, Okay, let's select sales amount, so let's select the value goal status. Right. So now you can see here so you can see it, it's saying that Okay, so 4000 is the value at this moment and you are targeting 10,000.

So this is okay, right? In case if this value was 2000 or something But in case for some reason if it was 2000, then it would have shown a red sign out here. If you see at this moment, our KPI is compared against hard coded value. In other words, it's a constant value. So if you edit the formula of this KPI I'm sorry. If you edit the KPI I edited some and edited the formula I'm so sorry, right.

So I clicked on Edit KPI settings. So, you can see that this value is hard coded, it would be great that if this value comes from some other calculation, so for example, I could do something like this I could say that okay, I want that we should do twice of the sales. So for example, look at this, I am creating one more measure. And this measure. If you look at the formula, it says twice of the sales amount right. So this we can see that this is like a target.

Okay, so you can see Now, this is our actual sum of the sales amount and this is my target I want to achieve. So, now what I can do is I can go and I can edit the KPI setting and rather than giving the absolute value so, I can go and say that this is a measure right and the absolute value and so, so, you can see okay, this is the measure value, this is the seals is the measure value and I want to go and compare not with an absolute value at this moment, I want to compare with the target. So this target is what I have now right. So there it is. So now with this, my KPI will now start comparing with this 8000 and not with the hardcoded 10,000. So again, a repeat, edit KPI setting at the top, you can see that this is the measure and this is the comparison with with which between the measure will be done right.

So I've set target out here so I'll say okay, if the target reaches, let's say, 14% only it is bad. The time Good is between 8014 to 18% it is it is okay and about 80% it is good right. So, there it is. So, now I can go and process this cube and we can see the output. So, you can compare a KPI with a hard coded value or you can go and compare KPI with a calculated value calculated value right slow slow that has So, you can see that this is my sales amount and my target is this right. And as per this target depending on what it has achieved, we can see that Okay, tell us what is the status So, at this moment the status is okay.

Right. So if it goes above 80% like around something 7000 then yes, I should see a green sign out here, right Also like in KPI, if I go and see country wise for example, look at country wise you can see that even country wise we can see the values now. So it takes this 2000 and twice offered right and then says you know what is the KPI Okay, so again, you do have the flexibility because now the value is not absolute right it is relative. So we can see like he's taking this whatever is the amount as for the dimension, and then multiplying it by two and then depending on that, it is calculating the KPI. In the previous video, we also discussed about something called as translation. So in translations, we can go ahead and display our dimension names, our fact names in a localized language like Chinese, Hindi, Nepalese or something right.

So you can see like, I have clicked on the translation tab, and then I can go and I can add those translations here. Now in case of tabular, if you want to create an translation, it is bit different So, in tabular to add in translation you have to go here and say model translations and manage translation. So, for example, now let us say I want to create Indian translation or Hindi translation. So, what I can do is, here it is Hindi, right, I will add it. Now, these translations are actually stored into JSON files. So, what they have done as you know, they have actually given an export of a template.

So, for example, you can see I've selected in the India So, I can just export it. And let's say that we save this file as translation dot JSON translate, it's a JSON file. So, h i hyphen translation, right. So, I'm saving it. So now let's go to that folder. Actually, this approach is much better if you see in the previous approach.

If I want to enter translation, I need to have This, this this whole installation of visual studio audience, I cannot add a translation here. And and that's very bad, right? Because probably I would, I would hire someone who know who can do my translations. And for him to go and buy a whole SSgt is too heavy, isn't it? So this is good, you know that I can give now this translation file to someone who's going to do the translation and it's a simple JSON file. So basically, he can just open it in a notepad so he can, I can go ahead and he can open it in Notepad and write the translation here, and then send me those translations and then I can import it.

So for this, I don't need to really buy Visual Studio, I don't need to buy licenses, you know, just you know, it's just a simple text file, right? So for over here, if you see in this JSON file, there is something if you go scroll down below there is something called scope. So in this cultures, for example, he's saying, okay, so dim country, you want to display as what so I'll say, let's say they wish, you know. And then I'll say this is dish description. So this I will say as. So you can see you're actually putting translations here.

And now what I can do is once these translations are entered, I can go back and I can see import the translations. So I can go and say, translation and I'll say input translation. So and yes, I want to override it, I want to overwrite the existing translation. So browse and see important so important successfully. So let us go ahead and try to process the cube. So I'm going to go ahead and process the cube solve Remember we need some kind of an account to process it.

Now, let us go in the Excel sheet and let us try to see this translation. So, we can see at this moment I have English and also I have Hindi, the one which I've added at this moment, so I'll just say okay. And if you see now the dimension names for example, we did translations for the dim country dimension. So, you can see this is your dish kaname and this is the dimension right. So basically, translation helps you to give localized names, you know, to your to your fact, names to your dimension names and so on. So while reporting if you want to display localized display, localized text, you can use you can use this and the good part in tabular is that they have given translation as a separate text file so I can just give it to a data entry person and he can add it or I can just import it.

While in case of multi dimension that was really problematic. I remember, you know, for one of my previous companies, you know, we had hired some data entry people, and they really needed to have the full SS DT to add these translations here. In one of our previous MDX videos, we discussed about a concept called as partitions we discussed and we also demonstrated something called as partitions. Now, partitions means, you know, basically rather than processing the whole data from the data warehouse, we create small small partitions and we only processed that So, for example, let us say that you want to only process data of 2018 right. So, what you can do is you can go and create an partition of 2018 and only process data of 2018. So, if you remember to create a partition in the SSRS cube, right, in the MDX cube, we use this part Tap out here, right?

So in case of tabular, you need to go and click on this small icon out here, you can see that I have moved my mouse partitions. So click on this partition thing. And then here you can go and create a new partition. So for example, I can go and create a new partition. And I will say that process top two rows, right. So this will go and he will process top two.

Okay, so I've created a partition. So if you see at this moment, in my partitions, I have two partitions. One is this basic one, which is everything you can see Select star from everything. And look at the second one, this is like top two, right? So let's save this model process. And then we'll see process partition.

So we click on process partition. And you can see that now that it gives me two options saying that you want to select you want to do a process of the full pot full one or you want to process only top So, if I take the top one it will actually go and just you can see that it has processed five rows right. But if you take if I take the second one So, if I take the second one he will only process two rows stop two. So we can see two rows right. So, to create a partition, look at this small icon out here, right and then to process the process partition wise, he will say process and process partition is a process all it just goes in process all okay. So that brings us to the end of this video series.

Now, it was a one hour video we have learned a lot of things over here and we have not only learned tabular but we also compared it with MDX right. So what I'll be doing is at the end of this video, I have put up a slide and in that slide, there are some questions which I will be giving. You can go through those questions and you can revise so that you can know that How much you want us to understood this topic? Now here is a small suggestion from my side or else a small request from my site. This learn msps series is really really to my near heart like, I really, whenever I record a video on msbi I just leave everything for a week and I sit for this sit for the msbi videos, you know, that's that's the passion of msbi what I have, right it's almost a 43rd video which I'm recording at this moment.

Now what I want is I want from you guys that how many of you guys are really really looking at this for the third video right? So please go to facebook.com slash questpond and please let me know that do you really enjoy this series? Because the more you tell me that you enjoy the series, the more I will record so now I need more encouragement from you guys, right? Because I don't know I've been recording videos I've been putting videos on questpond I know that some people have put the feedbacks, but I don't know like on the 43rd video is that someone who is seeing that video right? So in case if you are watching if you're hearing this voice If you're watching this video, and if you think that we should be continuing more on this series, please go to facebook.com slash correspond, and please put a post you're saying that yes, I'm following the series and I like it so that I can keep recording.

My further plan in this learn msbi series is to record more, three, two or three hours on tablet. That's my plan, right? But it will all depend on your feedbacks so that I know that people are watching this videos and I should be recording more. Thank you so much. Keep learning