Lab 1.1 MSBI Installation in Details (Upgrade Lab)

MSBI Step by Step Training Lab 1.1 MSBI Installation in Details (Upgrade Lab)
30 minutes
Share the link to this page
Copied
  Completed
In this video we have explain how to install and setup MSBI.

Transcript

Welcome to lab 1.10. Why such a weird number in between? And why not lab two because this is an upgraded lab. This is an upgraded lab, which I've just recorded recently. So what are upgrade labs upgrade labs are those labs you know, which I've regularly inserted in between of the main labs, you know, to ensure that the new changes in msbi is incorporated in this course. So, whatever are the new features or changes, you know, you are aware of it.

And this also ensures that you can smoothly take this course and also know that what new features are coming in. So, this lab is a 30 minutes lab which only focuses on msbi installation, 30 minutes lab only on installation, because I can feel the pain of my students, my friends who are trying to take this course out, and I want to ensure 1,000% that you should be able to complete this course. So, I've heard everyone very clearly the MSB is installations are problem, I read that and just 30 minutes lab is dedicated for the same. So this this 30 minutes lab, I'm trying to go through every step of the installation. To ensure that you can take up this course smoothly, I understand that the msba installation is complex, or I'll say this weird, you know, because of so many players playing into it, right. So let's make sure together we install things properly.

So to start with, I want to stress on three important points and then we will go into in depth of the msba installation process. Point number one, this course was recorded in 2014 using the SQL Server 2014 version, and later in 2016, I gave updates around the new tablet features of SSIS and I'm still recording some new features in 2017 as well, or all the way msba works has not really changed but, but the installations have changed dramatically and it is such a big change that I started I'm getting complaints that people were not able to start the course because of the installation. So this is a dedicated prerequisite video to make sure that you can follow this course properly. Point number two. This prerequisite video I have recorded on November 2019. And just recently SQL Server 2019 RC has been released.

I would personally suggest to avoid SQL Server 2019. As I can see, it's very very buggy, extremely buggy when it comes to the msbs section. I see so many defects and with all the specs showstoppers that rather than learning you'd be losing time and stamina on installation. Please use SQL Server 2017 I repeat, please use SQL Server 2017 version for this course. So that you do not face issues and and just you know adding to it that is no 2008 Team released as such, I think after a year once SQL Server 2019 gets stable, I will try to provide an upgrade for it. But for now, I'm making it extremely clear that I will not be providing help if you come with SQL Server 2019 issues, at least for the coming one year.

So point number two, please use SQL Server 2017 version four discourse. Point number three. To complete msbi installation, we need four setups. First is we need SQL Server 2017 Developer Edition. Second one is we need SQL Server Management Studio. Then we need the Visual Studio Community Edition.

And then we need the SS dT 2017. And all of these setups, two things have to be noted. First one is they should have the same version number. So for example, if you're installing SQL Server 2017 ensure that the Management Studio the Visual Studio Community Edition and SSI You all have to be in the same version that is 2017. And the next thing is the sequences, you will first install SQL Server then the Management Studio, then Visual Studio, and then SSgt. So you need to follow the sequence.

So point number three, we need four setups for completing the msba installation. So step one, let us get first the SQL Server 2017. Developer Edition. This is very essential. It has to be a Developer Edition. I'll tell you why.

So once you go and type on Google SQL Server 2017 Developer Edition, you will go to this SQL Server downloads. Please note, you shouldn't be installing SQL Server 2019. Right. Please try to install 2017. So I'll go to SQL Server downloads here. You can see lot of flavors of SQL Server like we have the 2017 on premise in cloud Express Edition, but Our interest is the Developer Edition why because this Developer Edition is free the full featured is free, you can use it for development you can learn it, but it is for non production in other words for production you need to go and use you know you need to go and buy the license right.

So, this is you know, this will help us to, we will not have any kind of evaluation period, you know, so we can just learn it unlimited Li free with all full featured right, one of the thing is that it is not for production, which is okay right. So let's go ahead and say Download Now once you do that you can see down below, it will start downloading that setup. So the first thing is get SQL Server 2017 Developer Edition installation. So once the Developer Edition has been downloaded, go ahead and run the installer will go ahead and go ahead and run the setup. Once you run the setup the first screen which will which will pop up is that what is your installation types Do you want to do a basic installation? Basic installation means you know it will do some default installation.

Second one is custom and third one is that it will actually go and download the SQL Server set of files right the ISO files. But for now, we want to go ahead and click on custom please don't select on basic because in basic by default it does not select the The msba features right. So, we need to go and do one custom installation. So click on this and then it will go to the next screen. In the next screen what it will do is it will try to download all the necessary packages what is needed for the installation. Now please note that when this downloading happens automatically he also starts installing some prerequisite.

