SQL Server Components, Tools and Objects

SQL Server Components

SQL Server Database Engine

The core service for storing and processing data.

SQL Server Database Engine includes the Database Engine, the core service for storing, processing, and securing data, Replication, full-text search, and tools for managing relational and XML data.

The Database Engine also features these components

  • Full-Text Search
  • Service Broker
  • Replication
  • Notification Services

Analysis Services (SSAS)

Tools for creating and managing online analytical processing (OLAP) and data mining applications.

Reporting Services (SSRS)

Components for creating and deploying reports.

Reporting Services includes server and client components for creating, managing, and deploying tabular, matrix, graphical, and free-form reports. Reporting Services is also an extensible platform that you can use to develop report applications.

Integration Services (SSIS)

Tools for moving, copying, and transforming data

Integration Services is a set of graphical tools and programmable objects for moving, copying, and transforming data.

Integration Services (SSIS)

SQL Server Management Tools

SQL Server Management Studio

SQL Server Management Studio is an integrated environment to access, configure, manage, and administer components of SQL Server. Management Studio lets developers and administrators of all skill levels use SQL Server.

SQL Server Configuration Manager

SQL Server Configuration Manager provides basic configuration management for SQL Server services, server protocols, client protocols, and client aliases.

SQL Server Profiler

SQL Server Profiler provides a graphical user interface to profile and trace an instance of the Database Engine or Analysis Services.

Database Engine Tuning Advisor

Database Engine Tuning Advisor helps create optimal sets of indexes, indexed views, and partitions.

Business Intelligence Development Studio (BIDS) – deprecated

The Business Intelligence Development Studio is an IDE for creating Analysis Services, Reporting Services, and Integration Services solutions.

Connectivity Components

Installs components for communication between clients and servers, and network libraries for DB-Library, ODBC, and OLE DB.

SQL Server Database Objects


Tables are the main form for collection of information. Tables are objects that contain all the data in SQL Server databases. Each table represents a type of object that is meaningful to its users.


A view can be thought of as either a virtual table or a stored query. The data accessible through a view is not stored in the database as a distinct object. What is stored in the database is a SELECT statement. The result set of the SELECT statement forms the virtual table returned by the view. A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced.


An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a B-tree structure that enables SQL Server to find the row or rows associated with the key values quickly and efficiently. There are clustered and non-clustered indexes.


A trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure. As a matter of fact, triggers are often referred to as a “special kind of stored procedure.”The main difference between a trigger and a stored procedure is that the former is attached to a table and is only fired when an INSERT, UPDATE or DELETE occurs. You specify the modification action(s) that fire the trigger when it is created.


Stored procedures in Microsoft SQL Server are similar to procedures in other programming languages in that they can:  Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.  Contain programming statements that perform operations in the database, including calling other procedures. Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure). You can use the Transact-SQL EXECUTE statement to run a stored procedure. Stored procedures are different from functions in that they do not return values in place of their names and they cannot be used directly in an expression.


The primary job of a constraint is to enforce a rule in the database. Together, the constraints in a database maintain the integrity of the database. For instance, we have foreign key constraints to ensure all orders reference existing products. You cannot enter an order for a product the database does not know about. Maintaining integrity is of utmost importance for a database, so much so that we cannot trust users and applications to enforce these rules by themselves. Once integrity is lost, you may find customers are double billed, payments to the supplier are missing, and everyone loses faith in your application.


A rule specifies the acceptable values that can be inserted into that column.

How to create and use analytics reports with Power BI

Short story about Power BI

You see all the cool stuff that Power BI has to offer but you don’t really know what it is? Where do you really get started? What all it offers?

It is visualization tool to create stunning reports and dashboards to help you gain insights in your business. And to make business decisions.

In today’s world of fast changing trends and urge to make positive but creative business stories, sometimes it is overwhelming to gather all the data, go through, analyze them and figure out what would be the most optimal business decision for your company.

Power BI is the tool that visualize data you have and helps you to understand them better, but also to notice some trends within your work flows that are hard to see in Excel sheet. After all, human beings are visual creatures. Most of us process information based on what we see. 65 percent of us are visual learners, according to the Social Science Research Network.

There are 3 different pieces:

  1. Power BI Desktop – Free desktop app that offers you the most functionalities. Within just a few clicks you will get a bunch of visualization that will serve your business.
  2. Power BI Service – Cloud service in the Microsoft Cloud offerings. The point of the service is to drive and enable sharing of collaboration. It works as well in and out of your organization. You can have groups of people and share dashboards. Also, with the possibility of giving rights to ones who may or may not see particular visualization.
  3. Power BI Mobile App – It is possible to use all mentioned serviced on your phone, tablet or other devices wherever you are. Furthermore, there are other tools to help you while being mobile, like sending alert or annotation.

