MSBI Career and Job opportunities.( Microsoft Business Intelligence)

Welcome everyone, welcome to this small introductory video where we will try to understand what kind of carrier does a MSBI developer have What is the prospect of MSBI? Should people take this as a carrier or not? What kind of people can take MSBI as a carrier and how to go about learning MSBI? In order to first understand the job market of MSBI I have opened up this Very famous portal of India, On this I’ll search MSBI Whatever I am doing on this site here, it is focused on India You can very well search on your countries famous job portals I am sure the result should not be too much I have searched MSBI I have only got Two thousand three hundred results Is this MSBI really worth as a carrier or not? MSBI is not such a small market. Its a big chunk of market In MSBI you have three sections one is SSIS, other one is SSAS and the last one is SSRS The Job market is divided into one of these three sections When a company places a requirement he says I need a SSIS developer Sometimes he does not want SSAS and SSRS As a MSBI developer you should be knowing all of these three From the job aspect or from the job prospective Lot of projects just use SSIS or they just use SSAS or they just use SSRS There are some projects who actually use all the three of them If you want to analyse the job market of MSBI then you should be searching all of these three keywords as well Let us search with SQL server integration services Also add SQL server reporting services Also add SQL server analysis services We have 23000 jobs i.e 10 times more I would like to emphasize a very important point here The job distribution between SSIS and SSAS and SSRS They vary in a very huge manner Maximum numbers of jobs are available in SSIS and SSRS. Maximum times you will see that people either want SSIS, SSAS or they want both of them You have very very less jobs in SSAS Because SSAS is used for analysis purpose The jobs which are there in the SSAS they will normally been for bigger companies like JP Morgan Morgan Stanley, Wells Fargo Big financial institutions actually use SSAS while SSIS and SSRS can be use by smaller companies as well You will always find the job market of SSIS and SSRS are large in numbers The job market of SSAS is small in number At the same time we have very less good developers in SSAS That’s how the distribution of MSBI jobs are Let me discusss about this whole job market again has two kind of MSBI developers One kind of MSBI developers I will termed them as configurators. Configurators means they can drag and drop, they can right click, they can go to properties, they understand MSBI They know what every properties does and they can complete a project. This is one kind of MSBI developer who is a configurator who understands basics and can work with MSBI and deliver project There is a other kind of MSBI developer who goes one step above, who says I also know C# and SQL server very well If you know C# and SQL server very well then it adds more value to MSBI There are two kind of people who just know MSBI who just know how to drag and drop, execute the project, complete them And the other one are bit top of it, you can say advance level where they also know SQL server very well and they know C# very well. This job market is divided into this two categories If you see this requirement here where I have searched you can see if I just scroll below this is 5 to 10 years of experience, if I scroll below 5 to 7 years and 5 to 10 years. You will not find fresher MSBI as such means If I could I have searched with numbers of years of experience, 0 years of experience, I don’t think I will get such kind of requirement. In other words the industry expects a MSBI person to be at least above 3 to 4 years of experience that is the requirement Next questions comes for people who are coming newly in this market How should they go about it The first thing for the fresher, for the freshers you should at least try to understand the basics of MSBI, you should be able to understand every property of MSBI and you should know all the three legs of MSBI and you should be in a mode where you can execute the project First basic thing the basic level of MSBI you should be knowing For people who senior for them I think the road map is very clear if you are a SQL server dba I am dead sure MSBI adds value to you because MSBI is a layer on SQL Server For SQL dba this is a very very great path For C# developer it again adds value because in MSBI there are situations where you suddenly need some kind of a custom development For MSBI, C# and VB.NET is a programming language When it comes to customization C# and MSBI people can add MSBI to their list as well For freshers you should at least reach that basic level where you know all of these three technologies how to use them For people who are already in the market, who are in the industry If you are a SQL server dba then this is the right choice for you, you should definitely go for it For C# developer it adds value because when you just say C#, there are 1000 requirements and there are 10000 people When you say C# with MSBI the requirements are 100 then you have 5 people The competition is equivalently less MSBI definitely adds lot of value to your carrier either you are fresher or either you are senior I would like to make a very important point for people who are entering as freshers in the MSBI trade At the basic level once you know MSBI once you know SSIS, SSAS, SSRS how to configure the components, how to use them Then you have to know C# and SQL very very well Just don’t learn MSBI Try to learn C# as well as SQL People who are seniors for them the road map is very clear. I always feel if you are a SQL s dba, it is must that you should know MSBI If you are a C# developer You will one of those knish crowd you will stand where you have extra MSBI added to your series How do we learn MSBI, that we know there is advanced and there is basic The best way to learn MSBI so that you will fetch jobs as soon as possible is by doing a project Don’t learn MSBI section by section, don’t say now I will learn SSIS only and I will learn SSAS Learn it with a project, understand the complete ecosystem how these three things work with each other Thinking from that prospective we have floated a course called as Learn MSBI in 4 Days that means 32 Hours In this In the 32 hours or 4 days of course we have taught MSBI with a project We have taken a project We started doing ETL we started doing cube making, we started doing reporting I would suggest you to follow that video in case you are not aware where the video is Right where this video is playing down we have put that link of learn MSBI in 4 days You can click on that and start learning it right away now I hope this video was useful. In this video we tried to understand what was MSBI, What is a job market what are the various things people look at when you look at a MSBI developer and from where to start Best Of Luck and happy job hunting Thank you so much