So it is very possible that you can get you know you will be prompted to restart the computer please go ahead and restart the computer. You know if the SQL Server setup is wanting so Once you are you have completed in a with the with the basic prerequisite right it will pop up this screen this is termed as the SQL Server installation center. So from this you know, you will actually go and install you know whatever SQL Server things you need. So you can see at the left hand side menu it has planning, installation maintenance, right. But at this moment our our focus is that we want to do installation. So, already the prerequisite has been installed in the previous screen.

So, go ahead and click on this installation. Once you click on this installation, right, you will you will have a lot of things out here you can see new SQL Server standalone install SQL Server Reporting Services and so on. Click on this new SQL Server standalone installation. Right so click on this and you will be prompted with this kind of bizarre in this wizard you know it will actually tell you that this is in three edition and it is in Developer Edition. So Go ahead and select that Developer Edition and then click on next you don't need to put the product key why because this is Developer Edition it is for non production it has the full feature as we discussed previously right. So and then you click on next in the next you know it will actually go and start setting up some rules and let it install the rules This is like a default activity Do not try to interrupt in between Do not try to hit cancel and so on right.

In case it gives you some warnings or errors of Windows Firewall, please ensure that the necessary access the setup has you can see at this moment I am seeing I'm seeing some warnings but I will just I can just neglect those warnings at this moment right. But in case you see some errors, you need to actually go and attend it and ensure that those errors are fixed. And after this, it will say that okay, because you selected custom installation. What things do you want to install? Right? So the First thing is we want the core SQL Server, the core SQL Server means by which we can go and create the tables, the databases and so on.

So please click on this database engine services. So this checkbox has to be selected, please select dq s, this dq S is one of the features, which is that in SSIS, and we'll be talking about this we want to do SSIS as well, you know, so, remember mSv has three things you know, one is it has SSIS. One is it has SSIS and SSRS that I will be covering in the coming up lab. But in the installation, we need to go and select this by default this is not selected. Also go and scroll below. So if you go and scroll below, ensure that you install data quality client and surely install integration services as well.

Right. So please note, we need to select five checkboxes at least database engine services dq s Analysis Services Data Quality client and integration services, right. So these things are there. So that says so now, then hit on next, once we hit on next he says that please give a name to this instance. So basically whatever installation you are doing, please give some name to it. So I'm going to just go and say, default instance, this is my default name, MS SQL Server.

So this is the default instance whenever I connect, I will be connecting to this instance. Now SQL server has a lot of things, you know, it has, you know, the SQL Server agent, it has the engine, the SSIS, SSIS, SSRS, and so many things, right? So here you can see that it tells us that tell me under which account you want to run all these services right? For now, if you're not getting an errors, you know, just keep it as it is. And just click on Next. Now, SQL Server security works in two modes.

One is the Windows Authentication. And another one is the SQL Server authentication. So Windows Authentication means it will use your concurrent windows username and password, and it will try to log into SQL Server. SQL Server authentication or mixed authentication means it will use Windows Authentication as well as you can give your own username and password into SQL Server. So for now, we will go and select Windows Authentication. So select Windows Authentication and say add the current user.

This add the current user what it does is which with whatever username and password at this moment you are logged in into your operating system, it will be picked up. So ensure that the current user has all the rights for installation the current user has all the rights to access SQL Server. Now, this is a very important screen. As I've said that SQL Server msba has three things SSIS SSIS and SSRS in SSIS, you know there are two things There are two kinds of ways you know by which you can go and create cubes, one is the multi dimension and the other one is the tabular. So, we need to have both of them because in my course, I have covered both of them I have covered the MDX version that is multi dimension as well as tabular. So, in my course, you will whenever you see this word tabular that means that I am using this feature of SSIS whenever you see a normal like multi dimension, you must be seeing this one right.

So, at this moment, first let us select tabular because you need to create two different instances for tap for both of them. So, tabular and multi dimension cannot run on the same SQL Server instance. Let me repeat that tabular and multi dimension cannot run on the same instance itself right. So, first thing what I will do in this instance, I will install tabular right against add current user and once you are done it will actually pop up a preview screen In saying that, so these are all things you have selected. Are you sure you want to do the installation? I will say yes, I am sure.

And you will click on Install and it should then go and start installing the SQL Server. SQL Server necessary features right now as I've said that SSIS has two modes of running one is the tabular mode and one is the MDX or the multi dimension mode. So when we ran the SQL Server setup, first, we actually installed tabular. So in order to install MDX, we have to rerun the setup, we have to install a new instance and select MDX so the steps are all seen. So you can see I have again read on the SQL Server installation. And now I will go here.

