SSAS Cube Design | Coconote

so today I'll start with module 6 okay so module 6 is basically talking about ssas Cube design so what is ssas skill server analysis Services is a tool which is used to develop cubes okay so what is a cube cube is a multistructural multi-dimensional is a multi-dimensional object where you use that for different analytic analytics in multi-dimensional way right so usually table is what table is a two dimensional structure but cube is a multi-dimension structure in ssas usually in the physical world cube is a threedimensional structure right but uh here we are talking about multi-dimensional structures and we call it as a cube okay so what is this multi-dimensional structure why it is called as multi-dimensional structure what is the exact difference between a table and a cube you will learn in this class okay which is very important okay so these are the list of things which we are going to cover today okay so there are lot of things here okay there are lot of things so there is no surprise that we will be left over with couple of topics okay or three or four topics that's okay so we'll we we'll take up that in the next class okay but in today's class let us understand the in and out of the basics of ssas and at least we'll make sure that you understand what is a Cube and how it is deployed how we can use the cube and how it is can be connected from a different things okay so we'll see how it goes okay so before going uh jumping anything on the ssas I will I will do something I'll tell you something on a ba semantic model so why we are all doing all these things why we are developing databases why we are doing ETL why we are S as and rs so what is this all Ed for right so at the end of the day this is all used to make sure that your clients or inducers have correct information on hand which will be which will enable them to take better decisions right this is what the overall agenda of this one right but how we can enable them to make the better decisions is using the concept of bi right okay so in the business intelligence semantic there are two things right one is a table solution and the second is a multi-dimensional solution so some you you'll have the data in the form of your table example you have a employee table or you have you have uh uh you have some product table anything right so that is a the table of format right but uh you can use the reporting actually using the table models also example employee table product table you can join them you can create a view in them or you can create a some store procedure or something you can generate a report right it's very easy but the thing the the problem if you do like that as the size of the data set grows it's very tough to give Optimal Performance example if you have a product with 1 million records and employees with 200 records and with uh a sales of uh 20 million records if you join this table either in re or SQL server or db2 any database it will stuck for some while right because this is joining for two many too many millions of rows and the user has to every query user has to wait when he's doing some analysis user has to wait for 5 minutes 10 minutes you don't know but nobody has time to wait right so what user wants is just browse something okay get a automatically that is not possible is in the table or model so in the multi-dimensional model what we are going to talk using the ssas which is the olac model those things will be very useful for the IND user and it brings the Optimal Performance okay as I was telling talking about the end users who will be using this data so so if you work for some organizations like already so you know who are the IND users right so basically some of the IND users something like just uh give me a platform I will do my analysis by myself right but some team say that okay so we have a group of users or group of managers where you want where they want to analyze your data right so just give us some solution okay so if there is one person then uh they may do the reporting or some analysis some in Excel but if there are too many persons if they want to stand on the same page if they want to analyze the same kind of data to make better decisions they need a single source of Truth which is in the form of a data Arrow so they need the data wos things in the form of some cube in ssis and they want to see how the data is coming up so in those Concepts if you if the users work on different versions of truth if they work on different versions of the data it's very confusing then and there is a conflict right so if there should not be a conflict then you should use a data or SS to QQ to give the information correctly to the users yeah okay so you you have uh yesterday I was telling you about two things okay very important let me uh okay two thing I was talking about two things right so what what are the two things I was talking about I was talking about dimensions right some of the examples of dimensions are dim customer dim product dim date dim geography dim product category right these are the example of Dimension tables right so these are some of the examples right so Dimension tables will have surrogate Keys okay diamension tables will have surrogate keys and what fact tables will have fact tables will have measures so we call it as facts right what are the measures measures are something like sales amount quantity total extended Price Right these kind of facts are called measures remember this one you'll see this particular word used many times in ssis okay what are called as measures measures are nothing but facts or numbers what are the examples of measures sales amount order quantity tax amount these are the measures which are called as facts also Dimension tables will have surrogate keys in the form of a key which is also like a primary key right if you combine Dimension tables okay if you combine Dimension tables with fat tables then you can get something as a cube okay if you combine d mention table with a fact table you get something as a cube so how you will get that all those things we'll see it later okay so this is the concept of cube so yesterday I was telling you about the uh some of the examples of the cube right some of the examples of the fact table so if you see this right uh if you see this what happens if you this particular thing explains you about a customer different customers in the different regions in different what are their sales amount okay what are their sales amount so the data is displayed like this in the Excel spreadsheet but when it comes to our data erors it need not be like this right this all this customer R and customer name will go to dim customer okay this particular will go to dim customer and this will go to dim geography region will go to dimy and these are what April May these are months right months and years this will go to dim date okay and what and these are the sales amount right this will go to a fact table right so this is a set of data this set of data will be converted into different things in our data Aros right so so this is uh some something which you learn uh in this class right so this I I hope you you understand uh uh how the dimension table are fact tables are okay so uh okay we have something right we have something to start so before starting anything I want to make sure that you guys are comfortable with some of the things here right so any questions uh just put it in the chat window if I see it as important to be answer as possible then I'll answer it otherwise Lo right uh I'll create a project for create a project for ssas right so what I have to do is I have to right click and run as administrator in my SQL Server data tools click yes yeah so here is as the same way when we were doing for the SS ssis project we used to create a new project right so in the same way we can have uh a project created for SS SS uh as right so in the business intelligence uh you have three sections previously we used to create integration Services project now we should create analysis Services project okay we should create analysis Services project so it is asking for M PA but what I want to do is I just wanted to change the path to edor right so what I'll do is like okay I I'll I'll give the name as this one yeah it says the select folder it says that it does not exist right so what I'll do is like I'll create this okay I'll create this folder okay so this is my uh path and what what I'll do is like I I'll give my name as first VI so the name of the uh project is the first Cube and this is my path and what I have to do is I have to select analysis Services here and there are different sections which you need not worry at at this particular time okay so the most important thing is analytic analysis Services multi-dimensional okay the most important thing here is analysis Services project right so here you have if I expand this if I expand you to make you clear picture on this right so there are different things here analysis Services multi-dimensional data maning project okay and import server from import from server so if you want to import something from the server analis which say table table table project okay and import from Power keyboard and import from server so these things right so these things are the additional fun functionalities available in the ssas okay additional things which is available in the ssas but the very important thing which constitutes 90% of uh ssas is this portion something like ssis we have so you have to select this particular thing and click okay then it will create something as analysis Services uh project okay so once you create this analysis Services prodject project this is also called as multi-dimensional development project okay okay so this is called as multi-dimensional development project and also you can have some uh development about the data mining but very few organizations use the data mining okay here we will talk about uh multi-dimensional analysis and we'll talk about the cubes which are the core concept of s say yes okay so if you if you come here like uh there are different sections here so make sure you understand all these things here so there's a big section here uh which is related to different parts of ssas so in the same way you have uh first of all there is a concept called Data sources so what it talks is like it says that what is your data source just tell me that so you can have some data source views it is something like having some views in the SQL server or Oracle right on on top of the base tables and we talk about the cubes okay so these are very important things okay and we will talk about the dimensions also four things we talk about the dimensions and uh in the future I I'll tell you something on roles okay and the remaining things is uh not required for this particular class right because this talks about uh Mining and all those things which is another topic okay so we'll concentrate on data sources data source views and developing cubes right and we'll talk about the dimensions and the different things okay to start with right uh to start with what I I just wanted to tell you something so I have a database called okay I have a database called SQL Server 2000 I have a server for SQL Server 2008 R2 and I have a database called Adventure works gwx right Adventure work gws so what are the tables I have in this one I have some few tables called which is related to the dimensional table and fact table okay so how you can get this is actually there's a lot of stuff available in the Microsoft uh website you can download adventure worlds DW 2008 R2 or Adventure worlds DW 2015 right where you have the similar kind of tables okay so the reason I have it in a different database is when I was giving the demo uh in one of the sessions so I I created a adventure Works gwx which have which which will store my Dimension tables and F tables okay so this is the database adventure of zwx where I have my Dimension tables and fat tables okay okay so if someone is not clear about SQL Server if you want to generate a ER diagram if you want if you want to generate a databas diagram to see what is the linkage between these things you have a section called database diagrams okay if you expand this if you already have a database uh diagram it will show show it up if you don't have it will ask whether you want to create it okay just create it so this is just to make sure to understand what is your ER diagram and or or a database diagram how the connectivity is taking place so you can actually create it from here okay okay or you can do it as a new database diagram so let us wait for it to come back right so what you can do is this make sure that you understand the relationship I select everything this start yeah so what it will do is it will they will create a database diagram based on your based on your tables right but what happens is like if you see this so there is no linkage between the different things okay there is no linkage between the different things so now you can use this particular tool to uh link this up okay but uh if you want to understand this structure clearly so you based on this key you can use some tools called Visio Microsoft Visio to develop ER diagram for this okay so this is where you can create your diagram and all but the same thing I'll show you it in SQL analysis Services how the uh things are linked up and how the things are uh how the things are connected to each other yeah so what I'll do is I'll go back so just make sure that we'll be using this particular database right we which where we have the dimension tables and fact tables okay so I'll go back here what I'll do is I'll create my data source I'll create my data source so when when I create my data source so there are some few visards okay so whatever you are seeing in the pp right in the pp you may see you may be seeing the same steps okay but the same steps we are covering it here so what we'll do is like when when you create a data source it says that okay I'm ready to develop a cube but tell me what are your data sources okay it is saying that I'm ready to develop a cube but tell me what are your data source okay I'll tell you what is it so what is our data source our data source is 2008 server and this is the database name right okay so what I'll do so it is saying that already I have a connection for my 20 server so if you don't have a connection click new click new and you can give the server name here right and if you have Windows authentication just connect with it or then you can just select the database name and test connection test connection succeeded right you can do like this but uh already my database I have a connection if you have existing connection you can use this EX create is same as ssis click next okay so here there is a very important section something called as impersonation information okay which is called as impersonation information what I do is initially I'll tell you what is this concept I'll click here first of all as different one of the sections called uh example I click this as use the service account okay click next but I'll tell you what is that right so what is my data source data source is talking about uh internet sales right okay sales Source yeah so I'll put the name as this one okay here we'll come back as I said I'll come back on in person information right so what I'll do is I'll double click so what we are doing here is we are telling this is my server this is my database name and these are the uh list of tables I'm going to access in this one right if I double click that again I have the same thing here and I have a section called impersonation information okay so here what is my thing my connection is pointing to 2008 server but database is not selected here okay so I want to select a database called Adventure ver dwx where my Dimension tables and fact tables are stored okay and I want to use the windows authentication okay I want to test the connection test connection succeeded click okay and you done with this you done with connecting to a 2008 R2 database which which is having Dimension tables and fact tables okay and there is one other important section and where very important interview question as well okay very important section which is called as impersonation information okay so what is this impersonation information okay this in impersonation information means you are telling ssas engine you're telling ssas engine how to connect to my data source okay so what is my data source my data source is available in 2008 R2 database in the adventure Works dwx database you telling ssas engine how to connect to your Source because this particular SS the whole stuff whatever you open you opened in SQL 2014 version right but your data source is 2008 version you're telling ssas how you will connect from this 2014 of ssas engine to your Source system your Source system may be a different database also right so this information impersonation information is talking about that okay it is talking about do you want ssas to connect your data source using a Windows username and password do you want to do this uh okay if you want to do this you can do this okay or do you want to use a service account it means there will be some service account right for every ssas okay if you go to control panel okay so let me uh remove normal mode okay if you go to all programs uh SQL Server 2014 configuration tools SQL Server 2014 configuration manager if you open this it will open the configuration manager which you already opened here right and it will have the list of things with the services which are running in the 2014 so there is a service for SQL Server analysis Services 2014 the highlighted portion right so if I double click this one if I double click this one okay so this is using a service account called This is using a service account called n service and some msol app 2014 okay this is called a service account okay so in the production system or development system you have a same service account like this so it is asking whether you want to connect using the service account okay so if you if you select this option it means that this service account should have a access to your Source database also you have to make sure that yeah or you want to use the current credentials of the user so person who is running this particular ssas do you want to use the current credentials okay or do you want to inherit what is the meaning of inherit do you want to inherit whatever you used in The Source whatever you used on the general tab you give some information right do you want to inherit the same kind of informations okay so this is in personation information the main thing primary thing of it is to make sure how your ssas engine will connect to your data source so this can be any number of types depends on Project requirement and your security model okay so we will see we we'll select some of the impersonation information as a start and we'll see how it goes okay here what I want to do is I want to use imperson information called service account okay just select okay so we'll we come back there so we'll come back and see if there are any problems okay so my data source is created so what is my next step next step is to create data Source views right so what is this data source views you may be having several tables in your database I may be I have around 20 tables here but I don't want to create a cube using all the 20 tables I want to create a cube using only some of these tables okay so that's the reason why I want to create a view which is pointing to my base tables and which I can use in the cube also it is something like a view in SQL server or orle or db2 right so new data source view click next so what is the source you want to point to to create a view already I view already I have a thing called this particular Source right so the same Source it is showing up here do you want to use the same source to create your data source right yes I want to use that right so I want to use the same thing what I'll do I just select that click next okay next one is no foreign keys were found you can create logical relationship on matching columns okay so what happened is what happened is when we tried to create a database diagram here right it was not linking up because there was no primary key for there are primary keys but there were no foreign Keys that's why the tables were not linked up but what SS is saying that I don't see any relation ship between a dimension table dimension table and a fact table in your data source do you want to create a logical relationship within ssas yes I want it to be done okay so I want I can create logical relationship by matching columns what is a matching column if there is a uh product key in diamond dim product the same product key will be in Factor internet sales right using the matching columns actually it can create The Logical relationship ssis okay so what is your criteria for foreign key matches same same name as primary key okay so it may be something like that right may name product key in product and product key in pable is it like that or same name as destination table no it's not like that destination table plus primary ke so what is your key here so I have customer ID in orders table and customer ID another table so I'm I'm going to match using the name of the column right and click next okay here it is asking what are the different tables you want to use what are the different tables you want to use for developing for developing a cube so you have to select some list of tabl based on that it will create a view okay here I don't want to select everything okay I want to select very few things right what I want to select I want to select dim customer okay I want to select dim geography and I want to select dim product dim product category dim sub product category okay and I want to select a table called dim time okay which are all Dimension tables and I want to select a table called one fact table I don't want to select all the fact tables I want to select one fact table called fact internet s okay so these are the tables I am selecting one is dim customer and dim Geography okay and I have a dim product product category sub product which are related to the product and I have a Time Dimension and I have fact internet sales okay so what is the the name of the data source view you are giving I'm giving it as data source view as sales uh okay I'll leave it as the name of sales tables Okay click finish what happened because we have selected based on the primary key of the column name make please please use the foreign key and create a logical relationship right that's what we selected in the cube in the VIS right so that's why using the primary key what it did is it has automatically Linked UP it has automatically linked up using the it has automatically linked up using the uh primary key primary key and its name right so if you see here if you see here this particular product key in dim product this particular product key in dim product table and here also you have a product key because the names are same it has linked up using the product key column okay so again you can see that it is linked to dim customer right there is a customer key and you have a customer key here so that's why it is linked up with these two things okay but this fact table is not linked to any other things what is the reason for that because it did not find the matching name okay but the matching name it has found out in di product table and dim sub product category here you have thing called Sim subcategory product subcategory key right and you have a product subcategory key here also that's why these two tables are linked and then it links to product category you have a product category key here and you have a product category here so that's why the linking is done based on this one so what is happening here in the same way you have a geography key here and you have a geography key here So based on the column names based on the column names actually it has designed your logical relationship okay so here if you see what kind of schema is this what kind of schema is this this is a snowflake schema right because not all Dimension tables are connected to a fact table right fact table has only connected to only these two tables right only these two tables right but these tables are normalized dim product table is normalized into dim sub product category and dim product category and dim customer is normalized into gym geography right so because the the reason it is normalized this is called as a snowflake schema okay this is not a star schema okay but you will see one particular dim time which is not at all linked to any of the tables white is not linked okay so if you see what is it time key this is a Time key right but you don't have any time the name with the time key here okay because the names are different that's why did not match it okay but if you go to some columns called due date key or ship date key right or order date key all these keys are also talking about a date which is also a Time key which is internally mapped to this one because the names are different here and here that's why it did not match okay what you can do is uh if there is no matching automatically using the name there is a option in ssas to create a new relationship okay select here select here right click go to new relationship you can always have a new relationship here okay what is your Source foreign key what is your Source foreign key table and destination primary key table so my foreign key table is fact internet internet sales what is my destination table primary key table right my primary key table is dim time right I can s dim time so what are what are your Source columns which can be mapped to the this one so my source column this product key oh sorry uh order date key which can be mapped to time key okay once I map map it to the time key I cannot see it again right once I map it to the time key I cannot see it to again right so I'll put as order key my order date key is nothing but time key so because these two are tip to together click okay now it is a there is a linking right now there is a linking correct so time key is matched to this one okay so in the fact table if you see fact table is a combination of all the surrogate Keys product key order date key customer key which is connected to this one and it will have another something as facts on the bottom of this particular table right and the dimension table will be only having the details about the dimension okay Okay click save what I'm doing is I'm doing doing control s in my particular uh laptop so what what you can do is you can go to file and save right so now this is saved right now this is saved so what you have done here you have done only the what you have done you have done only the relationship or the linkage between the dimension table and fact tables in the data source view right you have done the relationship in the data source view okay so once you are done with the data source view you can close this okay you can close this and come to a section called cubes click new Cube Okay click new Cube so now using the dimension tables and fact tables we are going to design a cube okay so I'll click next okay here there are some few options okay here there are some few few options so the few options are something like this do you want to use existing tables when creating a cube do you have already the existing tables usually 99% of the times you will have existing tables if you are working for a company unless you are end user who want to create very Dynamic things you will have this have option selected but you 99% of the thing you select I have existing tables yes right so or it can say the generate table generate tables in the data source do you want to generate the new tables in the data source I don't want to do that right I will say that I already have the existing tables which are in the form of Dimension tables fact tables which I use the ETL ssis ETL to load those Dimension tables and fact tables and it is located in data ER I want to use that right so I'll tell that use existing tables okay now very important section okay here in the cube visard it will ask you to select what are your measure group tables what are your measure group tables so what is called as a measure measure is nothing but a fact or number okay in the database terminology you will call it as a fact or you call it as some number okay but when you come to the dimensional modeling Concept in the dimension modeling concept it is called as measures facts are called as measures and fact tables are called as measure groups okay facts are called as measures fact tables are called as measure groups okay so it is asking you to select what are your measure groups asking you to select what are your measure groups okay so my what are my measure groups here I have only one fact table so this is my only measure group I don't have anything else right this is my only measure group so what I should do is I should not select everything here right I should select only my major group right what is my major group this is my measure group right so don't select everything because your measure groups are nothing but your fact tables click next okay once measure group or fact table is selected it will ask you to select what are your measures what are your measur measures are nothing but your facts right measures are nothing but your facts here you have to identify what are your measures okay so I'll tell you here fact table is combination of so fact table is connected from different dimension tables using the surrogate key and it will have the surrogate keys from the different dimension tables and it will also have the facts right so this all these Keys whatever you're seeing here all these Keys due date key ship key these are the referencing fields from Dimension table see these are not your measures right these are not your measures so just don't select this so what I'll do here here I will select Only The Columns which I feel it as a measure which feel it as a fact right this is my linking column I will remove this this is link column remove this remove this remove this just remove this I don't want this number order quantity is my measure yes this is a number unit price is a measure this is a measure this is a measure measure measure measure sales on is my important measure okay tax amount is my measure Freight is my measure okay but there is one new column which came up here okay okay this is a new column which I don't have in the data source right which I don't have in the data source this is automatically created by ssas engine okay is automatically created by data engine okay [Music] okay okay so just select this just remove this and just select the remaining things and click next and what are your Dimensions my Dimension tables are this one right so what I want to do is I want to automatically select all these Dimension tables okay I don't this is my fact table just remove this and click next right so what is your Cube name say I I I will put the cube name as sales Cube click finish now a cube is created we will see how this cube is created okay so this is the cube what we have created okay so we had the data source as say sales data source and we have created some data source view which is some combination of some Dimension tables and fact tables and then what we did is like we created a cube while creating a cube we have taken couple of important steps we have selected whatever what are our Dimension tables and what are our fact tables so Dimension tables are called as we have selected as Dimension attributes and for the fact tables we have selected as measure groups okay so once it is complet so you can see the particular screen like this right okay so you can see the screen like this which is generated by ssis automatically so what is happening here so we have some few tables which are connected right so how this is how these are connected so we have a table called Product but because that is being normalized so it is divided into three tables right and which is making into the fall of snowflake schema right and we have some couple of tables in customer and dim link to each other right and we have another table called dim time okay and these three tables dim time dim customer and diing product or having the surrogate Keys which you are pointing to fact internet sales okay so the overall schema is called as a snowflake schema okay so here if You observe this particular section there are lot of things which are new for you and you're seeing for the first time if you're learning ssis for the first time right all the things we had in ssis are completely different than this right so on the left section here you have something called as measures okay so whatever the measures you have selected in the quizard so all these measures are present here and there is nothing present which is not selected right these are called measures and in the bottom section you have something called as dimension ions okay these are called as Dimensions so in the dimensions what is happening you have selected a dimension table called Product customer and dim time okay and you have selected selected it corate Keys based on the normalization what it did is it has intelligently created something here so if You observe it carefully we have selected three tables right we selected three tables called dim product Sim product category and dim product category but what it did is it created only one parent table in the visard we have selected three tables but what it did is it has created only one dm product but it made sure because it is linked up each other it made sure that it has a product category key product key and product subcategory key okay although in the database side it is normalized into three different groups okay although on the database side it is normalized into three different different tables okay but coming into the cube section it has created only one table but it made sure that the keys are intact okay so this is the beauty of ssas so it can intelligently identify and it can Ro the things right and in the same way although on the database side this is normalized into two different things but it has created two different keys in between the DI customer which is a parent object right and you have something called as dim time where you have time key as the surrogate key right so these are the dimension tables and tables which we have created and in s in the ssas you can see them as two important groups right what are those two important groups one is called as dimensions and second is called as facts which are called as measures okay these two things will form a cube Okay so it will form what are the things about it we'll see it right at the same time when you create a cube there are lot of new sections here which which is on the top ribbon okay on the top ribbon there are lot of new sections so what we'll do is we'll go we'll make sure that we will cover all these sections right we'll cover all these sections and see what are the uses of different sections okay so in ssas it has some beautiful functionalities which are not present in other olac tools okay in terms of design or in terms of functionality okay so we'll see what are those things yeah so I hope you you have the concept of this Cube now right you have the concept of this Cube now so what I did is I just try I'm trying to save save this it is saying that the following objects will also be saved would you like to proceed yes I want to proceed right so just save it okay so so so that the whole project is saved okay so what happen is like this is a sales Cube and this is a data source and this is a data source view data source data source View and this is the cube right okay so very important here uh what we'll do is I'll try to do some things here what I'll try to do is I'll try to deploy the cube okay now the cube is created okay cube is created but I'll try to deploy the cube and there is a concept of process the cube okay and we will say face some issues while deploying and I'll tell you why those issues are coming up okay and we will fix the problem okay right and after that we'll have one voiceover session where you can ask the questions on voice and we'll talk on that sure and anything also pinned on the chat I'll make sure that I I'll answer that once these things are done okay so this is something on the uh Cube so to deploy the cube what should I do is to deploy the cube I should go to so project level this is my project level my first cube right so go to properties okay go to properties so what you what you do is like deployment server Edition like there a Developer Edition or enter presentation so it's okay so my thing is a Developer Edition so uh that's fine I leave it as So based on the Edition you just select your type of Edition so how you can know the type of Edition you have you can go to your uh uh you can go to yourl server 2014 and select at that uh version okay so if you execute this like you'll be knowing what type of Edition you'll have so my thing is uh Enterprise evaluation Edition okay so to know the version just come here right so what you can do is like it's okay so I can deployment server Edition I can uh I just put it Developer Edition we'll see what happens right and deployment server version 12.0 output been through so remove passwords equal to uh I just put it as true so let's see what are the things happens right and just debugging so just leave it as it is and come to the deployment section okay in the deployment section initially don't don't touch anything okay what you do is you specify your server name where you want to deploy you specify your server name where you want to deploy the ssas okay so what I want to deploy this ssas in SQL Server 2014 of analysis Services I want to deploy this in SQL Server 2014 analysis Services right so what is my server name my server name is this one okay and how can I say it is a 2014 version using the instance name okay and what is your database name so here there is a concept called database so which will be same as your project name okay or you can change it okay just try to keep this properties click okay and click okay save it okay and go to build okay go to build and make sure that you build the cube now it is saying build started and build succeeded if you see the bottom section right there are no errors okay so once you build the cube there are two important topics okay one is deploy and second one is process okay so this cube is generated if you want to deploy this Cube already you have set the properties right you have set the properties where it is to be deployed okay so let us try like what happens if you deploy I say deploy okay we got some few errors okay let us see what what are those errors if you see this errors what it is saying is login failed for the user ENT Service msol app this one what it saying it is saying that heyy your login is failed and something is not working okay so why this login is failed when we have created our data source okay I told you there are two one two important sections right one is General how you want to connect to your Source database using some authentication right so that you can develop it okay but when the ssas is running it when the ssas is running it okay when SS is running it how you want to connect your ssas engine to your data source how you want ssas to connect to your data source okay so what is happening is this ssas is running using a service account called n service msol lab SQL 2014 so where where you can see the service account you can see the service account in configuration manager is the same service account it is using as log on as but this service account is not a login in 2008 server so this is my 2008 server this is my 2008 server if you go here in the security tab there are list of sections so I don't have MSO app 2014 as a login here okay that's the reason why it is not able to connect okay so if you have a login for the to app uh network service here then it will be able to connect to the so you have to make sure that whatever the service account or whatever the property you give in the imperson impersonation section it should make sure that it should it can connect using those IDs using those credentials to data source so that is called as impation so I use service account it says that uh no it is not available so you can maybe you can try with use a specific Windows username and password so what is my windows username password my windows username this one is the username and uh this one is the password okay so this is why I I just keep my windows username and password right and just saved it okay we'll try to do it again deploy okay so what is happening so it is trying to deploy the things it is trying to deploy the things things and it says that deployment completed successfully now why it has completed successfully because in my 2008 server which is my data source I have my windows user ID I have my windows user ID as a login right I have my windows user ID login that's why the impersonation using my windows credentials what given in impersonation information it can create a connection to my source server that is the reason why it is able to connect and deploy the things to SQL Server 2014 okay there is a catch right just give a second uh okay so I got you I I hope you got the right so it is going to Source 2 year server and it is making sure that it can connect to it but it is saying that it is deployed completed successfully but where it has deployed this it has deployed in a separate place right so that place is SQL Server 2014 so where we have given the deployment properties it has deployed to this one 2 14 server and it has created a database called first cube in this 2014 server let's go to the properties and in the deployment we have given a deploy to 2014 server but by source is 2008 connect to my source using my windows credentials but deploy here okay I hope you got the point right right so what will happen is I should go to my 2014 analysis services and see if it is deployed properly right so how can I go to 2014 analysis services so there is a so what you have to do is to go to 2014 analysis Services first of all make sure that in your configuration manager there is a section for SQL Server analysis Services 2014 make sure that this service is running okay if this service is not running try to start it okay now currently my service is running so it means that my analy service is up and running right so so what you can do is you can open this 2014 management Studio using runess administrator or some user ID password where you have the permission right and this will be open right once you open this there is option for connect and here you have to connect to analysis Services okay here you have to connect to analysis Services 2014 and click connect right so what is happening once I click 24 anal Services now I have a different thing here so the top section is for SQL Server 24 database right but this is for analysis Services okay and in our project it is saying that it has deployed to your database called first Cube it means that I should have something in the database section here with some name as first Cube yes I have so it means that it is deployed successfully right so just open this go data sources this is our sales data source is same as this one and this is our data source view same as this one and this is our Cube which is same as this one and in the cube we have measure groups and we have Dimensions yeah so this is deployed successfully okay it means that cube is deployed success what I'll do is I'll just close this okay I'll just close this I'll remove this my cube is deployed successfully but once my cube is deployed successfully it doesn't mean that I straight away I can use it okay once the cube is deployed successfully we have to make sure that that is processed what is process what are the types of it we'll see it later but on High level I'll try to process it what is the meaning of process okay this is very important okay you have different dimension tables dim time dim customer dim product and you have a fact table what it will do all the Sate keys are connected to the fact table to the centralized fact table and it has a different carugate Keys as a relationship to the dimension tables and it has the facts in it now we have designed a cube in the same manner but ssas should internally create some aggregations important word ssas should create some aggregations so that it has all the combinations of this data so what are these combinations so this will store some aggregation example this C particular customer in this month with this product this is the Sal total sales amount right now I'm talking about total sales amount sometimes I want average of sales amount sometimes I want the minimum of sales amount I want maximum of s sales amount right so my aggregation can be anything I I I may require total sales amount minimum sales amount maximum sales amount right so my my average sales amount or I may sometimes you can count of number of uh Records this is also another type of aggregation right so my my aggregation can be anything right so all these aggregation ssas will create internally using the engine when you process the cube okay you you'll understand about this topic more in the future classes right this is about something about the process for processing what you have to do is right click okay go here and show process what it is saying the server content appears to be of date right would you like to build and deploy first project first it is saying that it saw some few changes like after we have deployed it is you want to yes I want to do it just build it succeed it and try to deploy it right and uh now the deployment is completed right if you see this deploy deployment is completed successfully the message on the bottom section right and automatically I have already the thing if I refresh this uh this right I have a cube called my sales cube right so but the new window has come up which is a process okay so there are different options for the process so don't change anything for now what are the processing options I'll tell you more on that okay but just expand this window so that you can see everything and what do you want to process when you process it calculates all the aggregation that stores in the m Dimension Cube okay it says that proc the information is somewhere and this is this is your processing information right click close click close it means that it has done all the aggregations and it has kept inside right now it is completed just this right the QB is also process okay now if I go right if I still refresh it I I'll still have all this the process it means my cube is ready to use now okay my cube is ready to use okay so what I can do here to use the cube to use the cube make sure that there is another service up and run also one of the important question okay go to the configuration managers there is a SQL server browser okay there is a service called server browser make sure that this is up and running if this is not up and running so that you not browse the cube using the S okay so make sure this is up running right so what I do is to browse the I have a particular called browser okay I have a called browser here I'll go to the browser section I'll go to a browser section in the browser section I can see my sales Cube okay I can see my sales Cube which is the name of the cube I can see my measures which is are my facts right I can see my Dimension tables I can see my Dimension tables right and automatically I can start using the cube right automatically I can start using the so how can I use the cube I'll show you simple scenario just save it okay right so on the top of the cube one important section you have one thing called Dimension hierarchy operator filter expression okay so what type of Dimension you want to select so I have a question from my manager okay so my my my my requirement from one my users or sales manager is okay for every customer for every product in the month of some January show me what are the total sales figures this is the query he asked for every customer so I should go to the customer key right okay so what what he asked is just show me for every customer what are the different things present right what I can do is I can just take the customer key and drag and drop here it says no row found that's okay and what I can do is for every product means I can drag and drop product key okay he says that I want in the month of something right I want in the month of something but if you see I have only the thing called key right I don't have anything else so this is bit confusing here so I cannot know what is my month name and all those things right I cannot know month name and all those things so what I'll do is like okay for for default for to explain you I I'll just drag and drop something right so what I can do is I can drag and drop the time key right so what what he wants is he wants the sales amount right just go here just go here what is the sales amount now it is executing the results and it is giving the results for this customer for this product for this time frame this is the sales amount but what are these values these are the surate keys will the end user will understand the surate key no right so you should give me the customer name give me the product name give me the time then I will make sure that I can understand this one it is giving the results properly and in the future he may ask that okay I don't want the time frame just for customer and product just give me the total sales am just remove this and it is executing it is changing the numbers okay so this is how you can deploy and process Cube and use it okay but one important piece of information what we are missing here is we are having the keys only only Keys we don't have any uh attributes to it right you don't have an attribute state so that the meaning the information whatever you're reading here it is not giving you the clear information right so that's why so as this is the first Cube so we will we will stop it here and see how we can fix this problem in the while creating the next cubes right so this is the very high level concept of the cube and you how you can use it example you want to use some other cont okay I this is the total sales amount right what is the total sales amount uh what the what are the total number of products uh ordered right this which is nothing but ordered quantity you say someone this is order quantity so you can just drag and drop whatever you want and it will automatically shown here yeah so this is how you can link up but the thing is that you can you're not getting the correct information from the dimension because we we have done we have not done some of the things which we will do in the later parts of the cube okay so this is something about the overall thing about the cube and we will see more things about the cube in the in the next few minutes or in the next class also so this is something right so what we can do is like we did not we can make some modifications to this particular thing and make sure that we have the new things coming up right so I'll go back to the slides okay I'll go back to the slides and see what we can go forward from that right so what we did is we did a cube called one of the cube development and what we did is like we have selected one of the uh data source when we have selected the impersonation information and we have uh used A View visard to get the view and we have we have something ready so this is maybe bigger picture right but what we had is like we have something right we have what we did is like we did a cube right and what we did is like we made sure that we have all the measure group selected and we click next and we made sure that we all the dimensions ready right and uh this is something it uh looked like right okay okay so one one important thing what we missed out here is is uh uh we saw only the keys available on the browser right so but uh actually we can have some of the information present in that okay so we when when I saw the browser actually I had only the keys right but I want to make sure that I have some values here okay what I can do is I can go here dim product I can edit dim product okay so in the dim product actually I have only three keys selected okay but I don't have anything but I I want some other things also be to be done right in this particular section what we'll do is we we'll have we'll have some of the values from the name so what I'll take is I'll take the English product name what I have to do is I'll go have to go here and dragon drop okay I want english sub product category name Dragon drop I want English product category name Dragon drop right this C product category and this product sub category and this is product name right I sell sa this right would you like to proceed yes I want to save this click okay and what I'll do is I'll do the same thing for dim customer go here I have only two keys here what I'll do is I I what I want is I want English country region name right and I want uh so do we have any other things here no right and what I want is I want the customer first name plus last name all all those those things right so what I want is like I just want the customer first name customer last name okay I don't want anything else in this team customers click okay save go here and go to the dim date edit the dimensions I have a Time key right but what I want is I want year and month okay so where can I see year and month I have the calendar here year right I have the calendar here this Dragon drop I want the calendar quarter and I want the calendar month so where is my calendar month English month name or I'll select week week also I have something called this week week English month name so do we have something as week here okay so I I'll select English uh day name of the week okay so these are my things available in the uh time Dimension so I modified the time Dimension right so in the facts already have whatever I required I have the uh different measure groups here right now the things are saved so the problem we faced earlier is we had only the keys here this customer key this customer key this is a s sales amone as an IND user right you will not understand what what is the meaning of this key I'm not at all worried about this key why you showing me this key don't show me I don't want right I want give give me the meaningful information right so for that what we did is we did we edited the dimension made sure that the user information is present here right so we made sure that all the things we dragged and Dro right click okay once you done with this what you have to do is you have to deploy and process it again right you have to deploy the cube first so that it is deploying in the same same thing now it is deploying all the changes it is deploying all the different dimensions right yeah now it is deployed so if I go back to my SQL Server 2014 right so I have a thing called cube right if I open this I have thing called Dimensions right if I open this Dimension I have customer Dimension product this one this one right make sure okay so if I refresh this one if I rest this one did I add any New Dimensions here I did not right so it is just showing the new di same tables but I have new attributes of the dimensions which is which are here so now the cube is deployed how can I see the new attributes I have to process it first right so what I should do is right click and process so just expand this click run yeah so now it is processed successfully now it has all the aggregations of different measor groups for this combination of Dimension products for all these combinations it has the aggregations mean Max sum average count these are called as aggregations right all these are present in the ssas engine and it is stored everything in the cube and it has deployed and it has processed okay now we'll go back to the browser section right see the browser section what I want is like uh if you see here it is saying that the que has been processed on the server right the cube has been this is very important the cube has been processed in the reprocessed on the server to prevent possible browsing heads click reconnect to hide this SP click here so when you have done the last time right like you have done something and you have deployed the cube now again you made some changes and you deployed the things again to the server so it is saying that is showing the wrong last screen please reconnect so I have to click reconnect here right now when I click Recon I got a fresh scen okay now you'll see the difference of the things what we have done in the last time and now right so for this customer I told you right for this customer for this product in the month of something just give me a sales example so what I'll do is for this country okay for this country for this customer I'll give the first name last for this country for this customer for this product category for this product subcategory for this product okay for this product for this year okay so to to give you a Clarity what I'll do is uh I'll give you very high level first let me remove everything so just to make sure that you so what I'll do is first of all I'll try take only the country okay from dim customer I'll take only the country okay and dim product I will take only the product category okay here I will take only the year calendar year okay these are my Dimensions the way I see the data or call my Dimensions right these are my Dimensions okay for this country for for this product category for this year what are my sales amount sales amount I can get it from measures right this is the sales amount yaho y now you got it right so for this country for this product category for this calendar year this is my sales amount superb right now to get this information in SQL Server table using some queries now you have to write a query right what if you have to do the same thing in SQL or orle or db2 any database if someone asks that for the for all the countries for all the product categories for all the years give me the sales amount so what you have to do you have to join the tables you to get a quy out of it but ssas is doing by default using its engine so you have done so much of hard work writing that query after someone ask you but he will say that okay now you have given the uh country wise right and you have given the product category wise right just please give it on this product subcategory wise also so just add and add here that's it if I remove this for accessories in 2003 this is the amount right so what you can do is like if you want to select only for some particular year right so you can have some filters here right so dim time you don't have any hierarchy right team time what is the year you I want the calendar year is equal to I want the only the sales from 2004 it will make sure that it will display only the 2004 this is kind of a we condition in SQL okay but in ssas this is not called as wear condition this is called as slicing okay this is called slicing of the information okay in SQL it is called as we but in ssas data Dimension model concept this is called as slicing okay so this is the for 2004 data this is a f amount and someone says that okay this is 2004 data right uh can you give it by every quarter this is the query if you have to write that in SQL it's again you have to modify the query and do it right but in SS it's very easy okay I can give it every quarter just drag and drop like this same sales amount divided into first quarter second quarter third quarter I'll show you something so this is the final thing right so the total sales amount will not vary this is the high level sales amount if you split up for the same accessories for the same year for the for the quarter it will actually split the sales amount intelligently right so it will split the sales amount intelligent ently right if you see this calendar quarter automatically the sales amount will be splitted into the different quarters based on the data same Australia same aies same year 1 2 3 first quarter second quarter third quarter it has split the amount into three different parts right earlier the amount was the cost sum of all these things right earlier the amount was all sum of all these things now it has split the things if you remove the things right again for 200 for it it will do a combination 81309 but if I put it 81309 is split up here because this is 40,000 35,000 75,000 plus 5,000 plus so it will become 81,000 right this is the beauty of ssas which you cannot do very easily in SQL Server if you have to do it in SQL server or or db2 you have to write lot of queries right but as a olab tool to give you to give the end user a meaningful information to make better decisions to enable a better platform for the business we will deliver a concept called business intelligence right this is the beauty of business intelligence you have done so much of hard work building the databases you have done so much of hard work building the ETL process but this particular type of things will give more and more and more input to the end user right so if suppose if you are a sales manager right you're a sales manager of Australia you are interested only in the sales of Australia right you are not interested in anyone what you'll do is you'll select the dim customer and you'll select English country region name equals to and you'll select only I want the sales of Australia only just select I don't want anything else right it will use only the Australia now I want to drill down more analysis right I want to do it okay this is for accessories this this is the total sales right for the bik total sale okay what is the subcategory of it uh can you split that this is a question right I can just drag and drop in the accessories in the buy cracks this year this quarter this is a sale right he says that okay uh just can you give me the product name this is the product category this subcategory right just give me the product names these are the product names and the sales amount is still there and sometimes like he will go here and he'll see okay this particular here it is selling a lot right so okay so let us take let us go to the customer which customer has sold this right which customer has hold this so he will go to the dim customer and you can okay this is the customer right what is his name where is that first name right I think it's is the last okay so this is the name of the customer so what I can do is I can just put down here right English this is the name of the customer this is the first name and this is the last name of the customer yeah first name and last name of the customer this country this customer in this product category with this product name in the year 2004 in calendar year 2 this is the sales amount super right this is called as multi-dimensional analysis you are seeing the data in multi-dimensional way this is one dimension name is another dimension product category is another dimension subcategory is another dimension name is another dimension you product name right calendar here is another dimension and calendar quarter is another dimension and sales amount is your fact just imagine that if someone is asking you to write a query in SQL server and give the result to me in 5 minutes can you do that you have to join join join so many tables make sure that your query is correct so to develop this particular query a developer will take maybe one day or two day he'll say okay after one day we'll give it but after one day the question changes from the manager he says that okay you have given the things by calendar quarter right uh actually can you split it by week I want weekly analysis again you have to modify the query you have to push it to the QA you have to push to the production so many things right so but here what you do okay you want it by week right so what you do is let just go here if you want month example just go here select month just drag and drop that's it in the calendar quarter second in the June month in the first this is the March month right and sometimes every day okay I want to generate a report by every day can you give the which day what is the sale happening okay let drag and drop this no problem in June on Tuesday there is $34.99 again on June on Tuesday there is $1,700 for a different product so this is a superb report he can generate right to generate this kind of report inq it takes days right so this is the beauty of olap tools and at the same time you are talking only about the sales amount right and in the future he will have AA okay actually my sales amount is coming up okay but I just want to how how many order quantity we are selling this drag drop order quantity and this is order quantity right and what is our unit price for that you want to calculate right so this is the unit price right so he want to select what is the standard cost he says okay so this is important right how we can do the analysis he will do the analysis in this way okay this particular product in this this this month I have sold this product at $34 $34.99 but my standard cost is only 13.08 it means there is lot of margin for me the product which I got it at $13 I sold it at 34.99 the product which I got at 1082 I sold it at at uh 1,700 right the product which I got the product which I got at $6.9 I sold it at 8.9 so it means that there is no loss for me he will check all the the product which I got at $713 I sold it for $120 so this is the product you can do this kind of analysis and if you work in a ba project there is no end for the questions from the users users will have so so many questions okay do this do this do this can you give me this can you give me that especially uh I'm working in a sales related company so they have lot of lot of questions every time so what we make sure is we have we make sure that all their questions are answered in the form of Dimension tables and P tables and we make sure that it is available in the cube anything if they want right okay just go here go to the cube just select this one you automatically get so there is no development effort just you can take some decision or you want to uh host a meeting using this data so that's okay you can do that right information is already there so this is the beauty of olap tools where other reporting tools or ETL tools or database cannot do it the same concept you can develop using SQL Server queries or Oracle queries but just imagine how much of pain it is and how much of time it will take to execute and different user has different requirement you cannot give just one query and use for one if there are 100 users each user will have different requirement it's very tough to write those many queries and those queries will be stuck using joints if they have more number of records at the source right to overcome all those problems we have beautiful tools called WAP tools which this is the importance of WAP yeah I hope you got the point here so I wanted to make sure that you understand this concept first and we have so many options right uh if you see look at our module six uh we have so many talk that's okay so let us understand my my my aim is to to make you understand the overall concept of this and it's very easy to understand the different concepts later but if you don't understand this the concept as a startup right then it's there's no use of teaching all these things yeah so make make sure that if you have missed something just just uh see the recording again and just make sure that you're in t right