Learn MSBI ( Microsoft Business Intelligence ) in 4 days ( SSIS , SSAS and SSRS)

Hello everyone and welcome to Learn MSBI in 4 days with a project end to end. again i repeat welcome to this series of Learn MSBI in 4 days with a project end to end. now each day of this training comprises of 8 hrs of teaching because of person works maximum for 8 hrs. So every day of this training will comprises 8 hrs of teaching so that mean you can also say that this series also be renamed as it can also be named as Learn MSBI in 32 hrs with a project. Now the first questions which will come to your mind is is it really possible to learn MSBI in 4 days in 32 hrs That means i sit on Monday then we have Monday, Tuesday, Wednesday, Thursday and Thursday i am done with MSBI and Friday i can go and do party the answer is “YES” Absolutely YES because even thogh MSBI is joined thing still MSBI is simple it is sweet and i can beat you i can give 100% guarantee nobody gives that in the world i can give you 100% guarantee that if you watch even this one hour video you should be able to work MSBI project. Now before we move ahead let us try to understand to this word BI. Let us try to definition of this word Business intelligence. Business intelligence or BI is nothing but it’s process of converting data into information. So BI again i repeat BI is nothing but converting your data into your information. Now if you look at the English vocabulary of both of this things that’s data information you will understand that they look very much similar but they are very much different. When we say data data is normally technical format For example your data can lie in CSV format your data can lie in sql server, your data can lie in some binary format, your data can lie in excel sheet i don’t know what So data is more of technical format and if you ask simple end user to go and understand this technical format it is very difficult. So you have to take this data you have to take all this technical format and presented to the user in a very user friendly way so that you can understand that data and make meaning out of the data. That thing you know which is where we said we have to make meaning out of the data is nothing but information. So BI is nothing but it’s a process of transforming from your technical data to a meaningful information so that the end user can look at the information and make proper discussion he can do forecasting he can run is business in a more proper manner. Now this journey from data to information involves very important step called as analysis. Now this analysis involves lot of complexity. Because if you look at the data site data can be in different formats you can have data in CSV you can have data in XML you can data in sql server and to go and run the analysis algorithm individually on each one of this data sources is very difficult tds and it is also illogical actually. So the best approach would be to take this data and dump it into some central database This central database is trump as data wire house and later on what we can do is on this central database we will go and run the analysis algorithm. So first part is you have data then this data is dump into your central database called as data wire house and on this data wire house the analysis is run and from that analysis information is generated. Now again the journey from data to data wire house is not so easy it involves lot of processing it involves lot of massing of data. If you see the data part the data is available different different data sources you have XML you have CSV you have binary format etc.. And if you want to go and bring all of this data different different data types into the central database. The first thing what you have is you have to do extraction of data you have to extract data from this data sources and dump up into this common platform in the common data warehouse. So the first process involves extraction the second process involves transformation. Transformation means if you look at the data sources they have different different data type for example must be one of the data sources terms mail as M and female as F and in some other data sources and they are saying mail as 1 and female as 0 so when we bring this different different data type into the common data wire house we need to go and ensure that the data has been transformed into the common format. So that second step involves transformation and the last step involves loading this transform data into data warehouse. So in short the journey from data to data wire house involves extraction transformation and loading. this complete process in MSBI world or i will say rather in BI world trump as ETL. Now once the data has been analyze from the data warehouse. We would like to store this analysis into some kind of data store so this data store structure is trump as a cube and from this cube we display the information or we display this data to the end user as a information. So some kind of reporting software will read this data from this cube and show it to the end user as a information. Now this is the complete BI cycle write from data to information the first thing what we need to is do is we need to do ETL after that we need to do analysis and create a cube auto fit i will talk about this cube structure later on in more details when we look into SSAS and after that we would like to go and display this analysis to the end user in a simplified graphical manner or must be in a tabular manner or whatever is it. Now Microsoft MSBI solution remember this BI word is very generic business intelligence whatever i am discussing now BI so you have both Microsoft as well as non Microsoft solution for it but when you used the word MSBI that means Microsoft Sql Server BI that means you are specifically talking about Microsoft technology here. Now MSBI provides end to end support to the full BI cycle so this BI cycle which i discuss write from data to information MSBI provide full support for it. When you go and create a MSBI project i will talk about the necessary softwares later on but for now you can see see on the screen if you want go and create MSBI project. You are given different different templates here you can see that i have click on this Business Intelligence link and as soon as i click on this Business Intelligence link there are lot’s of templates you can see right which is displayed on the screen. and each one of this templates serve some section of your BI life cycle. For example the ETL section that is the journey from data to the data wire house is done by integration services project. The analysis part in where we run the analysis and we create cube is done by this analysis services template. The Reporting part where we take that massage data or the analyze data so it to the end user is done by the Report Server Project. So all of this three templates Integration Services, Report Server and Analysis Services actually help you to get that full BI automation so this Integration Services also trump as SSIS. The Analysis Services is trump as SSAS that SS stand for Sql Server so Sql Server analysis services. Sql Server integration Services and last one is Reporting Services that is SSRS. So SSIS to do the ETL, SSAS to do the Analysis and SSRS to display that analyze data to the end user. Now we can go and start execution the project before we executive the project let us try to understand what Before we move ahead here is a nice cartoon which is created by my friend Mr Shekher. Look at this cartoon you will understand important s of the each one of this templates and where they are helpful in the BI cycle Now that we know what is role of SSIS what is role of SSAS what is role of SSRS. Now we can go and start execution of project befor we executive the project let us try to understand what project we are going to complete. Now before we start a project we need to have all the necessary tools with us. So that we don’t have any problem while we execution the project. So the first thing is we are going to learn MSBI using Sql Server 2014 which is the latest edition while i making this video. So go ahead and download the Sql Server 2014 Enterprise Edition now you can see other i have not said to download some ordinary edition like express or something i have told you to download enterprise full blown edition because this tutorials is targeted to make you true MSBI professional. So i don’t want loose any kind of installation or any kind of things so just go ahead and download this. This ultimate kind of thing that is enterprises edition and remember that the enterprise edition evaluations period is 180 days which is more then enough for learn MSBI. First thing is you need to go and download Sql Server enterprises Edition install it and the next thing what we the next thing what we need is the Sql Server data tools go ahead and download the Sql Server data tools 2013 When you search on google Sql Server data tools 2013 you will come to this link go ahead and install this Sql Server data tools 2013 Now you must be wondering that why have we install two different tools. first thing that is Sql Server 2014 enterprise edition what you have downloaded is nothing but the actual Sql Server RDBMS. When you install the Sql Server enterprises edition you would get something like this where you can go and create the database where you can create table etc. So the enterprise edition is the Sql Server enterprise edition is nothing but the main Sql Server for storing data creating table writing store procedure etc. And the next thing what you have downloaded the data tools is to create the MSBI project. The hole coding of extraction, transformation, loading, Analysis, Reporting you are going to go and do by using this 2013 data tools When you install 2013 data tools what you get is you get such kind of menu if you are not able locate this what you can do is you can go to Sql Server 2014 and inside that you can find here Sql Server data tools for Visual Studio 2013. Remember in case you are going to use different for the new version coming it will just the Sql Server data tools 2015 or 2016 so one is versions will change here but is other things will remain the same. So you have the database and you have the tools here and if you want to go and create project what we will do is we will do File –>New –>Project and you will click on this Business Intelligent Menu and then you can go ahead and start creating a SSIS project or SSAS project or SSRS project depending on situation what you are working on. So let us first discuss about the project what we can go to execute in this MSBI learning. The project is very very simple customer information system this customer information system get’s data from CSV files This CSV files have to be imported that means it has to be extracted it has to be transformed it has to imported into the system and then from the imported data we need to go and give some reports to end user So this customer data which comes into the system is simple CSV file basically it has customer name remember CSV file means it’s comma separated file it has customer name it has customer amount it has customer code and the sales date in when the customer actually broad the product Let me go and put some data into this CSV file here and this CSV file you can get from the from the site you don’t have type this out so i am going to enter dummy data here let’s assume that this customer code is unique customer code which is coming into the system has to be unique. So 1001,shiv, 100.23,1/1/2010 write in the same way 10002,Raju,300,2/2/2012 So you can see that i have entered like 8 records over here in the CSV and so let me go and save this file. Now the extension of the file is not important so it can be txt or it can be any other extension. But the format of the file has to be CSV format means the internal data representation of the file has to be comma separated so at this moment i will just give the name here as customer.txt the extension does not matter but the format of the file that mean over here it should be comma separated. Basically this customer information system get’s this customer data inside the CSV file and this CSV file has to be imported first thing in the data wire house and then over that data warehouse we need to go and generate the report. So now the first thing is we need to go and create data warehouse where we would like to go and upload this CSV file i am going to my Sql Server 2014 go ahead and open Sql Server 2014 click on this Sql server Management Studio and open Sql Server so let us go to Sql Server let us create a very simple database here called as customer data warehouse and let us give a name to this database as “CustomerDataWareHouse” now the goal is that we need to go and upload this CSV file into our customer data warehouse if you go to the database here we have that customer data warehouse you can see we have the “CustomerDataWareHouse” so now our goal it is to load loads data into data warehouse is by using this CSV file into this CustomerDataWareHouse and if you remember the first stage in our BI life cycle which loads the data into Data Warehouse is by using ETL. In order to do ETL we need to go and create SSIS project. So the first thing is in this customer data warehouse let us go and create simple table so i am going to create a a table here and this table will have 4 fields one is customer code “invarchar” Customer name “invarchar” Customer amount now when we will talk about the financial values like amount or any kind of account financial values we would like to keep that data types that money and sales date when the customer did the sales just put it here sales date and date and time save this i will save this as TblCustomer Let me go and make this as a primary Key so i am going to make this customer code as Primary Key and Save Now you can see here i am getting this error saying that saving changes is not permitted. This one of those error which you will keep getting in Sql Server what this means what this error says that if you want to go and say this changes then the table has to be drooped and re-created which is a danger things he is actually sending out of warning to you so what you can do here is remember this step you will always get this error again and again Tools –>options –>and you need to go the designer here and you need to go and say Prevent saving changes that required stable recreation. But remembers that it’s very dangerous things so don’t try to do this in production but what it means that it will actually go and drop your table and recreated create the same with using this new changes what you have done so i have done with everything our main goal is to load this data from this CSV file into this Sql Server table that’s our main goal so in order to do this remember we said we have to use SSIS to do the ETL we have to use SSIS to do the analysis and we have to use SSRS to do the reporting at this moment our goal is to extract this data from this file and push it to this table so let us go and open Sql Server data tools for Visual Studio 2013 you can get this things from here or if you wish you can get this things from here as well so let me go and click on this so there it is opening so let us click on New Project so you can see that Visual Studio 2013 Sql Server data tools are open i have clicked on –>New Project and let us go to this –>Business Intelligence menu let us click on –>Integration Services remember why integration Services because we want to do ETL you can see this word here ETL now i order to create SSIS project there are two ways one is you can use this from scratch template other you can use the wizard so let us not use the wizard let us try to go and learn the hard way so i am going to go browse over here and create this project in a separate folder all together So let me go and create the folder here saying “CustomerInformationSystem” and select this folder and let me give a name here saying “SSISCustomer” and say ok once new project is created you would see something like this on your screen you would see five tab at the top and you would see this SSIS tool box in case you are not able to see this SSIS tool box what my suggestion is go and click on this SSIS menu and click on SSIS tool box menu so you will see this five tabs and the tool box for now don’t think about this three tabs you can see there three tabs here Parameters, Event handlers and Package Explore don’t think about this three tabs for now concentrate only one the tool box Control Flow and Data Flow so in other words at this moment we will just concentrate on Control Flow, Data Flow and SSIS Toolbox the other three tabs i will talk later as we move ahead again from this two tabs this Control Flow and Data Flow let us concentrate more at this moment on Data Flow tab as time goes by i will definitely explain all the tab but i don’t want to pressurizes on the first time it self with all these concept let us try to complete that project complete that project Complete that project means load that file that CSV file into table so let us try learn less concept here less theory and as time comes will explain you all the tabs don’t worry it’s promise ok. but in case you still want have definition around you this Control Flow is nothing but it invokes the Data Flow so that’s not the full definition but in case some other people they would like to still have some kind of a very mild definition then you can think about this Control Flow invoke the Data Flow so let us go ahead and drag and drop this Data Flow task component from this SSIS toolbox and let us give name here saying “Load CSV”now if i double click on this you can see that it has fallen back to the data flow task so you can see here i am on the Control Flow tab if i double click on it it goes to data flow so in other words you can see that control flow has data flow code in other words control flow invokes the data flow and second one more important point note here is you can see that the SSIS toolbox changes when it goes from control flow to data flow you can see here the SSIS toolbox looks like this when you are in control flow but the time you go to data flow the SSIS toolbox looks different now this data flow tab is the tab in where you go and write the ETL code in other words your extraction, transformation and loading code is all return in this data flow tab control flow invokes the data flow so that’s very simple definition for now and later i will go into more in depth definition as the video moves ahead but for now remember control flow invoke the data flow so when you go and double click on the control flow it goes to the data flow tab and you can see now when you see this data flow tab when you are on the data flow tab and if you see the toolbox you can see that it has that absence of ETL you can see here there are components for source so in other words you would like to first go and extract the data from the source so that is so that is done by all this source component the next thing is you would like to go and do some transformation on the data you can see for that we have the transforms and finally you would like go and load that into the destination. So you can see your ETL thing is seen in this data flow tab here remember this source is for extraction this transform is for transformation and destination for loading all your ETL is done in this data flow tab let us go step by step first thing is we need to do the extraction in other words we need to go and defined the source from where the data will coming right now remember that our source at this moment nothing but it’s simple flat CSV file so you can see that when i clicked on this other sources you can see there lot’s up different types of sources here but for now what we need is we need a flat file source so i am going to drag and drop this flat file sources over here now every component you drag and drop on the data flow task and control flow task the first thing you need to do is you need to go and configure the component now if any component is not configured then you can see the Red sign here like this you can now because i have just drag and drop this component there red sign here indicating that this component is not configured in order to configure the component you need right click on the component and then say edit you need right click on the component say edit now every source component in order to connect to the actual data need something called as connection manager so for example you can see now here you have this flat file source and this flat file source has get connected to this customer.CSV so in order to connect this component this actual CSV source to the flat file source we need something called as the connection manager so let us go and create connection manager here so i am go to click new here and let us give a nice name to this connection manager here so let us a give a nice name like CSV Connection and some small description here so that whenever some other developers read this they would understand what is the use of this connection manager and next thing is we need to go and give the file name so at this moment our file is located this file is located customer.txt is located on the C drive so i will go and browse into the C drive and i will say customer.txt right there it is the flat file connection manager has lot of the flat file connection manager has lot of options you can see code page locale etc right don’t get distracted