Okay, now what? What’s the first step?

Data (Sources and Connectors)

First step is to download Power BI Desktop, upload relevant data and create your first visualization: dashboard or report. With Power BI Desktop, you can connect to data from many different sources.

Data types are organized in the following categories:

  • All
  • File
  • Database
  • Power BI
  • Azure
  • Online Services
  • Other

Each of mentioned data types provides the data connections. For example, the File category: Excel, Text/CSV, XML, JSON, Folder, PDF, SharePoint Folder;

And the Database category: SQL Server Database, Access Database, SQL Server Analysis Services Database, Oracle Database, IBM DB2 Database, IBM Informix database (Beta), IBM Netezza, MySQL Database, PostgreSQL Database, Sybase Database, Teradata, etc.

The Power BI team is continually expanding the data sources available to Power BI Desktop and the Power BI service. For now, there are more than 250.

Query definition

When working in the Query Editor window of Power BI Desktop, there are a handful of commonly used tasks.

The common query tasks are the following:

  • Connect to data
  • Shape and combine data
  • Group rows
  • Pivot columns
  • Create custom columns
  • Query formulas

You can edit the steps that Query Editor generates, and create custom formulas to get precise control over connecting to and shaping your data. Whenever Query Editor performs an action on data, the formula associated with the action is displayed in the Formula Bar.

Data Modeling

Data Modeling is one of the features used to connect multiple data sources in BI tool using a relationship. A relationship defines how data sources are connected with each other and you can create interesting data visualizations on multiple data sources.

With the modeling feature, you can build custom calculations on the existing tables and these columns can be directly presented into Power BI visualizations. This allows businesses to define new metrics and to perform custom calculations for those metrics.

Data Visualization

Visualizations (known as visuals for short) display insights that have been discovered in the data. A Power BI report might have a single page with one visual or it might have pages full of visuals. In the Power BI service, visuals can be pinned from reports to dashboards. There are many different visual types available directly from the Power BI Visualizations pane.

It’s important to make the distinction between report designers and report consumers. If you are the person building or modifying the report, then you are a designer. Designers have edit permissions to the report and its underlying dataset. In Power BI Desktop, this means you can open the dataset in Data view and create visuals in Report view. In Power BI service, this means you can open the data set or report in the report editor in Editing view. If a report or dashboard has been shared with you, you are a report consumer. You’ll be able to view and interact with the report and its visuals but you won’t be able to make as many changes as a designer can.

Consume and share

Next step is to publish that visualization from Desktop to the Cloud – Power BI Service. Of course, if you are worried about your data – there are ways to publish visualization, without publishing your data.

From all these visualizations, one can make dashboard that collects all singular visualizations within the organization to give you visual overview.

After creating and publishing wanted content, it’s time to share it with particular colleagues or groups of them in or outside your organization. And real time collaboration starts.

To be part of this collab, people you share visualizations with can use their private email addresses. Power BI Service is user friendly even for newbies who don’t have IT background, which is important because it really gathers experts from all departments in organisation. This is particularly important for analytics departments that create relevant analytics and reports, and then share them across the company.

Apart from consuming the mentioned materials, one can set different access rights for collaborators. Row-level security (RLS) with Power BI can be used to restrict data access for given users. Filters restrict data access at the row level, and you can define filters within roles. This is a practical tool for larger companies, especially ones that have more departments or operate in multiple countries.

Power BI Desktop is a versatile tool that supports four development modes:

  • Live Connection
  • Import (cached)
  • DirectQuery
  • Mixed

Live Connection is mode to develop a report that directly queries an existing data model. With it, one can exploit existing data assets and allow connecting to the base model or a perspective. Also, measures can be added to the report.

One of the features that will be interesting for some companies is that the user’s identity is passed through to enforce role permissions. A great benefit of Live Connection is definitely automatically dashboard tiles update.

Import mode is the most common mode used to develop models. This mode delivers extremely fast performance thanks to in-memory querying. It also offers design flexibility to modelers, and support for specific Power BI service features (Q&A, Quick Insights, etc.). Because of these strengths, it’s the default mode when creating a new Power BI Desktop solution.

It’s important to understand that imported data is always stored to disk. When queried or refreshed, the data must be fully loaded into memory of the Power BI capacity. Once in memory, Import models can then achieve very fast query results. It’s also important to understand that there’s no concept of an Import model being partially loaded into memory.

