In this article we will learn SQL Server MSBI Step by step.
Step 1 :- Get SQL Server Enterprise edition
Step 2 :- Extract the ISO
Step 3 :- Choose edition
Step 4 :- Select features
Step 5:- Set credentials to services
Step 6:- Install SQL Server data tools
Step 7:- Check if Everything is proper
Step 8: - Start Learning MSBI
Learn MSBI Step by Step Part 1 (Installation)
In this article we will learn SQL Server MSBI Step by step. But before we start learningSQL Server MSBI we need to install SQLServer and SQL Server data tools. SQL Server is the RDBMS and
SQL Server data tools is the development environment or you can the tool by which you will create MSBI projects.
So the first step is to get both these tools.
So the first thing is to get SQL Server enterprise edition.Now before we move ahead let me stress the fact that SQL Server comes with various versions express, standard and enterprise. For
MSBI we need to install the enterprise edition.
Note: - MSBI does NOT work with express edition.
So hit google and search for SQL Server enterprise edition as shown in the below figure.
In that you should get this https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2014
which has a ISO file. ISO file like a ZIP / Archive file. Now before you proceed with download you need to login with your Microsoft account. Microsoft accounts are free so go ahead and
register for the same.
There after depending on your system architecture which it is select 32 or 64 bit. In order to check if your operating system is 32 or 64 bit do the following:-
- Click on computer properties.
- If you see system properties as 64 bit its 64 bit or else its 32 bit.
After selection of bit type then select desired language in which you have to download setup ISO.
And then your setup will begin to download in your local system in ISO format. Now sit back and relax as it is 3.0 GB of download which will take time to download with average internet
Once ISO file is downloaded in your system extract that ISO file to the folder using ISO image extractor tool here for demonstration we have used Power ISO toolwhich you can easily download from its website.
Once you have extracted it to folder then open folder where the file has been extracted and then click on setup à installation window will open where you have to click on Installation à Now
SQL Server stand-alone installation for fresh SQL Server setup.
You will then be popped with a screen where you need to put which edition you want to install. Select evaluation and not express. MSBI does not work with express edition
With Evaluation version you can install full SQL Server for limited 180 days period.
If you have SQL Server product key then enter the same and install full version on your system.And then press Next.
Once you are on Licence Terms page tick on check box to Accept licence terms. And then press Next.
In order to proceed for further installation steps it should pass all operation if not then Warning, Failed or Skipped message will be given. Once operation is completed successfully click
If you want to have updates from Microsoft automatically then check the box. Please note this step is marked as recommended by Microsoft. For any bug or software related issues if
present will be resolved whenever updates get released.You can still proceed without marking check box and can clickNext.
Here before proceeding to installation process it is checking for the problems that might occur in failure case you have to correct it before proceeding for installation and if it passes with
warning then can be continued by clicking Next.
Here in this window you can do the installation with three different options,
- SQL Server Feature Installation
- SQL Server Power for SharePoint
- All Features with Defaults
We will select third option which will install all features with defaults so that we can work without any issues as all default features will be installed.
Once “All Features with Defaults” option is selected you can see Feature Selection has by default checked all feature which will get installed in the system. In case you are not selecting
defaults then do ensure you select checkboxes for reporting service , integration service and analysis service.
We would encourage to select all features as you are new in to MSBI and we do not want problems while we are doing the MSBI labs.
Once Feature Selection is done then click Next. Once all Feature Rules are passed successfully as shown in the following screen then click Next.
Under Instance Configuration you have to select Default instance if SQL Server is installed for the first time or if you have to create Named instance then that also you do itonce the instance
is decided and then click Next.
When SQL Server runs it needs lot of services and these services need credentials to run. So the next screen lists lot of services and under which account they will run. For now do not get
disturbed with this screen as you proceed in the tutorial we will see what each services do.
Here separate account has been used for each SQL service so whatever you see as default under Account Name keep it as it is. In case if you want to use some specific account you can
mention it accordingly and then click on Next. We have kept this windows each account values as default only.
Similarly in Database Engine Configuration select the Authentication mode here by default option is set to Windows Authentication Mode so keep it set to default mode unless you require
mix mode for Authentication one is SQL Server Authentication and other is Windows Authentication mode.
Windows authentication means the users will be stored in windows ADS or user groups while mixed means user credentials can be stored in Windows ADS or in SQL Server itself.
After you set Authentication mode add user account to Database Engine Configurationas shown in the following image and then click Next.
Following is the screen for Analysis Service Configuration where you will Server mode one is Multidimensional and Data Mining Mode and other is Tabular mode. We will keep server mode
set to default which is Multidimensional and Data Mining Mode.
When we do labs in SSAS we will try to understand what these server modes means.
After you set Server mode add user account by clicking to “Add Current User” to Analysis Service Configuration as shown in the following image and then click Next.
Coming next is Reporting Service Configuration where installation of report server will be done in native mode. Here there is also option available to configure Reporting service along with
installation. In order to keep installation simple we will only do the installation and later we will configure reporting service.
Once Install only option is checked then click Next
Now in Distributed Replay Controller add user account by clicking to “Add Current User” as shown in the following image and then click Next.
Now in this installation window give a suitable name here we have given as “MyController” in Distributed Replay. Also for now keep Working and Result Directory default as it is only and
And now it is time for the final screen which shows that finally SQL Server 2014 is ready to install on your system with details of installation and the path where SQL Server 2014 is going to
So till now we have finished installing SQL Server. But we can do MSBI just with SQL Server we need some tools for the same. These tools are names as SQL Server data tools or in short you
can also term it as SSDT.
Go to www.google.com and search for SQL Server Data tool for Visual studio 2013 and download it from Microsoft website.
Once you are on this link http://www.microsoft.com/en-in/download/details.aspx?id=42313 download
SSDT VS 2013 using “Download” link.
Once the file is downloaded to your local system then extract file “SSDTBI_x86_ENU” file to your local folder as shown in the following image.
After you complete extraction do double click and open SETUP file where you will see following screen and then click and Installation à New SQL Server stand-alone installation.
Once you click on New SQL Server stand-alone installation it will open new window as it is shown in the following image snapshot and in this new window if you want to have updates from
Microsoft automatically then check the box or even you can still proceed without marking check box and then click on Next.
Please Note:Now following steps of installation will be similar to what we did during installation of SQL Server 2014.
As it is a part of SQL Server 2014 installation so it will also check installation rules as you can see In the following window where if all operation are passed then you can proceed with
installation. And then click Next.
In case of operation failure resolve all issue and then proceed further for smooth installation.
In this window accept License Terms and by marking the check box and shown in the following image in order to proceed for further installation.
Now comes the important window which is of Feature Selection, where we find SQL Server Data Tools – Business Intelligence for installation as shared feature, mark on the check box and
proceed with Next.
Once you select SQL Server Data Tools – Business Intelligence then under the new window ofFeature rules it will check the rulewhether BI can be installed or not like for examplein order to
install SQL Server Data Tools – Business Intelligence the .NET Framework requirement is of 4.5.1 if your system contains that then it will directly install and show Installation Progress as you
see in the following screen or your installation will fail until you install .NET Framework requirement 4.5.1.
Once installation progress is finished without any error then installation of SQL Server Data Tools – Business Intelligence is successfully finished which installation screen will show screen
So now that all things are set let’s check if MSBI is working properly or not. Click on Start Menu --> All Programs --> then click on SQL Server data tool for Visual Studio 2013
and select Business intelligence node and see that you see SSIS, SSAS and SSRS templates. If you are able to see those templates that means MSBI is up and running and you are all set to learn MSBI.
For each template of SSIS, SSAS and SSRS you will see sub-template to create project with and without wizard. With wizard you do not need to create project from scratch where as in without wizard you have to create project right from the start.
So now that you have SQL Server and the data tools its time to start learning MSBI. In part 2 of this series we will see What is MSBI and simple project with SSIS.
If you want to have kick start you can see the below YouTube video Learn MSBI in 32 hours.