So I'm going to go and click on installation. And I will say new SQL Server standalone installation. So that it is I'll see you next see next Next, and now I'll say perform a new SQL Server 2017 installation. I will say it's a free edition. And I will accept the license terms you will go and you will select, you know, all those things, you know, which we selected previously. So dq s integration services flying tool SDK, so I've just selected the, you know, the same things, you know, which we have previously, I would like to install in my D drive this because I don't have space in my C drive.

I'll say next. And I would go and give a name instance out here saying that this is multi dimension right name instance. Let's see You must be I can see multi dimension right multi dimension answer next. And now this time you will go and you will select MDX. So, this is their This is Windows Authentication at the current user. So, at this moment if you see the current user I'm logged in is as Dell and now, I will go and I will select multi dimension and I will go ahead with the setup.

So, for SQL for SSIS in order to complete the labs, we have tabular and multi dimension just ensure that you read on the setup and you install the instances of multi dimension and Tableau differently. Why because the mode the way Tableau And the multi dimension work the way that architecture is, the way the structure of storage is completely different. That's why Microsoft has given complete different instances, you know, so that there is no confusion. Oh, we have installed the SQL Server Developer Edition. The next thing is you need to go and download the SQL Server Management Studio. The SQL Server Management Studio is nothing but it is a nice UI, which helps helps you to manage SQL Server create tables do backups, and lots of activities you know for which you would be writing lot of SQL scripts, you know, it helps you to do it in a very visual way.

So let us go ahead and download SQL Server Management Studio. So if you Google for SQL Server Management Studio 2017 for SQL Server Management Studio, I can I can be bit lenient, actually you can have 2017 and above. Right. So for SQL Server Management Studio, I know that in the future PDF said very strictly that use the version which is in sync, right? But for monuments today, I can say that yes, you can have the current version or a higher version. So in other words, for example, for 2017, do not use SQL Server Management Studio of 2014.

But 2017 also 2019 we'll do right. But for now, you know, just for safety sake, please stick to 2017. So go to SQL Server 2017 out here and you should find, sorry, SQL Server Management Studio 2017. Yes, that is ssms. People also call this as ssms. And you can see a download ssms and down below, it will start the download on my browser, right?

So there it is. ssms installation is pretty straightforward. So you don't really have to do anything. One thing you know, so once you run the setup, it will just say next, next, next, and at the last probably it can ask for a restart right So, that was ssms that is the next thing. And if your SQL Server Management Studio is successfully installed, then you should see such kind of link in your such kind of installation in your computer. You can see I have SQL Server Management Studio 18.

So if I click on this I can quickly check. So that's a Management Studio. So, this Management Studio will actually go and get connected to your SQL server which you have installed. Please note, we have done two installations till now right. So one installation is this one. This will SQL Server Core engine Developer Edition right.

So, this guy what it will do is it will actually go and connect to that development developer engine the Developer Edition engine. So you can see out here if you browse, you will see that this is a database engine. You can see that one is the the core SQL Server instance you know which has tabular the second one remember, please I have made a spelling mistake. This is MDX So, you can see this is a tabular This is MDX two different instances I had installed this one in my computer I also have 2019. Right. So if I do an okay, if I do one Connect, you can see that it's no my database is that it is running.

I can also go go and quickly check you know if the versions are right or not. So what you can do is you can just see it select at the rate at the red version to just quickly check what kind of SQL server version you are running or Developer Edition or what you can see, this is 2017 edition which are running right. Good. So we have installed the Developer Edition we have installed the Management Studio. The next thing is the Community Edition 2017. Now this one and the SSD, that is SQL Server Data Tools, both of them have to be of the same Same version, you cannot make a mistake here right?

So let's go back to Google again. Let's search. Visual Studio Community Edition. So Community Edition is free. You know, you can use it you can test your application and so on. So you can see within studio Community Edition download.

Please note that when you go on download, it always tries to flash the newest version so you can see it is showing 2019 do not make a mistake of downloading this if you are going to go and download this. Again you will have the versions which are not in sync right please try to hunt for 2017 so as you can see on the screen on the main homepage, you know they have they have not what we call they have not displayed the 2017 download you can see Visual Studio download older edition So I can try for this one So download older Yeah, that's what it is. So 2017 I will expand this and Visual Studio 2017 and other products Community Edition Yes. So I can probably go and download this you can see that is 2017 so when I click on that it actually takes me to a page in a where it asks me to sign in.

So please go and sign in. So must be I can so it's asking me to sign in. So if you see out here, let me pick up this login which is already I have go ahead and create a login into the Microsoft live.com. So that is if you sign in, you should get the older version of Visual Studio. So that it is my Visual Studio Oh Okay that I should use that other accountants are sorry. So sorry for this guy's this the this, what do you call this access is free you need to just go and register yourself on my dot visual studio.com sign up I should have logged in with that other account in a where I have the access.