DirectQuery mode is an alternative to Import mode. Models developed in DirectQuery mode don’t import data. Instead, they consist only of metadata defining the model structure. When the model is queried, native queries are used to retrieve data from the underlying data source.

There are two main reasons to consider developing a DirectQuery model:

  • When data volumes are too large – even when data reduction methods are applied – to load into a model, or practically refresh
  • When reports and dashboards need to deliver “near real-time” data, beyond what can be achieved within scheduled refresh limits. (Scheduled refresh limits are eight times a day for shared capacity, and 48 times a day for a Premium capacity.)

Mix mode can mix Import and DirectQuery modes, or integrate multiple DirectQuery data sources. Models developed in this mode support configuring the storage mode for each model table. This mode also supports calculated tables (defined with DAX).

The table storage mode can be configured as Import, DirectQuery, or Dual. A table configured as Dual storage mode is both Import and DirectQuery, and this setting allows the Power BI service to determine the most efficient mode to use on a query-by-query basis.

Mix mode strives to deliver the best of Import and DirectQuery modes. When configured appropriately they can combine the high query performance of in-memory models with the ability to retrieve near real-time data from data sources.

For any inquiries about purchasing a Power BI license or any additional questions, feel free to contact us at here

Video: This is Power BI

For more Customer Showcases, see Microsoft Power BI website

Transform data to include in a report

Transform data to include in a report

Sometimes, your data might contain extra data or have data in the wrong format. Power BI Desktop includes the Power Query Editor tool, which can help you shape and transform data so that it’s ready for your models and visualizations.

In this chapter, you will transform data with Power Query Editor.


Video: Transform data

Launch Power Query Editor

To begin, select Edit from the Navigator window to launch Power Query Editor. You can also launch Power Query Editor directly from Power BI Desktop by using the Edit Queries button on the Home ribbon.


After loading your data into Power Query Editor, you’ll see the following screen.

  1. In the ribbon, the active buttons enable you to interact with the data in the query.
  2. On the left pane, queries (one for each table, or entity) are listed and available for selecting, viewing, and shaping.
  3. On the center pane, data from the selected query is displayed and available for shaping.
  4. The Query Settings window lists the query’s properties and applied steps.

Transform data

On the center pane, right-clicking a column displays the available transformations. Examples of the available transformations include removing a column from the table, duplicating the column under a new name, or replacing values. From this menu, you can also split text columns into multiples by common delimiters.


The Power Query Editor ribbon contains additional tools that can help you change the data type of columns, add scientific notation, or extract elements from dates, such as day of the week.

Tip: If you make a mistake, you can undo any step from the Applied Steps list.


After you select Close & Apply, Power Query Editor applies the query changes and applies them to Power BI Desktop.

For more information, see Quickstart: Using Power Query in Power BI Desktop

Combine data from multiple sources

Combine data from multiple sources

With Power BI Desktop, you can use the Power Query Editor tool to combine data from multiple sources into a single report.

In this chapter , you will combine data from different sources by using Query Editor.


Video: Modeling and transforming data

Add more data sources

To add more sources to an existing report, from the Home ribbon, select Edit Queries and then select New Source. You can use many potential data sources in Power BI Desktop, including folders. By connecting to a folder, you can import data from multiple Excel or CSV files at once.

Power Query Editor allows you to apply filters to your data. For example, selecting the drop-down arrow next to a column opens a checklist of text filters. Clearing a filter allows you to remove values from your model before the data is loaded into Power BI.


Important: Filtering in the Power Query Editor changes which data is loaded into Power BI. Later, when you apply filters in the Data View or Report View, those filters only apply to what you see in visuals but do not change the underlying dataset.

Merge and append queries

You can also merge and append queries. In other words, Power BI pulls data that you select from multiple tables or various files into a single table. Use the Append Queries tool to add the data from a new table to an existing query. Power BI Desktop attempts to match the columns in your queries, which you can then adjust as necessary in Power Query Editor.


Write customized queries

You can use the Add Custom Column tool to write new customized query expressions by using the powerful M language.


For more information, see Tutorial: Shape and combine data in Power BI Desktop

Introduction to Power BI

Introduction to Power BI

Microsoft Power BI is a collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights. Whether your data is a simple Microsoft Excel workbook, or a collection of cloud-based and on-premises hybrid data warehouses, Power BI lets you easily connect to your data sources, visualize (or discover) what’s important, and share that with anyone or everyone you want.