so today I&#39;ll start with module 6 okay so module 6 is basically talking about ssas Cube design so what is ssas skill server analysis Services is a tool which is used to develop cubes okay so what is a cube cube is a multistructural multi-dimensional is a multi-dimensional object where you use that for different analytic analytics in multi-dimensional way right so usually table is what table is a two dimensional structure but cube is a multi-dimension structure in ssas usually in the physical world cube is a threedimensional structure right but uh here we are talking about multi-dimensional structures and we call it as a cube okay so what is this multi-dimensional structure why it is called as multi-dimensional structure what is the exact difference between a table and a cube you will learn in this class okay which is very important okay so these are the list of things which we are going to cover today okay so there are lot of things here okay there are lot of things so there is no surprise that we will be left over with couple of topics okay or three or four topics that&#39;s okay so we&#39;ll we we&#39;ll take up that in the next class okay but in today&#39;s class let us understand the in and out of the basics of ssas and at least we&#39;ll make sure that you understand what is a Cube and how it is deployed how we can use the cube and how it is can be connected from a different things okay so we&#39;ll see how it goes okay so before going uh jumping anything on the ssas I will I will do something I&#39;ll tell you something on a ba semantic model so why we are all doing all these things why we are developing databases why we are doing ETL why we are S as and rs so what is this all Ed for right so at the end of the day this is all used to make sure that your clients or inducers have correct information on hand which will be which will enable them to take better decisions right this is what the overall agenda of this one right but how we can enable them to make the better decisions is using the concept of bi right okay so in the business intelligence semantic there are two things right one is a table solution and the second is a multi-dimensional solution so some you you&#39;ll have the data in the form of your table example you have a employee table or you have you have uh uh you have some product table anything right so that is a the table of format right but uh you can use the reporting actually using the table models also example employee table product table you can join them you can create a view in them or you can create a some store procedure or something you can generate a report right it&#39;s very easy but the thing the the problem if you do like that as the size of the data set grows it&#39;s very tough to give Optimal Performance example if you have a product with 1 million records and employees with 200 records and with uh a sales of uh 20 million records if you join this table either in re or SQL server or db2 any database it will stuck for some while right because this is joining for two many too many millions of rows and the user has to every query user has to wait when he&#39;s doing some analysis user has to wait for 5 minutes 10 minutes you don&#39;t know but nobody has time to wait right so what user wants is just browse something okay get a automatically that is not possible is in the table or model so in the multi-dimensional model what we are going to talk using the ssas which is the olac model those things will be very useful for the IND user and it brings the Optimal Performance okay as I was telling talking about the end users who will be using this data so so if you work for some organizations like already so you know who are the IND users right so basically some of the IND users something like just uh give me a platform I will do my analysis by myself right but some team say that okay so we have a group of users or group of managers where you want where they want to analyze your data right so just give us some solution okay so if there is one person then uh they may do the reporting or some analysis some in Excel but if there are too many persons if they want to stand on the same page if they want to analyze the same kind of data to make better decisions they need a single source of Truth which is in the form of a data Arrow so they need the data wos things in the form of some cube in ssis and they want to see how the data is coming up so in those Concepts if you if the users work on different versions of truth if they work on different versions of the data it&#39;s very confusing then and there is a conflict right so if there should not be a conflict then you should use a data or SS to QQ to give the information correctly to the users yeah okay so you you have uh yesterday I was telling you about two things okay very important let me uh okay two thing I was talking about two things right so what what are the two things I was talking about I was talking about dimensions right some of the examples of dimensions are dim customer dim product dim date dim geography dim product category right these are the example of Dimension tables right so these are some of the examples right so Dimension tables will have surrogate Keys okay diamension tables will have surrogate keys and what fact tables will have fact tables will have measures so we call it as facts right what are the measures measures are something like sales amount quantity total extended Price Right these kind of facts are called measures remember this one you&#39;ll see this particular word used many times in ssis okay what are called as measures measures are nothing but facts or numbers what are the examples of measures sales amount order quantity tax amount these are the measures which are called as facts also Dimension tables will have surrogate keys in the form of a key which is also like a primary key right if you combine Dimension tables okay if you combine Dimension tables with fat tables then you can get something as a cube okay if you combine d mention table with a fact table you get something as a cube so how you will get that all those things we&#39;ll see it later okay so this is the concept of cube so yesterday I was telling you about the uh some of the examples of the cube right some of the examples of the fact table so if you see this right uh if you see this what happens if you this particular thing explains you about a customer different customers in the different regions in different what are their sales amount okay what are their sales amount so the data is displayed like this in the Excel spreadsheet but when it comes to our data erors it need not be like this right this all this customer R and customer name will go to dim customer okay this particular will go to dim customer and this will go to dim geography region will go to dimy and these are what April May these are months right months and years this will go to dim date okay and what and these are the sales amount right this will go to a fact table right so this is a set of data this set of data will be converted into different things in our data Aros right so so this is uh some something which you learn uh in this class right so this I I hope you you understand uh uh how the dimension table are fact tables are okay so uh okay we have something right we have something to start so before starting anything I want to make sure that you guys are comfortable with some of the things here right so any questions uh just put it in the chat window if I see it as important to be answer as possible then I&#39;ll answer it otherwise Lo right uh I&#39;ll create a project for create a project for ssas right so what I have to do is I have to right click and run as administrator in my SQL Server data tools click yes yeah so here is as the same way when we were doing for the SS ssis project we used to create a new project right so in the same way we can have uh a project created for SS SS uh as right so in the business intelligence uh you have three sections previously we used to create integration Services project now we should create analysis Services project okay we should create analysis Services project so it is asking for M PA but what I want to do is I just wanted to change the path to edor right so what I&#39;ll do is like okay I I&#39;ll I&#39;ll give the name as this one yeah it says the select folder it says that it does not exist right so what I&#39;ll do is like I&#39;ll create this okay I&#39;ll create this folder okay so this is my uh path and what what I&#39;ll do is like I I&#39;ll give my name as first VI so the name of the uh project is the first Cube and this is my path and what I have to do is I have to select analysis Services here and there are different sections which you need not worry at at this particular time okay so the most important thing is analytic analysis Services multi-dimensional okay the most important thing here is analysis Services project right so here you have if I expand this if I expand you to make you clear picture on this right so there are different things here analysis Services multi-dimensional data maning project okay and import server from import from server so if you want to import something from the server analis which say table table table project okay and import from Power keyboard and import from server so these things right so these things are the additional fun functionalities available in the ssas okay additional things which is available in the ssas but the very important thing which constitutes 90% of uh ssas is this portion something like ssis we have so you have to select this particular thing and click okay then it will create something as analysis Services uh project okay so once you create this analysis Services prodject project this is also called as multi-dimensional development project okay okay so this is called as multi-dimensional development project and also you can have some uh development about the data mining but very few organizations use the data mining okay here we will talk about uh multi-dimensional analysis and we&#39;ll talk about the cubes which are the core concept of s say yes okay so if you if you come here like uh there are different sections here so make sure you understand all these things here so there&#39;s a big section here uh which is related to different parts of ssas so in the same way you have uh first of all there is a concept called Data sources so what it talks is like it says that what is your data source just tell me that so you can have some data source views it is something like having some views in the SQL server or Oracle right on on top of the base tables and we talk about the cubes okay so these are very important things okay and we will talk about the dimensions also four things we talk about the dimensions and uh in the future I I&#39;ll tell you something on roles okay and the remaining things is uh not required for this particular class right because this talks about uh Mining and all those things which is another topic okay so we&#39;ll concentrate on data sources data source views and developing cubes right and we&#39;ll talk about the dimensions and the different things okay to start with right uh to start with what I I just wanted to tell you something so I have a database called okay I have a database called SQL Server 2000 I have a server for SQL Server 2008 R2 and I have a database called Adventure works gwx right Adventure work gws so what are the tables I have in this one I have some few tables called which is related to the dimensional table and fact table okay so how you can get this is actually there&#39;s a lot of stuff available in the Microsoft uh website you can download adventure worlds DW 2008 R2 or Adventure worlds DW 2015 right where you have the similar kind of tables okay so the reason I have it in a different database is when I was giving the demo uh in one of the sessions so I I created a adventure Works gwx which have which which will store my Dimension tables and F tables okay so this is the database adventure of zwx where I have my Dimension tables and fat tables okay okay so if someone is not clear about SQL Server if you want to generate a ER diagram if you want if you want to generate a databas diagram to see what is the linkage between these things you have a section called database diagrams okay if you expand this if you already have a database uh diagram it will show show it up if you don&#39;t have it will ask whether you want to create it okay just create it so this is just to make sure to understand what is your ER diagram and or or a database diagram how the connectivity is taking place so you can actually create it from here okay okay or you can do it as a new database diagram so let us wait for it to come back right so what you can do is this make sure that you understand the relationship I select everything this start yeah so what it will do is it will they will create a database diagram based on your based on your tables right but what happens is like if you see this so there is no linkage between the different things okay there is no linkage between the different things so now you can use this particular tool to uh link this up okay but uh if you want to understand this structure clearly so you based on this key you can use some tools called Visio Microsoft Visio to develop ER diagram for this okay so this is where you can create your diagram and all but the same thing I&#39;ll show you it in SQL analysis Services how the uh things are linked up and how the things are uh how the things are connected to each other yeah so what I&#39;ll do is I&#39;ll go back so just make sure that we&#39;ll be using this particular database right we which where we have the dimension tables and fact tables okay so I&#39;ll go back here what I&#39;ll do is I&#39;ll create my data source I&#39;ll create my data source so when when I create my data source so there are some few visards okay so whatever you are seeing in the pp right in the pp you may see you may be seeing the same steps okay but the same steps we are covering it here so what we&#39;ll do is like when when you create a data source it says that okay I&#39;m ready to develop a cube but tell me what are your data sources okay it is saying that I&#39;m ready to develop a cube but tell me what are your data source okay I&#39;ll tell you what is it so what is our data source our data source is 2008 server and this is the database name right okay so what I&#39;ll do so it is saying that already I have a connection for my 20 server so if you don&#39;t have a connection click new click new and you can give the server name here right and if you have Windows authentication just connect with it or then you can just select the database name and test connection test connection succeeded right you can do like this but uh already my database I have a connection if you have existing connection you can use this EX create is same as ssis click next okay so here there is a very important section something called as impersonation information okay which is called as impersonation information what I do is initially I&#39;ll tell you what is this concept I&#39;ll click here first of all as different one of the sections called uh example I click this as use the service account okay click next but I&#39;ll tell you what is that right so what is my data source data source is talking about uh internet sales right okay sales Source yeah so I&#39;ll put the name as this one okay here we&#39;ll come back as I said I&#39;ll come back on in person information right so what I&#39;ll do is I&#39;ll double click so what we are doing here is we are telling this is my server this is my database name and these are the uh list of tables I&#39;m going to access in this one right if I double click that again I have the same thing here and I have a section called impersonation information okay so here what is my thing my connection is pointing to 2008 server but database is not selected here okay so I want to select a database called Adventure ver dwx where my Dimension tables and fact tables are stored okay and I want to use the windows authentication okay I want to test the connection test connection succeeded click okay and you done with this you done with connecting to a 2008 R2 database which which is having Dimension tables and fact tables okay and there is one other important section and where very important interview question as well okay very important section which is called as impersonation information okay so what is this impersonation information okay this in impersonation information means you are telling ssas engine you&#39;re telling ssas engine how to connect to my data source okay so what is my data source my data source is available in 2008 R2 database in the adventure Works dwx database you telling ssas engine how to connect to your Source because this particular SS the whole stuff whatever you open you opened in SQL 2014 version right but your data source is 2008 version you&#39;re telling ssas how you will connect from this 2014 of ssas engine to your Source system your Source system may be a different database also right so this information impersonation information is talking about that okay it is talking about do you want ssas to connect your data source using a Windows username and password do you want to do this uh okay if you want to do this you can do this okay or do you want to use a service account it means there will be some service account right for every ssas okay if you go to control panel okay so let me uh remove normal mode okay if you go to all programs uh SQL Server 2014 configuration tools SQL Server 2014 configuration manager if you open this it will open the configuration manager which you already opened here right and it will have the list of things with the services which are running in the 2014 so there is a service for SQL Server analysis Services 2014 the highlighted portion right so if I double click this one if I double click this one okay so this is using a service account called This is using a service account called n service and some msol app 2014 okay this is called a service account okay so in the production system or development system you have a same service account like this so it is asking whether you want to connect using the service account okay so if you if you select this option it means that this service account should have a access to your Source database also you have to make sure that yeah or you want to use the current credentials of the user so person who is running this particular ssas do you want to use the current credentials okay or do you want to inherit what is the meaning of inherit do you want to inherit whatever you used in The Source whatever you used on the general tab you give some information right do you want to inherit the same kind of informations okay so this is in personation information the main thing primary thing of it is to make sure how your ssas engine will connect to your data source so this can be any number of types depends on Project requirement and your security model okay so we will see we we&#39;ll select some of the impersonation information as a start and we&#39;ll see how it goes okay here what I want to do is I want to use imperson information called service account okay just select okay so we&#39;ll we come back there so we&#39;ll come back and see if there are any problems okay so my data source is created so what is my next step next step is to create data Source views right so what is this data source views you may be having several tables in your database I may be I have around 20 tables here but I don&#39;t want to create a cube using all the 20 tables I want to create a cube using only some of these tables okay so that&#39;s the reason why I want to create a view which is pointing to my base tables and which I can use in the cube also it is something like a view in SQL server or orle or db2 right so new data source view click next so what is the source you want to point to to create a view already I view already I have a thing called this particular Source right so the same Source it is showing up here do you want to use the same source to create your data source right yes I want to use that right so I want to use the same thing what I&#39;ll do I just select that click next okay next one is no foreign keys were found you can create logical relationship on matching columns okay so what happened is what happened is when we tried to create a database diagram here right it was not linking up because there was no primary key for there are primary keys but there were no foreign Keys that&#39;s why the tables were not linked up but what SS is saying that I don&#39;t see any relation ship between a dimension table dimension table and a fact table in your data source do you want to create a logical relationship within ssas yes I want it to be done okay so I want I can create logical relationship by matching columns what is a matching column if there is a uh product key in diamond dim product the same product key will be in Factor internet sales right using the matching columns actually it can create The Logical relationship ssis okay so what is your criteria for foreign key matches same same name as primary key okay so it may be something like that right may name product key in product and product key in pable is it like that or same name as destination table no it&#39;s not like that destination table plus primary ke so what is your key here so I have customer ID in orders table and customer ID another table so I&#39;m I&#39;m going to match using the name of the column right and click next okay here it is asking what are the different tables you want to use what are the different tables you want to use for developing for developing a cube so you have to select some list of tabl based on that it will create a view okay here I don&#39;t want to select everything okay I want to select very few things right what I want to select I want to select dim customer okay I want to select dim geography and I want to select dim product dim product category dim sub product category okay and I want to select a table called dim time okay which are all Dimension tables and I want to select a table called one fact table I don&#39;t want to select all the fact tables I want to select one fact table called fact internet s okay so these are the tables I am selecting one is dim customer and dim Geography okay and I have a dim product product category sub product which are related to the product and I have a Time Dimension and I have fact internet sales okay so what is the the name of the data source view you are giving I&#39;m giving it as data source view as sales uh okay I&#39;ll leave it as the name of sales tables Okay click finish what happened because we have selected based on the primary key of the column name make please please use the foreign key and create a logical relationship right that&#39;s what we selected in the cube in the VIS right so that&#39;s why using the primary key what it did is it has automatically Linked UP it has automatically linked up using the it has automatically linked up using the uh primary key primary key and its name right so if you see here if you see here this particular product key in dim product this particular product key in dim product table and here also you have a product key because the names are same it has linked up using the product key column okay so again you can see that it is linked to dim customer right there is a customer key and you have a customer key here so that&#39;s why it is linked up with these two things okay but this fact table is not linked to any other things what is the reason for that because it did not find the matching name okay but the matching name it has found out in di product table and dim sub product category here you have thing called Sim subcategory product subcategory key right and you have a product subcategory key here also that&#39;s why these two tables are linked and then it links to product category you have a product category key here and you have a product category here so that&#39;s why the linking is done based on this one so what is happening here in the same way you have a geography key here and you have a geography key here So based on the column names based on the column names actually it has designed your logical relationship okay so here if you see what kind of schema is this what kind of schema is this this is a snowflake schema right because not all Dimension tables are connected to a fact table right fact table has only connected to only these two tables right only these two tables right but these tables are normalized dim product table is normalized into dim sub product category and dim product category and dim customer is normalized into gym geography right so because the the reason it is normalized this is called as a snowflake schema okay this is not a star schema okay but you will see one particular dim time which is not at all linked to any of the tables white is not linked okay so if you see what is it time key this is a Time key right but you don&#39;t have any time the name with the time key here okay because the names are different that&#39;s why did not match it okay but if you go to some columns called due date key or ship date key right or order date key all these keys are also talking about a date which is also a Time key which is internally mapped to this one because the names are different here and here that&#39;s why it did not match okay what you can do is uh if there is no matching automatically using the name there is a option in ssas to create a new relationship okay select here select here right click go to new relationship you can always have a new relationship here okay what is your Source foreign key what is your Source foreign key table and destination primary key table so my foreign key table is fact internet internet sales what is my destination table primary key table right my primary key table is dim time right I can s dim time so what are what are your Source columns which can be mapped to the this one so my source column this product key oh sorry uh order date key which can be mapped to time key okay once I map map it to the time key I cannot see it again right once I map it to the time key I cannot see it to again right so I&#39;ll put as order key my order date key is nothing but time key so because these two are tip to together click okay now it is a there is a linking right now there is a linking correct so time key is matched to this one okay so in the fact table if you see fact table is a combination of all the surrogate Keys product key order date key customer key which is connected to this one and it will have another something as facts on the bottom of this particular table right and the dimension table will be only having the details about the dimension okay Okay click save what I&#39;m doing is I&#39;m doing doing control s in my particular uh laptop so what what you can do is you can go to file and save right so now this is saved right now this is saved so what you have done here you have done only the what you have done you have done only the relationship or the linkage between the dimension table and fact tables in the data source view right you have done the relationship in the data source view okay so once you are done with the data source view you can close this okay you can close this and come to a section called cubes click new Cube Okay click new Cube so now using the dimension tables and fact tables we are going to design a cube okay so I&#39;ll click next okay here there are some few options okay here there are some few few options so the few options are something like this do you want to use existing tables when creating a cube do you have already the existing tables usually 99% of the times you will have existing tables if you are working for a company unless you are end user who want to create very Dynamic things you will have this have option selected but you 99% of the thing you select I have existing tables yes right so or it can say the generate table generate tables in the data source do you want to generate the new tables in the data source I don&#39;t want to do that right I will say that I already have the existing tables which are in the form of Dimension tables fact tables which I use the ETL ssis ETL to load those Dimension tables and fact tables and it is located in data ER I want to use that right so I&#39;ll tell that use existing tables okay now very important section okay here in the cube visard it will ask you to select what are your measure group tables what are your measure group tables so what is called as a measure measure is nothing but a fact or number okay in the database terminology you will call it as a fact or you call it as some number okay but when you come to the dimensional modeling Concept in the dimension modeling concept it is called as measures facts are called as measures and fact tables are called as measure groups okay facts are called as measures fact tables are called as measure groups okay so it is asking you to select what are your measure groups asking you to select what are your measure groups okay so my what are my measure groups here I have only one fact table so this is my only measure group I don&#39;t have anything else right this is my only measure group so what I should do is I should not select everything here right I should select only my major group right what is my major group this is my measure group right so don&#39;t select everything because your measure groups are nothing but your fact tables click next okay once measure group or fact table is selected it will ask you to select what are your measures what are your measur measures are nothing but your facts right measures are nothing but your facts here you have to identify what are your measures okay so I&#39;ll tell you here fact table is combination of so fact table is connected from different dimension tables using the surrogate key and it will have the surrogate keys from the different dimension tables and it will also have the facts right so this all these Keys whatever you&#39;re seeing here all these Keys due date key ship key these are the referencing fields from Dimension table see these are not your measures right these are not your measures so just don&#39;t select this so what I&#39;ll do here here I will select Only The Columns which I feel it as a measure which feel it as a fact right this is my linking column I will remove this this is link column remove this remove this remove this just remove this I don&#39;t want this number order quantity is my measure yes this is a number unit price is a measure this is a measure this is a measure measure measure measure sales on is my important measure okay tax amount is my measure Freight is my measure okay but there is one new column which came up here okay okay this is a new column which I don&#39;t have in the data source right which I don&#39;t have in the data source this is automatically created by ssas engine okay is automatically created by data engine okay [Music] okay okay so just select this just remove this and just select the remaining things and click next and what are your Dimensions my Dimension tables are this one right so what I want to do is I want to automatically select all these Dimension tables okay I don&#39;t this is my fact table just remove this and click next right so what is your Cube name say I I I will put the cube name as sales Cube click finish now a cube is created we will see how this cube is created okay so this is the cube what we have created okay so we had the data source as say sales data source and we have created some data source view which is some combination of some Dimension tables and fact tables and then what we did is like we created a cube while creating a cube we have taken couple of important steps we have selected whatever what are our Dimension tables and what are our fact tables so Dimension tables are called as we have selected as Dimension attributes and for the fact tables we have selected as measure groups okay so once it is complet so you can see the particular screen like this right okay so you can see the screen like this which is generated by ssis automatically so what is happening here so we have some few tables which are connected right so how this is how these are connected so we have a table called Product but because that is being normalized so it is divided into three tables right and which is making into the fall of snowflake schema right and we have some couple of tables in customer and dim link to each other right and we have another table called dim time okay and these three tables dim time dim customer and diing product or having the surrogate Keys which you are pointing to fact internet sales okay so the overall schema is called as a snowflake schema okay so here if You observe this particular section there are lot of things which are new for you and you&#39;re seeing for the first time if you&#39;re learning ssis for the first time right all the things we had in ssis are completely different than this right so on the left section here you have something called as measures okay so whatever the measures you have selected in the quizard so all these measures are present here and there is nothing present which is not selected right these are called measures and in the bottom section you have something called as dimension ions okay these are called as Dimensions so in the dimensions what is happening you have selected a dimension table called Product customer and dim time okay and you have selected selected it corate Keys based on the normalization what it did is it has intelligently created something here so if You observe it carefully we have selected three tables right we selected three tables called dim product Sim product category and dim product category but what it did is it created only one parent table in the visard we have selected three tables but what it did is it has created only one dm product but it made sure because it is linked up each other it made sure that it has a product category key product key and product subcategory key okay although in the database side it is normalized into three different groups okay although on the database side it is normalized into three different different tables okay but coming into the cube section it has created only one table but it made sure that the keys are intact okay so this is the beauty of ssas so it can intelligently identify and it can Ro the things right and in the same way although on the database side this is normalized into two different things but it has created two different keys in between the DI customer which is a parent object right and you have something called as dim time where you have time key as the surrogate key right so these are the dimension tables and tables which we have created and in s in the ssas you can see them as two important groups right what are those two important groups one is called as dimensions and second is called as facts which are called as measures okay these two things will form a cube Okay so it will form what are the things about it we&#39;ll see it right at the same time when you create a cube there are lot of new sections here which which is on the top ribbon okay on the top ribbon there are lot of new sections so what we&#39;ll do is we&#39;ll go we&#39;ll make sure that we will cover all these sections right we&#39;ll cover all these sections and see what are the uses of different sections okay so in ssas it has some beautiful functionalities which are not present in other olac tools okay in terms of design or in terms of functionality okay so we&#39;ll see what are those things yeah so I hope you you have the concept of this Cube now right you have the concept of this Cube now so what I did is I just try I&#39;m trying to save save this it is saying that the following objects will also be saved would you like to proceed yes I want to proceed right so just save it okay so so so that the whole project is saved okay so what happen is like this is a sales Cube and this is a data source and this is a data source view data source data source View and this is the cube right okay so very important here uh what we&#39;ll do is I&#39;ll try to do some things here what I&#39;ll try to do is I&#39;ll try to deploy the cube okay now the cube is created okay cube is created but I&#39;ll try to deploy the cube and there is a concept of process the cube okay and we will say face some issues while deploying and I&#39;ll tell you why those issues are coming up okay and we will fix the problem okay right and after that we&#39;ll have one voiceover session where you can ask the questions on voice and we&#39;ll talk on that sure and anything also pinned on the chat I&#39;ll make sure that I I&#39;ll answer that once these things are done okay so this is something on the uh Cube so to deploy the cube what should I do is to deploy the cube I should go to so project level this is my project level my first cube right so go to properties okay go to properties so what you what you do is like deployment server Edition like there a Developer Edition or enter presentation so it&#39;s okay so my thing is a Developer Edition so uh that&#39;s fine I leave it as So based on the Edition you just select your type of Edition so how you can know the type of Edition you have you can go to your uh uh you can go to yourl server 2014 and select at that uh version okay so if you execute this like you&#39;ll be knowing what type of Edition you&#39;ll have so my thing is uh Enterprise evaluation Edition okay so to know the version just come here right so what you can do is like it&#39;s okay so I can deployment server Edition I can uh I just put it Developer Edition we&#39;ll see what happens right and deployment server version 12.0 output been through so remove passwords equal to uh I just put it as true so let&#39;s see what are the things happens right and just debugging so just leave it as it is and come to the deployment section okay in the deployment section initially don&#39;t don&#39;t touch anything okay what you do is you specify your server name where you want to deploy you specify your server name where you want to deploy the ssas okay so what I want to deploy this ssas in SQL Server 2014 of analysis Services I want to deploy this in SQL Server 2014 analysis Services right so what is my server name my server name is this one okay and how can I say it is a 2014 version using the instance name okay and what is your database name so here there is a concept called database so which will be same as your project name okay or you can change it okay just try to keep this properties click okay and click okay save it okay and go to build okay go to build and make sure that you build the cube now it is saying build started and build succeeded if you see the bottom section right there are no errors okay so once you build the cube there are two important topics okay one is deploy and second one is process okay so this cube is generated if you want to deploy this Cube already you have set the properties right you have set the properties where it is to be deployed okay so let us try like what happens if you deploy I say deploy okay we got some few errors okay let us see what what are those errors if you see this errors what it is saying is login failed for the user ENT Service msol app this one what it saying it is saying that heyy your login is failed and something is not working okay so why this login is failed when we have created our data source okay I told you there are two one two important sections right one is General how you want to connect to your Source database using some authentication right so that you can develop it okay but when the ssas is running it when the ssas is running it okay when SS is running it how you want to connect your ssas engine to your data source how you want ssas to connect to your data source okay so what is happening is this ssas is running using a service account called n service msol lab SQL 2014 so where where you can see the service account you can see the service account in configuration manager is the same service account it is using as log on as but this service account is not a login in 2008 server so this is my 2008 server this is my 2008 server if you go here in the security tab there are list of sections so I don&#39;t have MSO app 2014 as a login here okay that&#39;s the reason why it is not able to connect okay so if you have a login for the to app uh network service here then it will be able to connect to the so you have to make sure that whatever the service account or whatever the property you give in the imperson impersonation section it should make sure that it should it can connect using those IDs using those credentials to data source so that is called as impation so I use service account it says that uh no it is not available so you can maybe you can try with use a specific Windows username and password so what is my windows username password my windows username this one is the username and uh this one is the password okay so this is why I I just keep my windows username and password right and just saved it okay we&#39;ll try to do it again deploy okay so what is happening so it is trying to deploy the things it is trying to deploy the things things and it says that deployment completed successfully now why it has completed successfully because in my 2008 server which is my data source I have my windows user ID I have my windows user ID as a login right I have my windows user ID login that&#39;s why the impersonation using my windows credentials what given in impersonation information it can create a connection to my source server that is the reason why it is able to connect and deploy the things to SQL Server 2014 okay there is a catch right just give a second uh okay so I got you I I hope you got the right so it is going to Source 2 year server and it is making sure that it can connect to it but it is saying that it is deployed completed successfully but where it has deployed this it has deployed in a separate place right so that place is SQL Server 2014 so where we have given the deployment properties it has deployed to this one 2 14 server and it has created a database called first cube in this 2014 server let&#39;s go to the properties and in the deployment we have given a deploy to 2014 server but by source is 2008 connect to my source using my windows credentials but deploy here okay I hope you got the point right right so what will happen is I should go to my 2014 analysis services and see if it is deployed properly right so how can I go to 2014 analysis services so there is a so what you have to do is to go to 2014 analysis Services first of all make sure that in your configuration manager there is a section for SQL Server analysis Services 2014 make sure that this service is running okay if this service is not running try to start it okay now currently my service is running so it means that my analy service is up and running right so so what you can do is you can open this 2014 management Studio using runess administrator or some user ID password where you have the permission right and this will be open right once you open this there is option for connect and here you have to connect to analysis Services okay here you have to connect to analysis Services 2014 and click connect right so what is happening once I click 24 anal Services now I have a different thing here so the top section is for SQL Server 24 database right but this is for analysis Services okay and in our project it is saying that it has deployed to your database called first Cube it means that I should have something in the database section here with some name as first Cube yes I have so it means that it is deployed successfully right so just open this go data sources this is our sales data source is same as this one and this is our data source view same as this one and this is our Cube which is same as this one and in the cube we have measure groups and we have Dimensions yeah so this is deployed successfully okay it means that cube is deployed success what I&#39;ll do is I&#39;ll just close this okay I&#39;ll just close this I&#39;ll remove this my cube is deployed successfully but once my cube is deployed successfully it doesn&#39;t mean that I straight away I can use it okay once the cube is deployed successfully we have to make sure that that is processed what is process what are the types of it we&#39;ll see it later but on High level I&#39;ll try to process it what is the meaning of process okay this is very important okay you have different dimension tables dim time dim customer dim product and you have a fact table what it will do all the Sate keys are connected to the fact table to the centralized fact table and it has a different carugate Keys as a relationship to the dimension tables and it has the facts in it now we have designed a cube in the same manner but ssas should internally create some aggregations important word ssas should create some aggregations so that it has all the combinations of this data so what are these combinations so this will store some aggregation example this C particular customer in this month with this product this is the Sal total sales amount right now I&#39;m talking about total sales amount sometimes I want average of sales amount sometimes I want the minimum of sales amount I want maximum of s sales amount right so my aggregation can be anything I I I may require total sales amount minimum sales amount maximum sales amount right so my my average sales amount or I may sometimes you can count of number of uh Records this is also another type of aggregation right so my my aggregation can be anything right so all these aggregation ssas will create internally using the engine when you process the cube okay you you&#39;ll understand about this topic more in the future classes right this is about something about the process for processing what you have to do is right click okay go here and show process what it is saying the server content appears to be of date right would you like to build and deploy first project first it is saying that it saw some few changes like after we have deployed it is you want to yes I want to do it just build it succeed it and try to deploy it right and uh now the deployment is completed right if you see this deploy deployment is completed successfully the message on the bottom section right and automatically I have already the thing if I refresh this uh this right I have a cube called my sales cube right so but the new window has come up which is a process okay so there are different options for the process so don&#39;t change anything for now what are the processing options I&#39;ll tell you more on that okay but just expand this window so that you can see everything and what do you want to process when you process it calculates all the aggregation that stores in the m Dimension Cube okay it says that proc the information is somewhere and this is this is your processing information right click close click close it means that it has done all the aggregations and it has kept inside right now it is completed just this right the QB is also process okay now if I go right if I still refresh it I I&#39;ll still have all this the process it means my cube is ready to use now okay my cube is ready to use okay so what I can do here to use the cube to use the cube make sure that there is another service up and run also one of the important question okay go to the configuration managers there is a SQL server browser okay there is a service called server browser make sure that this is up and running if this is not up and running so that you not browse the cube using the S okay so make sure this is up running right so what I do is to browse the I have a particular called browser okay I have a called browser here I&#39;ll go to the browser section I&#39;ll go to a browser section in the browser section I can see my sales Cube okay I can see my sales Cube which is the name of the cube I can see my measures which is are my facts right I can see my Dimension tables I can see my Dimension tables right and automatically I can start using the cube right automatically I can start using the so how can I use the cube I&#39;ll show you simple scenario just save it okay right so on the top of the cube one important section you have one thing called Dimension hierarchy operator filter expression okay so what type of Dimension you want to select so I have a question from my manager okay so my my my my requirement from one my users or sales manager is okay for every customer for every product in the month of some January show me what are the total sales figures this is the query he asked for every customer so I should go to the customer key right okay so what what he asked is just show me for every customer what are the different things present right what I can do is I can just take the customer key and drag and drop here it says no row found that&#39;s okay and what I can do is for every product means I can drag and drop product key okay he says that I want in the month of something right I want in the month of something but if you see I have only the thing called key right I don&#39;t have anything else so this is bit confusing here so I cannot know what is my month name and all those things right I cannot know month name and all those things so what I&#39;ll do is like okay for for default for to explain you I I&#39;ll just drag and drop something right so what I can do is I can drag and drop the time key right so what what he wants is he wants the sales amount right just go here just go here what is the sales amount now it is executing the results and it is giving the results for this customer for this product for this time frame this is the sales amount but what are these values these are the surate keys will the end user will understand the surate key no right so you should give me the customer name give me the product name give me the time then I will make sure that I can understand this one it is giving the results properly and in the future he may ask that okay I don&#39;t want the time frame just for customer and product just give me the total sales am just remove this and it is executing it is changing the numbers okay so this is how you can deploy and process Cube and use it okay but one important piece of information what we are missing here is we are having the keys only only Keys we don&#39;t have any uh attributes to it right you don&#39;t have an attribute state so that the meaning the information whatever you&#39;re reading here it is not giving you the clear information right so that&#39;s why so as this is the first Cube so we will we will stop it here and see how we can fix this problem in the while creating the next cubes right so this is the very high level concept of the cube and you how you can use it example you want to use some other cont okay I this is the total sales amount right what is the total sales amount uh what the what are the total number of products uh ordered right this which is nothing but ordered quantity you say someone this is order quantity so you can just drag and drop whatever you want and it will automatically shown here yeah so this is how you can link up but the thing is that you can you&#39;re not getting the correct information from the dimension because we we have done we have not done some of the things which we will do in the later parts of the cube okay so this is something about the overall thing about the cube and we will see more things about the cube in the in the next few minutes or in the next class also so this is something right so what we can do is like we did not we can make some modifications to this particular thing and make sure that we have the new things coming up right so I&#39;ll go back to the slides okay I&#39;ll go back to the slides and see what we can go forward from that right so what we did is we did a cube called one of the cube development and what we did is like we have selected one of the uh data source when we have selected the impersonation information and we have uh used A View visard to get the view and we have we have something ready so this is maybe bigger picture right but what we had is like we have something right we have what we did is like we did a cube right and what we did is like we made sure that we have all the measure group selected and we click next and we made sure that we all the dimensions ready right and uh this is something it uh looked like right okay okay so one one important thing what we missed out here is is uh uh we saw only the keys available on the browser right so but uh actually we can have some of the information present in that okay so we when when I saw the browser actually I had only the keys right but I want to make sure that I have some values here okay what I can do is I can go here dim product I can edit dim product okay so in the dim product actually I have only three keys selected okay but I don&#39;t have anything but I I want some other things also be to be done right in this particular section what we&#39;ll do is we we&#39;ll have we&#39;ll have some of the values from the name so what I&#39;ll take is I&#39;ll take the English product name what I have to do is I&#39;ll go have to go here and dragon drop okay I want english sub product category name Dragon drop I want English product category name Dragon drop right this C product category and this product sub category and this is product name right I sell sa this right would you like to proceed yes I want to save this click okay and what I&#39;ll do is I&#39;ll do the same thing for dim customer go here I have only two keys here what I&#39;ll do is I I what I want is I want English country region name right and I want uh so do we have any other things here no right and what I want is I want the customer first name plus last name all all those those things right so what I want is like I just want the customer first name customer last name okay I don&#39;t want anything else in this team customers click okay save go here and go to the dim date edit the dimensions I have a Time key right but what I want is I want year and month okay so where can I see year and month I have the calendar here year right I have the calendar here this Dragon drop I want the calendar quarter and I want the calendar month so where is my calendar month English month name or I&#39;ll select week week also I have something called this week week English month name so do we have something as week here okay so I I&#39;ll select English uh day name of the week okay so these are my things available in the uh time Dimension so I modified the time Dimension right so in the facts already have whatever I required I have the uh different measure groups here right now the things are saved so the problem we faced earlier is we had only the keys here this customer key this customer key this is a s sales amone as an IND user right you will not understand what what is the meaning of this key I&#39;m not at all worried about this key why you showing me this key don&#39;t show me I don&#39;t want right I want give give me the meaningful information right so for that what we did is we did we edited the dimension made sure that the user information is present here right so we made sure that all the things we dragged and Dro right click okay once you done with this what you have to do is you have to deploy and process it again right you have to deploy the cube first so that it is deploying in the same same thing now it is deploying all the changes it is deploying all the different dimensions right yeah now it is deployed so if I go back to my SQL Server 2014 right so I have a thing called cube right if I open this I have thing called Dimensions right if I open this Dimension I have customer Dimension product this one this one right make sure okay so if I refresh this one if I rest this one did I add any New Dimensions here I did not right so it is just showing the new di same tables but I have new attributes of the dimensions which is which are here so now the cube is deployed how can I see the new attributes I have to process it first right so what I should do is right click and process so just expand this click run yeah so now it is processed successfully now it has all the aggregations of different measor groups for this combination of Dimension products for all these combinations it has the aggregations mean Max sum average count these are called as aggregations right all these are present in the ssas engine and it is stored everything in the cube and it has deployed and it has processed okay now we&#39;ll go back to the browser section right see the browser section what I want is like uh if you see here it is saying that the que has been processed on the server right the cube has been this is very important the cube has been processed in the reprocessed on the server to prevent possible browsing heads click reconnect to hide this SP click here so when you have done the last time right like you have done something and you have deployed the cube now again you made some changes and you deployed the things again to the server so it is saying that is showing the wrong last screen please reconnect so I have to click reconnect here right now when I click Recon I got a fresh scen okay now you&#39;ll see the difference of the things what we have done in the last time and now right so for this customer I told you right for this customer for this product in the month of something just give me a sales example so what I&#39;ll do is for this country okay for this country for this customer I&#39;ll give the first name last for this country for this customer for this product category for this product subcategory for this product okay for this product for this year okay so to to give you a Clarity what I&#39;ll do is uh I&#39;ll give you very high level first let me remove everything so just to make sure that you so what I&#39;ll do is first of all I&#39;ll try take only the country okay from dim customer I&#39;ll take only the country okay and dim product I will take only the product category okay here I will take only the year calendar year okay these are my Dimensions the way I see the data or call my Dimensions right these are my Dimensions okay for this country for for this product category for this year what are my sales amount sales amount I can get it from measures right this is the sales amount yaho y now you got it right so for this country for this product category for this calendar year this is my sales amount superb right now to get this information in SQL Server table using some queries now you have to write a query right what if you have to do the same thing in SQL or orle or db2 any database if someone asks that for the for all the countries for all the product categories for all the years give me the sales amount so what you have to do you have to join the tables you to get a quy out of it but ssas is doing by default using its engine so you have done so much of hard work writing that query after someone ask you but he will say that okay now you have given the uh country wise right and you have given the product category wise right just please give it on this product subcategory wise also so just add and add here that&#39;s it if I remove this for accessories in 2003 this is the amount right so what you can do is like if you want to select only for some particular year right so you can have some filters here right so dim time you don&#39;t have any hierarchy right team time what is the year you I want the calendar year is equal to I want the only the sales from 2004 it will make sure that it will display only the 2004 this is kind of a we condition in SQL okay but in ssas this is not called as wear condition this is called as slicing okay this is called slicing of the information okay in SQL it is called as we but in ssas data Dimension model concept this is called as slicing okay so this is the for 2004 data this is a f amount and someone says that okay this is 2004 data right uh can you give it by every quarter this is the query if you have to write that in SQL it&#39;s again you have to modify the query and do it right but in SS it&#39;s very easy okay I can give it every quarter just drag and drop like this same sales amount divided into first quarter second quarter third quarter I&#39;ll show you something so this is the final thing right so the total sales amount will not vary this is the high level sales amount if you split up for the same accessories for the same year for the for the quarter it will actually split the sales amount intelligently right so it will split the sales amount intelligent ently right if you see this calendar quarter automatically the sales amount will be splitted into the different quarters based on the data same Australia same aies same year 1 2 3 first quarter second quarter third quarter it has split the amount into three different parts right earlier the amount was the cost sum of all these things right earlier the amount was all sum of all these things now it has split the things if you remove the things right again for 200 for it it will do a combination 81309 but if I put it 81309 is split up here because this is 40,000 35,000 75,000 plus 5,000 plus so it will become 81,000 right this is the beauty of ssas which you cannot do very easily in SQL Server if you have to do it in SQL server or or db2 you have to write lot of queries right but as a olab tool to give you to give the end user a meaningful information to make better decisions to enable a better platform for the business we will deliver a concept called business intelligence right this is the beauty of business intelligence you have done so much of hard work building the databases you have done so much of hard work building the ETL process but this particular type of things will give more and more and more input to the end user right so if suppose if you are a sales manager right you&#39;re a sales manager of Australia you are interested only in the sales of Australia right you are not interested in anyone what you&#39;ll do is you&#39;ll select the dim customer and you&#39;ll select English country region name equals to and you&#39;ll select only I want the sales of Australia only just select I don&#39;t want anything else right it will use only the Australia now I want to drill down more analysis right I want to do it okay this is for accessories this this is the total sales right for the bik total sale okay what is the subcategory of it uh can you split that this is a question right I can just drag and drop in the accessories in the buy cracks this year this quarter this is a sale right he says that okay uh just can you give me the product name this is the product category this subcategory right just give me the product names these are the product names and the sales amount is still there and sometimes like he will go here and he&#39;ll see okay this particular here it is selling a lot right so okay so let us take let us go to the customer which customer has sold this right which customer has hold this so he will go to the dim customer and you can okay this is the customer right what is his name where is that first name right I think it&#39;s is the last okay so this is the name of the customer so what I can do is I can just put down here right English this is the name of the customer this is the first name and this is the last name of the customer yeah first name and last name of the customer this country this customer in this product category with this product name in the year 2004 in calendar year 2 this is the sales amount super right this is called as multi-dimensional analysis you are seeing the data in multi-dimensional way this is one dimension name is another dimension product category is another dimension subcategory is another dimension name is another dimension you product name right calendar here is another dimension and calendar quarter is another dimension and sales amount is your fact just imagine that if someone is asking you to write a query in SQL server and give the result to me in 5 minutes can you do that you have to join join join so many tables make sure that your query is correct so to develop this particular query a developer will take maybe one day or two day he&#39;ll say okay after one day we&#39;ll give it but after one day the question changes from the manager he says that okay you have given the things by calendar quarter right uh actually can you split it by week I want weekly analysis again you have to modify the query you have to push it to the QA you have to push to the production so many things right so but here what you do okay you want it by week right so what you do is let just go here if you want month example just go here select month just drag and drop that&#39;s it in the calendar quarter second in the June month in the first this is the March month right and sometimes every day okay I want to generate a report by every day can you give the which day what is the sale happening okay let drag and drop this no problem in June on Tuesday there is $34.99 again on June on Tuesday there is $1,700 for a different product so this is a superb report he can generate right to generate this kind of report inq it takes days right so this is the beauty of olap tools and at the same time you are talking only about the sales amount right and in the future he will have AA okay actually my sales amount is coming up okay but I just want to how how many order quantity we are selling this drag drop order quantity and this is order quantity right and what is our unit price for that you want to calculate right so this is the unit price right so he want to select what is the standard cost he says okay so this is important right how we can do the analysis he will do the analysis in this way okay this particular product in this this this month I have sold this product at $34 $34.99 but my standard cost is only 13.08 it means there is lot of margin for me the product which I got it at $13 I sold it at 34.99 the product which I got at 1082 I sold it at at uh 1,700 right the product which I got the product which I got at $6.9 I sold it at 8.9 so it means that there is no loss for me he will check all the the product which I got at $713 I sold it for $120 so this is the product you can do this kind of analysis and if you work in a ba project there is no end for the questions from the users users will have so so many questions okay do this do this do this can you give me this can you give me that especially uh I&#39;m working in a sales related company so they have lot of lot of questions every time so what we make sure is we have we make sure that all their questions are answered in the form of Dimension tables and P tables and we make sure that it is available in the cube anything if they want right okay just go here go to the cube just select this one you automatically get so there is no development effort just you can take some decision or you want to uh host a meeting using this data so that&#39;s okay you can do that right information is already there so this is the beauty of olap tools where other reporting tools or ETL tools or database cannot do it the same concept you can develop using SQL Server queries or Oracle queries but just imagine how much of pain it is and how much of time it will take to execute and different user has different requirement you cannot give just one query and use for one if there are 100 users each user will have different requirement it&#39;s very tough to write those many queries and those queries will be stuck using joints if they have more number of records at the source right to overcome all those problems we have beautiful tools called WAP tools which this is the importance of WAP yeah I hope you got the point here so I wanted to make sure that you understand this concept first and we have so many options right uh if you see look at our module six uh we have so many talk that&#39;s okay so let us understand my my my aim is to to make you understand the overall concept of this and it&#39;s very easy to understand the different concepts later but if you don&#39;t understand this the concept as a startup right then it&#39;s there&#39;s no use of teaching all these things yeah so make make sure that if you have missed something just just uh see the recording again and just make sure that you&#39;re in t right

Transcript for:SSAS Cube Design

Transcript for:
SSAS Cube Design