Please note again that these accounts what I've created our free accounts so you don't have don't have to pay anything for the Community Edition. Okay, so there it is. So you can see now I have logged in with this she bought so it's a free account on my dot visual studio.com and if I search for 2017 out here that's a team Explorer, and that's the Community Edition you need to download. So guys, please ensure that you download Visual Studio 2017 and not 2019. For now, this is very important. What Microsoft does is whenever it releases the new version of Visual Studio, it removes all the versions of Visual Studio currently from the main homepage.

So what happens is, you know, when people come to my course, you know, they just go online, they search for Visual Studio, and they just install it. So now you have SQL Server 2017 Developer Edition, and you have Visual Studio 2019. And then suddenly things don't work, right. So that's the 2017 download. So go ahead download it, when you're downloading this 2000 when when you go and download it and when you start installing 2017 one very important thing, what you need to do is you need to go and do the following selection you can see here when you run your Visual Studio Community Edition Unit Two Go to your data storage and processing, you can see that you have to go and check on this and ensure that you install SQL Server Data Tools. This is the most important part of your installation Visual Studio.

You should check this checkbox. So when you when you run Visual Studio Community Edition, select data storage and processing and click on this SQL Server Data Tools and do the installation right. So that was a second part. Third part I'm sorry. So we have installed we have installed SQL Server 2017. We have installed the Management Studio, we have installed the Community Edition.

And we have also clicked on that checkbox saying that the data warehouse tools solid the SS dt is installed that is SQL Server Data Tools. Remember SS DT SQL Server Data Tools ssms SQL Server Management Studio rights These are some of the acts on him, you will keep hearing in the SQL Server world. Now the last thing which is pending is the data tools. Again, this has to be very, very cautiously done. Do not install SSG 2019 right. So let's go back again to visual let's go back online and that search for SQL Server Data Tools, SQL Server Data Tools 2017 please ensure that you will download SQL Server Data Tools 2017 so SS DT we can see again be very very clear that you are not installing 2019 right so SS DT so that it is we can see SS DT stand alone installer right.

So this one looks to be SPR so that it is SS DT for vs 2017. Remember That SSD T goes in sync sync with Visual Studio. So must be the Google Keyword what you'd like to search is SQL Server Data Tools for Visual Studio 2017. That's a good way of searching it right. So SSgt for 2000 takes to the same link. But that it is SSD t standalone the same link it is.

But please ensure that you see this word SSD t. So you can go and you can download this. Now once you start installing SSD t, then it says that okay, to which Visual Studio instance, I should go and install the features of SSD T. Remember SSD T and Visual Studio is there to create projects to write code, right. So once you have SQL Server when the deployment will take place, this will Server Management Studio for doing all that backup and so on. And the SSD and the Visual Studio is to create projects, to write code and to do all that ETL and so on. Right So if you go and if you see, when you run your SSD T, one of the very important things it will say it will pop up is that to which version of Visual Studio you want to go and install the SS DT tools.

You can see it at this moment in my computer. I have Visual Studio Community Edition 2019 as well as I have 2017 so please ensure that you select 2017 and you install the SQL Server Data Tools into that edition. Right. So once you are done with this, you are almost ready with msbi right? So Developer Edition, Management Studio, Visual Studio Community Edition 2017 and SSgt should be installed targeting this Community Edition, right. And once the installation is done, you can go and you can check so I'm going to go to my Visual Studio 2017 and inside this I should be able to now create projects Using the SQL Server Data Tools.

So if I go here you can see now if I want to go and create an SSIS project, there it is, you can see business intelligence and integration services. If I want to create an analysis services project, there it is, I can create both multi dimension and tabular. And if I want to go and create an SSRS project, there it is, right. So all the things are here. And the other parts of the course absolutely remains same, right. So, I hope that you enjoyed this video in this video, my main goal was that how to go ahead and do the installation of msbi.

I do understand the msbi installation is complex because of so many tools it has to work with, right? It has to work with SQL Server, right? It has to work with the Visual Studio, which is again a different thing. And it has its own templates so it has to also manage that part. So that's why the installation becomes complex. I hope that Microsoft really simplifies this and makes it a one click.

But at this moment, this is what the situation is. So, in case if you have any questions you know about installation, you can send me a mail at questpond questpond.com. I'm more than willing to help you. The other parts of the course absolutely remains same. So from lab two onwards, you shouldn't be having issues if you follow this installation process. Thank you so much happy learning.

And I hope that you will complete the msbi lessons in an easy way. Thank you.

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.