Power BI can be simple and fast, capable of creating quick insights from an Excel workbook or a local database. But Power BI is also robust and enterprise-grade, ready not only for extensive modeling and real-time analytics, but also for custom development. Therefore, it can be your personal report and visualization tool, but can also serve as the analytics and decision engine behind group projects, divisions, or entire corporations.

If you’re a beginner with Power BI, this longer story about Power BI will get you going. If you’re a Power BI veteran, this articles will tie concepts together and fill in the gaps.

The parts of Power BI

Power BI consists of a Microsoft Windows desktop application called Power BI Desktop, an online SaaS (Software as a Service) service called the Power BI service, and mobile Power BI apps that are available on Windows phones and tablets, and also on Apple iOS and Google Android devices.


These three elements — Desktop, the service, and Mobile apps — are designed to let people create, share, and consume business insights in the way that serves them, or their role, most effectively.

How Power BI matches your role

How you use Power BI might depend on your role on a project or a team. And other people, in other roles, might use Power BI differently, which is just fine.

For example, you might view reports and dashboards in the Power BI service, and that might be all you do with Power BI. But your number-crunching, business-report-creating coworker might make extensive use of Power BI Desktop (and publish Power BI Desktop reports to the Power BI service, which you then use to view them). And another coworker, in sales, might mainly use her Power BI phone app to monitor progress on her sales quotas and drill into new sales lead details.

You also might use each element of Power BI at different times, depending on what you’re trying to achieve, or what your role is for a given project or effort.

Perhaps you view inventory and manufacturing progress in a real-time dashboard in the service, and also use Power BI Desktop to create reports for your own team about customer engagement statistics. How you use Power BI can depend on which feature or service of Power BI is the best tool for your situation. But each part of Power BI is available to you, which is why it’s so flexible and compelling.

We discuss these three elements — Desktop, the service, and Mobile apps — in more detail later. In upcoming units and modules, we’ll also create reports in Power BI Desktop, share them in the service, and eventually drill into them on our mobile device.

The flow of work in Power BI

A common flow of work in Power BI begins in Power BI Desktop, where a report is created. That report is then published to the Power BI service and finally shared, so that users of Power BI Mobile apps can consume the information.

It doesn’t always happen that way, and that’s okay. But we’ll use that flow to help you learn the different parts of Power BI and how they complement each other.

Okay, now that we have an overview of this module, what Power BI is, and its three main elements, let’s take a look at what it’s like to use Power BI.

Get data from Excel

Get data from Excel

Likely, you’ve used Microsoft Excel to create or view reports or to build pie charts or other visuals. Getting your Excel data into Power BI is a straightforward process.

In this chapter, you will bring Excel workbooks into Power BI.



This unit explains how you can import an Excel workbook file that contains a simple table from a local drive into Power BI. You’ll then learn how to begin exploring that table’s data in Power BI by creating a report.

Note: Up until now, we’ve been importing data through Power BI Desktop. This unit page is done from the Power BI service.

Make sure that your data is formatted as a table

For Power BI to import data from your workbook, that data needs to be formatted as a table. In Excel, you can highlight a range of cells, and then on the Insert tab of the Excel ribbon, select Table.


Make sure that each column has a good name; it will make it easier for you to find the data that you want when creating your reports in Power BI.

Import from a local drive

Wherever you keep your files, Power BI makes importing them simple. In Power BI, you can go to Get Data > Files > Local File to select the Excel file that you want.


After the file has been imported into Power BI, you can begin creating reports.

Your files don’t have to be on a local drive. If you save your files on OneDrive or SharePoint Team Site, that’s even better.

Create reports

After your workbook’s data has been imported, a dataset is created in Power BI and it will appear under Datasets.


Now, you can begin exploring your data by creating reports and dashboards. Select the Open menu icon next to the dataset and then select Explore. A new blank report canvas appears. On the right-hand side, under Fields, are your tables and columns. Select the fields for which you want to create a new visualization on the canvas.


You can change the type of visualization and apply filters and other properties under Visualizations.

If you use any of Excel’s advanced BI features like Power QueryPower Pivot, or Power View, you can import that data into Power BI, too.

For more information, see Get data from Excel workbook files.

Connect to data sources

Connect to data sources

Power BI Desktop connects to many types of data sources, including local databases, worksheets, and data on cloud services. Sometimes when you gather data, it’s not quite as structured, or clean, as you want it to be. To structure data, you can transform it, meaning that you can split and rename columns, change data types, and create relationships between columns.

In this chapter, you will:

  1. Connect to data.
  2. Import data into Power BI Desktop.

Video: Connecting to data sources

You can connect Power BI Desktop to many types of data sources, including on-premises databases, Microsoft Excel workbooks, and cloud services. Currently, there are about 60 Power BI-specific connectors to cloud services such as GitHub and Marketo. You can also connect to generic sources through XML, CSV, text, and ODBC. Power BI will even extract tabular data directly from a website URL.

Connect to data

When you start Power BI Desktop, you can choose Get Data from the ribbon on the Home tab.


In Power BI Desktop, several types of data sources are available. Select a source to establish a connection. Depending on your selection, you’ll be asked to find the source on your computer or network. You might be prompted to sign in to a service to authenticate your request.


Choose data to import

After connecting, the first window that you’ll see is the Navigator. The Navigator window displays the tables or entities of your data source, and selecting a table or entity gives you a preview of its contents. You can then import your selected tables or entities immediately, or you can select Edit to transform and clean your data before importing.


After you’ve selected the tables that you’d like to bring into Power BI Desktop, select the Load button. You might want to make changes to those tables before you load them. For example, if you only want a subset of customers or a specific country or region, select the Edit button and filter data before loading.


No matter what type of data you need, you’re likely to find a way to import it into Power BI Desktop.

Explore Power BI Desktop

Explore Power BI Desktop

The idea of building and sharing reports is an abstract concept. It will make more sense if you explore Power BI Desktop hands-on. The first step is to launch and explore the user interface (UI).

In this chapter, you will:

  1. Launch the Power BI Desktop.
  2. Explore the UI.

Video: Overview of Power BI Desktop and the Power BI service

Launch Power BI Desktop

When you launch Power BI Desktop, the Getting Started dialog box will appear, which provides useful links to forums, blogs, and introductory videos. Close this dialog box for now, but keep the Show this screen on startup option selected so that you can explore it later.

Explore the report building environment

In Power BI Desktop, you’ll begin to build reports in the Report view. You’ll be working in five main areas:

  1. Ribbon – Displays common tasks that are associated with reports and visualizations.
  2. Report view, or canvas – Where visualizations are created and arranged. You can switch between Report, Data, and Model views by selecting the icons in the left column.
  3. Pages tab – Located along the bottom of the page, this area is where you would select or add a report page.
  4. Visualizations pane – Where you can change visualizations, customize colors or axes, apply filters, drag fields, and more.
  5. Fields pane – Where query elements and filters can be dragged onto the Report view or dragged to the Filters area of the Visualizations pane.

Tip: You can collapse the Visualizations and Fields panes to provide more space in the Report view by selecting the small arrow, as shown in the following screenshot.


Create a visual

To create a visual, drag a field from the Fields list onto the Report view.


Power BI Desktop automatically created a map-based visualization because it recognized that the State field contained geo-location data.

Publish a report

After creating a report with a few visuals, you’re ready to publish to the Power BI service. On the Home ribbon on the Power BI Desktop, select Publish.


You’ll be prompted to sign in to Power BI. When you’ve signed in and the publish process is complete, the following dialog box will appear. You can select the link below Success!, which will take you to the Power BI service, where you can see the report that you published.

Pin a visual to a dashboard

When you view a published report in the Power BI service, you can choose the Pin icon to pin that visual to a dashboard.


You can choose whether to pin the visual to an existing dashboard or to create a new dashboard.

For more information, see Report View in Power BI Desktop .

Use Power BI

Use Power BI

Now that you know the basics of Microsoft Power BI, let’s jump into some hands-on experiences and a guided tour.

As you learn about all the things that can be done with Power BI, keep in mind that all these activities, and all the analysis that’s done with Power BI, generally follow a common flow. The common flow of activity in Power BI looks like this:

  1. Bring data into Power BI Desktop, and create a report
  2. Publish to the Power BI service, where you can create new visualizations or build dashboards.
  3. Share dashboards with others, especially people who are on the go.
  4. View and interact with shared dashboards and reports in Power BI Mobile apps.

As mentioned earlier, you might spend all your time in the Power BI service, viewing visuals and reports that have been created by others. And that’s just fine. Someone else on your team might spend all her time in Power BI Desktop, which is fine too. To help you understand the full continuum of Power BI and what it can do, we’ll show you all of it. Then you can decide how to use it to your best advantage.

So, let’s jump in and step through the experience. The first order of business is to learn the basic building blocks of Power BI, which will provide a solid basis for learning how Power BI turns data into cool reports and visuals.

"The purpose of a business is to create a customer who creates customers" — Shiv Singh

Our reference list:

Liked our service? Start a project with us.