Q1) What is Power BI Desktop application?

Power BI Desktop is an application that can be installed. This works nicely with Power BI by providing
data exploration and shaping and creating report with quality visualization. You can save your work to a
file and publish your reports in Power BI site.

Q2) What data sources can Power BI connect with for data exploration?

Files(XLSX & CSV)

Content Packs

Connectors to databases and other datasets such as Azure & SQL etc.

Q3) What are content packs?

They are collection of related documents and files that are stored as in a set.

Q4) What are all the types of Content packs?

Two types of content packs.

a. Services providers like Google Analytics, Market.

b. Salesforce Shared by other users in your organization.

Q5) What are Building Blocks in Power BI?

 Visualizations
 Datasets
 Reports
 Dashboards
 Tiles

Q6) What is Visualization?

Visualization is a visual representation of dataset that extracts information from the data based on the
business.

Q7) What is report in Power BI?

Report is basically a collection of visualization charts that appear together on one or more pages within
power BI.

Q8) What is Dashboard in power BI?

Dashboard is single layer presentation sheet of multiple visualizations reports.

Q9) What is Tiles in power BI?

Tiles is basically a single visualization in a report or on a power BI dashboard.

Q10) What are all the different types of filters available in Power BI Reports?

 Visual-level Filters
 Page-level Filters
 Report-level Filters

Q11) What is Visual level filter?

Visual level filters works on only an individual/single visualization, reducing the amount of data that the
visualization can see in power BI.

Q12) What is page level filter?

Page level filters work at the report-page level in power BI. Multiple pages in the same report can have
multiple page-level filters.

Q13) What is report level filter?

Report level filters work on the entire report within power BI project. Filtering all pages and
visualizations will include within the report.

Q14) What are all the parts of Power BI?

 Power BI is a cloud-based business service.

 Excel BI Tool Kit
 Power Query
 Power Pivot

Q15) What is Power BI is a cloud-based business service?

Power BI is a cloud-based business service enables the client to interpret information with higher
efficiency and execution.

Q16) What is Excel BI Tool Kit?

The Excel BI Toolkit comprises of Excel and four add-ins that enable us to do the transfer function, shape
transform, create, and to show our analyses.

Q17) What are all the types of Data Refresh Options available in Power BI?

 Package refresh
 Model or data refresh
  Tile refresh
 Visual container refresh

Q18) What is DAX in power BI?

DAX(Data Analysis Expression) do basic calculation and data analysis on data in power pivot. DAX is a
formula language used to compute calculated column and calculated field within power BI.

Q19) Where we can apply DAX functions?

 DAX works on column level values.

 DAX can’t modify or insert data.
 DAX can create calculated column

Q20) Mention some of the most common DAX Functions used?

 SUM
 MIN
 MAX
 AVG
 COUNTROWS
 DISTINCTCOUNT
 IF
 AND
 OR
 SWITCH

Q21) What is unique about the CALCULATE functions?

This is the only function that allows us to modify filter context of measures or tables.

Q22) What is the default visual interaction?

 The DVI is “Drill Mode On”. In the event, we keep it off, we need to change those settings
without fail. Rather set the Default to ‘Drill Mode On’.
 Drilling Mode will be on, if the aggregates are at initial level.

Q23) What are all the connectivity modes available?

 Import
 DirectQuery

Q24) What is Power Pivot?

Power Pivot helps in analysing our data. It empowers users to import large scale of data from numerous
data sources into a single Excel sheet for better users interface.

Q25) What are all the benefits of using variables?

Variables will make unconstrained DAX expressions which are consistent to interpret with data.
Variables can’t be shared among queries or be characterized at the model level.

Q26) what are the ways to create a bar chart?

Drag any one measure into the worksheet and drag one more dimension along it. Now you go to the
show me pane, and choose the bar chart tile. it can be both vertical and horizontal

Q27) What are the types of Data Refresh in Power BI?

By choice there are 4 different types of refreshes that can happen. They are,

 Package refresh
 Model or data refresh
 Tile refresh
 Visual container refresh

Q28) What is DAX?

For basic calculation and data analysis on data in power pivot we can use DAX. It is a formula language
to compute calculated columns and calculated fields.

 Pros and cons of Dax

 Dax works on multiple column values and Dax cannot modify or insert data
 We can calculate calculated column and measures, but we cannot calculate using rows.

Q29) Write a Sample formula for DAX syntax:

For the measure named Total Sales, calculate = SUM of values in the [SalesAmount ] column.

 Measures
 equal signs
 DAX syntax
 Parenthesis
 Reference Table and
 Column name

Q30) Do I need to sign up with my work email?

It does not support email addresses provided by regular email services or telecommunications
providers. So you should sign up using your professional address.

List the work email addresses that are supported.

Working email addresses ending in .edu and .org are always supported.

Those ending in .gov are not currently supported.

Q31) What is xVelocity in-memory analytics engine?

The main engine, xVelocity in-memory analytics engine. This machine can handle large amount of data
as it stores data in columnar databases and in enhances memory analytics which results in faster
processing of data as it loads all data to RAM memory.

Q32) Is Power BI available within campus?

No, Power BI is not available as a private or an internal cloud service. However, with Power BI and
Power BI Desktop you can connect to your own on-premises data sources. With the On campus Data
Gateway, you can connect to your on campus SQL Server Analysis Services server as well.

Q33) Where else can we view on-premises?

 SQL Server with the Power BI iOS.

 SQL Server on the iPhone.
 SQL Server on the iPad.

Q34) What are the Power BI mobile devices?

Power BI has its own apps for Android phones, iOS devices, and Windows 10 devices. Download Power
BI apps from below:

 Apple App Store

 Google Play and
 Windows Store

Q35) What are the Connectors to databases and other datasets?

Azure SQL Database and SQL Server Analysis Services.

Q36) What are the Organizational content packs?

BI professions and system integrators use this tool to build their own content packs to share purpose-
built dashboards, reports, and datasets within their organization.

Q37) What are the different BI add ins?

 Power Query
 Power Pivot
 Power View
 Power Map

Q38) What do I need to install to use Power BI?

Just a Web browser and email. You can use Power BI Desktop for free up to 60 days.

Q39) Where can you download the Power BI mobile apps?

 App Store
 Google Play and
 Windows Store

Q40) What are the policies for on-premises?

Power BI Desktop’s use rights do not limit you to one copy of Power BI Desktop for each individual user
at the company may install and use one copy on their premises.

Q41) Where do I get started with Power BI?

 Power BI Blogs
 Seminars
 Getting started videos on YouTube Channels
 Join a community and ask questions

Q42) How can I buy Power BI?

You can purchase Power BI licenses directly at www.powerbi.com.

Q43) Does Power BI meet national, regional, and industry-specific compliance requirements?

Without a doubt, and to learn more about Power BI compliance, at the Microsoft Trust Centre.

Q44) What has happened to the Power BI Office 365 experience?

It has been dropped.

Q45) How can we create a dashboard?

First upload a dataset and then upload the same, create multiple reports and infuse everything together
as an appealing dashboard.

Q46) What do you know about Power BI?

Power BI provides a cloud-based data sharing platform, developed by Microsoft Corporation. Power BI is
a business intelligence and data visualization tool which is capable of converting data from several
sources of data and places it in the user interface, dashboard and also prepares business intelligence
reports. Power BI is a multiple utility services software; it can provide multiple amounts of software,
services and connectors, Power BI mobile applications for various environments, Power BI cloud service
based on SaaS and Power BI desktop. Power BI is basically used in commercial application specifically by
business people who need to create BI reports.
Q47) How Power BI is different from Excel BI toolkit?

Through an Excel BI toolkit, one can get data from various sources to create user interactable report. As
per the requirements in the report, the user has to model the data. Power BI allows the user support
solutions online, through which the user can share the queries and the interactable reports which is
built through the Excel BI toolkit.

Q48) What do you know about Power BI Desktop

Power BI desktop is an application supported by Power BI tool which is used for report creations
basically. Power BI Desktop is made available to the users in 32 and 64 bit versions. Power BI is readily
available as open source to all the users. With the enhanced support from Power BI service, Power BI
desktop lets user to explore the data, to model, to shape and to create reports with good interactable
visualization feature. Users can save their work or they are allowed to post their data and reports to
Power BI suite to share the posted information with others.

Q49) Name the basic building blocks of Power BI?

Dashboards, Datasets, Reports, Data visualization, Tiles, power BI desktop, Power map, power pivot,
power query and power view.

Q50) What is the need for Power BI?

Power BI has a wide variety of features. We need Power BI because of the following things:

 In a single platform, business intelligence and self-servicing needs were met

 To build interactable data visualization in data centers.
 To establish connection for Excel queries and dash boards of Power BI for fast analysis and
business data sharing to others.
 For accurate and quick solutions.

Q51) State the features of Power BI.

The features of Power BI are stated below:

 Power BI possesses advanced data driven platform which lets users for interactive data
visualization.
 Assures enhanced security and inbuilt control features for business organizations
 Not only ensures well organized and faster custom application creation but also automates the
workflow to improve the productivity of business.

Q52) State the advantages of Power BI.

The advantages of Power BI are stated below:

  User can create their data sources too easily and quickly to visualize data.
 Serves for multiple purposes such as data visualization, personalized reports, decision support
engines to business organizations and groups.
 Establishes faster connection with local DB or to excel spread sheets.

Q53) What do you mean by Self Service Business Intelligence?

 It is a system for well organized and systematic data analytics which allows business individuals
or organizations to analyze, filter and segment data without any deep technical understanding in
math operations and business intelligence.
 This system enables a user with basic knowledge of data analytics to build reports in dashboard
and share data to others.
 Allows users to access their own data and to build several visuals to obtain better knowledge in
their business.

Q54) What are the data sources supported by Power BI? Explain

Power BI supports wide variety of data sources and users can choose data source from the available
data source connections from Get data option. Power BI allows connections with Azure Cloud, SQL DB,
various flat files etc., It also supports various web and mobile platforms such as Google Analytics,
Facebook etc., It in fact allows the very old ODBC connections as well.

Q55) Explain Content pack in Power BI.

Content pack is an inbuilt service available in Power BI. Content packs enables their users to view their
dashboards where their personalized data is stored and can create reports for the data. Microsoft
supports content packs for widely used services and Power BI is one among them. Some of the other
services which contains inbuilt content pack are Azure Mobile engagement, Marketo, Adobe Analytics,
Salesforce.com etc., Content packs provides tools to support organizations, business professionals and
system integrating professionals to create their own content packs and can share data in dashboards
and to create reports and datasets.

Q56) What is the use of filters in Power BI reports? Explain.

Filters are used to perform several functions to filter data, report and visualization. There are various
filters available to perform these functions. They are:

 Page-level filters: Page-level filters perform its tasks at the page level in reports. So, every page
in a report will contain a page level filter.
 Visual level filters: Visual level filters are used for visualization through an individual’s dash
board and is used to reduce the amount of data in the visualization. Moreover, this filter is used
to filter not only data but calculations as well.
 Report level filters: Unlike page level filters, these filters perform filtering over the complete
report individually. It filters all pages and also visualizations present in the report.
Q57) State the differences between Power BI and Tableau.

Tableau is a widely used and most famous tool in Business intelligence. Though Power BI provides tough
competition to Tableau, Power BI has certain limitations.

Tableau Power BI
Tableau allows access not only to simple but to Power BI produce strong data manipulation in
strong visualizations as well. It is one of the best
backend but provides access only to simple
data visualization tools in the BI market. visualizations.
In case of Tableau, it allows the user to connect In free version, Power BI lets the user to only 1
larger datasets. GB data.
Power BI allows several data sources supported
Tableau is the best available tool in the BI
by Tableau. With the integration of Office 365
market. It supports wide variety of data sources
suite, it allows connection with Share point.
to visualize data. It allows the user to select a
Power BI also allows online support to the user
dataset in the dataset to visualize data for a
for direct visualization through Search Engines
selected dataset.
but is limited to Bling alone.
There is a free version available in Power BI
Tableau is costlier while compared to Power BI
which lets the user to 1GB data set limit. Pro
but provides a better price for small and medium
Software is paid but is cheaper in the market
scale companies.
while compared to other BI tools.

Q58) State the differences between Direct Query method and Import method.

Direct Query method Import method

Import method also does data transformation and
Data manipulation is much limited in this method manipulation tasks. If the data is published in PBI, it
and the data will be present in the SQL DB. is limited to 1 GB. Here the data will be present in
Power BI Azure.
As the name implies, the queries are direct and Import method requires to schedule refresh and
does not requires refresh scheduling. has to be refreshed 8 times in a day.

Q59) State the advantages of Direct query method.

Following are the advantages of Direct query method:

 User can build larger data sets data visualizations through Direct query method, but Power BI
desktop supports data visualizations on smaller data sets alone.
 There is no dataset limit for Direct query method and 1 GB dataset limit is not applicable in this
method.
 User can visualize current data report alone while using direct query.

Q60) State the limitations of Direct query method.

Following are the limitations present in Direct query method:

 User can perform data aggregation for unlimited number of rows but returning data is limited to
1 million rows alone.
 All the tables have to come from a single data base only.
 While using complex queries, the query editor returns an error. This error has to be removed
before running a new query.
 Relationship filtering can be allowed only in one direction while using Direct query method.
 There is no support for special treatments in direct query method for time related data present
in the tables.

Q61) Tell me about Power pivot.

Power pivot is a technique used in data model and analysis. It is available as an add on to be used with
Excel 2010. Because of this feature, millions of rows of data from various data sources can be imported
to one Excel work book. Power pivot helps the user to build relationship to heterogeneous data, to build
the computed columns and measures through formulas and to build pivot charts and pivot tables. It also
enables the user in data analysis through which the business people can take decisions on time
independently without any support from IT professionals.

Q62) What do you know about Power Pivot data model?

Power pivot data model is a data model composed of tables, columns, data types and table relations.
Generally, this data model is built to hold data for business operations. It could be either for an
individual to a business organization.

Q63) What do you mean by DAX?

DAX is the Data Analysis Expression which is used to perform data analysis and primary calculations of
data in the Power pivot. DAX performs math computing through formulas. It works on column values
and calculated field but cannot work on rows. Also, DAX will not alter a data or insert a new data.

Q64) State the differences between Power BI and SSRS.

Power BI SQL Server Reporting Services
Power BI gives strong data manipulation feature in its SQL server Reporting Services is generally used to make
backend but provides access only to simple pixel perfect reports and provides limited dash board
visualizations. options.
SSRS does not have any limit. Also, it allows users to
In free version, Power BI limits the use 1 GB data. connect larger data sets, which is not possible in Power
BI.
Power BI allows several data sources supported by
Tableau. With the integration of Office 365 suite, it
SSRS also allows various data sources for connections to
allows connection with Share point. Power BI also allows
make business intelligence reports.
online support to the user for direct visualization
through Search Engines but is limited to Bling search.
There is a free version available in Power BI which allows Upon request, the cost details are exposed to the user.
the user to 1GB data set limit. Pro Software is paid but is
cheaper in the market while compared to other BI tools.
Power BI is free version and Power Pro is paid. Pro is This software license is available on request and is
available to the user at a cost of 9.99USD/month with 10 available on cloud platforms such as Azure, AWS and
GB data limit and 1 million rows/hour data streaming. some other major platforms.
Implementation process in Power BI is quite simple. It Compared to Power BI, SSRS contains complex
uses cloud storage. implementation process.

Q65) State the differences between Power BI and Power BI PRO.

Power BI Power BI Pro

Is a free version with storage limited up to 1GB for
Is a paid version with more storage capacity options
data.
Pro software added with bunch of features through
Power BI is a basic tool for beginners to get started which user can refresh data daily, more storage
and to search data. option, direct and live data sources including
interaction option etc.,

Q66) Explain about data modeling in Power BI.

Power BI provides yet another feature called Data modeling, which allows the users to connect several
data sources in the business intelligence (BI) tool through a relationship. Relationship explains the way
data sources were connected to one another and build interactable data visualizations over various data
sources. Data modeling also lets the user to create customized math calculations in the existing table
using formulas. The columns in the tables were transferred live to Power BI for interactive data
visualization. Because of this feature, businesspeople could be able to define new metrics so as to do
customized math calculations for the defined metrics.

Q67) How do you create calculated columns in Power BI?

By joining two or more elements present in the existing data, users can build calculated columns in
Power BI. Users are allowed to define new metrics by stating math calculations in an existing column.
They are also allowed to join two columns to make it as one single column.

Q68) How do you define a dataset?

Data set can be defined as a collection of various information (data) through which representations can
be made by Power BI. For Example, Microsoft Excel Work book, SQL server table etc.,

Q69) What do you mean by dashboards in Power BI?

Dashboards in Power BI are used to visualize data, reports etc., Dashboard makes several perceptions to
make it single.
Q70) How do you create dash boards in Power BI?

Users can create their own dash boards in Power BI by attaching the visualizations available in Business
intelligence reports. The visualizations built from these reports through Power BI service are accessible
for attaching to build dashboards.

Q71) What do you mean by reports in Power BI?

Report contains various data collected from various sources. Several data are grouped on page in a
report. For example, data collected for products profit, sales of a company, data from a city etc.,

Q72) What do you mean by tiles in Power BI dashboard?

Tile is one more feature available in Power BI service. It is said to be a data snap shot which can be
attached to your dashboard. It is a single visualization present in a report or on individual’s dashboard.

Q73) What do you know about Power Query?

Power Query is an ETL tool which means extract, transform, load three data base functions in one tool.
Through this tool, user can pull data out from one data base and can place that data in another data
base. The method of reading a data is called as Extract. The method of transforming the data, i.e
changing data from the existing form to a new form as per the need is called as Transform. The method
of writingQ45) the data to the target database is called Load. This extract, transform and load function
can be done through interfaces and does not require any script. Using Power Query, user can import
data from several sources, can combine, append data from several sources and can shape the data as
per their requirement.

Q74) What is the language used in Power query to perform ETL?

Power query uses M-code, a recent programming language among developers. Since this language is
quite simple to learn and similar to other existing programming languages, users started to use this
language.

Q75) Explain Focus mode present in Power BI dashboard.

Focus mode is an enhanced feature in the Power BI dash board, which lets the user to focus their dash
board, i.e., they can visualize their dashboards too closer. In case, multiple values were present in the
dash board, this focus mode option enables the user to have a close view. User can also view columns in
tables closely through focus mode. User can closely view all the data or report in the dashboard through
focus mode. Focus mode also allows the user to attach a visual to some other dashboard through PIN
icon present in the dashboard.

Q76) As per your knowledge, tell me the platforms which Power BI application supports?

Power BI application is available for the following platforms:

  i-phone and i-pad
 Android platform
 Windows platform – windows desktop and tablets

Q77) Tell me about Data Gateway in Power BI service.

Data gateway enables the user to connect in built data sources to the Power BI service. Data gateway
allows any other version without any admin configuration to connect with. It is also called a Personal
Gateway. Through logging on the Power BI service, user can set up this personal gateway service.

Q78) Explain how to create visualizations in Power BI.

Power BI allows the user to create simple visualizations alone. Visualizations help users to view their
data and to help them to interact with the dashboard. Visualizations present users to view data too
effectively. It is the basic and important building block to any BI tool. Several data visualization
components were available in Power BI such as bar charts, maps, pie charts and complex components
like gauges, funnels, waterfalls etc., and various other components.

Q79) What do you mean by Combination charts in Power BI?

Combination charts are charts supported by Power BI which can plot various measures over a single
chart. These charts plot all the measure values. Any requirement of different measure can be plotted
using combination chart. Stacked column charts and Line charts are the widely used combination chart
types in Power BI.

Q80) How do you modify colors in charts?

Users are allowed to alter the chart colors in Power BI. There will be an option to change the color while
selecting any visualization. This color change option will be present under the Format tab in the
visualization dash board. Some of the options in the format tab are: Data colors, Legend, Title, Detail
label, Border, Background, General and Lock aspects.

Q81) How will you add shapes, images and text box to your visualization in Power BI?

To provide enhanced visualizations to users measures, there is a need to add text, images and shapes. If
user needs to add some messages, to add header or footer, any signatures to data visualization, user can
use this option. This option is available to user in their Power BI dashboard to add shapes, images and
text box. User is allowed to add different shapes in data visualization. User can enter anything in the text
box and edit the text using rich text editor. This text box is available in user’s report canvas. Images can
also be added to add images, logos etc., to data visualization. Any image can be selected by browsing
your system. Also user is allowed to resize selected images.

Q82) Is it possible to create duplicate reports? If yes, how?

Some situations require the usage of same visuals and same layout for various pages. There is an option
available in Power BI to duplicate a page. Power BI has a duplicate page option, and if you use it, a fresh
page will be created with similar visuals and layout like the original page. To create a duplicate page,
from the dash board menu, right click on the page and select duplicate page option.
Q83) How do you integrate Microsoft Excel to Power BI?

Power BI allows users to integrate excel workbook to Power BI. User can import data present in excel
sheets from the local disk of a system to Power BI. But, user has to check whether every column in excel
work book contain proper name before importing data from excel to Power BI.

Q84) How do you share reports through Power BI Desktop?

Power BI desktop allows the user to share their data and reports to other business users. Users can
share data, dashboards and business intelligence reports with other business people, individuals and to
their colleagues who were present in their organization.

Q85) What do you know about Power BI designer?

Power BI designer is a mix of Power view, Power query and Power pivot. It is a stand- alone application
which allows the users of power BI to build Power BI reports. The created report is further uploaded to
powerbi.com. Power BI designer does not require Microsoft Excel.

Q86) What are the DAX functions available in Power BI?

Power BI allows its users to use various function types to examine data and to create new metrics, new
columns. These functions are available from several categories such as: text, date, aggregate, counting,
logical and information. User can easily view the list of all these available functions. If you type
something in the formula space, you can view the list of available functions for the typed character. This
option is similar to find a contact option from a mobile device. Q87) State the list of aggregate functions
and counting functions in DAX?

Q87) State the list of aggregate functions and counting functions in DAX?

Some of the aggregate functions present in DAX are: Average, Min, Max, Sum and SumX. Some of the
counting functions available are: Count, Distinct Count, Count Rows, Count Blank and CountA.

Q88) What are the calculation types available in DAX?

Power BI allows the users to build two basic calculations using DAX: Calculated measures and Calculated
Columns.

Q89) What are the logical and date functions available in DAX?

Logical functions: AND, OR, NOT, IF and IF ERROR

Date functions: Hour, Date, Weekday, EOMonth and Now.

Q90) What are the information and text functions available in DAX?

Information functions: ISTEXT, ISNONTEXT, ISBLANK, ISERROR and ISNUMBER.

Text Functions: Search, Fixed, upper, concatenate and Replace.

Q91) How do you select admin role in Power BI?

For those who need admin privileges to access the BI admin portal without office 365 suite access,
admin role is granted to Power BI. Users who require admin access can follow the following steps.

 Go to Office 365 Suite Admin Center

 Choose Users option, further choose Active users
 Go to Roles tab and click on the Edit option
 Then, go to the customized administrator tab and then select the option Power BI administrator.

Q92) What are the Power BI versions available and how about the pricings?

 Power BI is a open source tool, available at free of cost but is limited to 1GB data and can make
10k rows/hour.
 Power BI Pro is freely available for 60 days as a trial version and further, user can pay
9.99USD/month and /user. User is allowed for 10GB data storage and can make 1 million
rows/hour.
 Power BI also contain a premium version for premium users. Premium version contains a
flexible pricing as per node/month.
 Power BI contains an on-premises report server which is capable of publishing reports. In this
option, user can change their cloud platform whenever they need.

Q93) Tell me about REST API and its association with Power BI.

REST APIs are used to operate the data present in Power BI in real time environments. With the
combination of REST API with Power BI, user can build dashboards, datasets, add/delete rows and can
receive groups. This Power BI REST API can be built through many technologies. Some of them are :

 Ruby
 .Net
 JQuery

Users require Azure active directory token to get access applications in Power BI dashboards.

Q94) Explain the security level of Power BI.

Power BI requires Azure active directory (AAD) access for every user who uses Power BI service. Users
require login credentials to build their BI account, which could be also an email account. Users need a
unique username to access this account. Azure cloud actually provides security to Power BI. It includes
various levels of security. They are:

 AAD Based Security

 Multi-tenant platform Security
 Networking Security

Q95) What are the repositories available in Power BI data storage?

There are two types of repositories available in Power BI for data storage. They are:

 Azure BLOB storage and

 Azure SQL DB

To upload users data, Azure Blob storage is used and to store system data, Azure SQL database is used.
Azure cloud is responsible to provide authentication, data and network security to the Power BI users.

Q96. What do you mean by BI?

BI stands for Business Intelligence. It’s a group of technologies that help in analyzing the entire data. It
also offers actionable insights to users, corporate executives, and managers to make the best decisions.

Q97. Mention some of the Power BI components?

There are different Power BI components namely

 Power Pivot (for in-memory tabular data modeling)

 Power Map (for 3D geospatial visualizing)
 Data Management Gateway
 Power BI Mobile (For iOS, Android, and Windows phones)
 Power Query(for transformation and data mash-up)
 Power View(for data visualizations view)
 Data Catalog
 Power Q/A (for natural language Q/A)
 Power Bi Desktop(a companion development tool)

Q98. What are the important parts included in the Microsoft self-service Business Intelligence
solution?

There are two important Business Intelligence solutions included in the Microsoft self-service namely

 Power BI
 Excel BI Toolkit

Q99. What do you mean by Power BI?

Power BI is the data-sharing environment in a cloud that enables users to visualize, analyze the
information with high speed, greater understanding, and efficiency. It also enables users to connect to a
different range of data using interactive reports, compelling visualizations, and user-friendly dashboards.

Q100. What do you mean by Excel BI Toolkit?

The Excel Power BI Toolkit enables users to develop attractive reports by importing various types of data
from different model data acc and sources to requirements.

Q101. What is the difference between Power BI and Power BI Pro?

 Power BI: It provides different features for the users to get started with data search in a unique
way.
 Power BI Pro: It holds some extra features added to the Power BI features like higher storage
capacity, data refresh scheduling and interactive live data sources.

Q102. Mention the difference between Self-service BI and managed enterprise BI?

Self-Service BI:

 In this type of BI, they help a different enterprise to process data from different data sources.
The source can be in any type of format.
 It also enables the companies to process data, conducts different operations in the business
easily.
 Data analyzing is also made easier.
 There are no requirements for third party vendors.
 The user can generate actionable and intuitive dashboards without the need for complicated
programming codes.

Managed Enterprise BI:

 In this type of BI, the data flows from various sources and there is no order for any companies to
take in and control the data sources.
 The organizations fail to conduct different business operations as they cannot analyze, report, or
collaborate to gather valuable insights.
 In this BI, there are lack of proper information as well time constraints are present when
analyzing data.
 This type of BI also needs third party vendors to help organizations in a different way.
 There is a requirement for complex programming skills to generate reports.

Q103. Mention the Power BI Cost?

Power BI and Power BI desktop are free of cost. If you need a premium package, then the users should
pay $9.99 per month for Power BI Pro. The one advantage here is that the users can pay the amount
after completing their 60 days of a free trial.

Q104. What do you mean by Power BI Desktop?

It’s one of the free desktop applications installed on your system. The Power BI Desktop along with the
Power BI service offers advanced shaping, creating reports, modeling, data exploration with the best
visualizations. All your works can be saved to a file. You can also publish that information, and report
them to others using the Power BI Suite.

Q105. Mentions some of the Power BI Building blocks?

There are five important components of Power BI building blocks namely

 Datasets: Its a group of data that helps users to create their own visualizations.
 Dashboards: It’s presenting your different visualizations in a single layer. It means you will be
able to integrate different visualization in a single layer of the page.
 Visualization: Its the data’s visual representation. For example Line graph, graphical
presentation of top of treemaps, pie chart, side by side bar charts, and so on.
 Reports: Its the group of visualizations that appear as a group on different pages. Sales by state,
logistics performance report, country, profit by-products report, and more are some of the
examples.
 Tiles: Its the dashboard or report’s single visualization.

Q106. What are the fundamentals required to use Power BI?

 You should have a work email address and a web browser.

 Make sure you do not have your work email address ending in .gov or .mil as they are not
supported at present.

Q107. What are the formats available for Power BI?

 Power BI Service: An online Software as a Service (SaaS)

 Power BI Desktop: For desktop computers
 Mobile Power BI apps: For Android and iOS devices.

Q108. Mention the work email address supported currently?

Your work email address should end with .edu or .org.

Q109. Why do you need a content pack?

A content pack is described as the predefined and readymade collection of reports and visualizations
utilizing your chosen service like Salesforce. You need them when you need to create a report
immediately instead of taking much time to create it from scratch.

Q110. What do you mean by DAX and explain its basic concepts?
DAX is derived as Data Analysis Expression, which is a group of constants, operators, and functions used
in formulas to return and calculate values. In short, they are used to develop new information from the
existing data. There are three basic concepts included in DAX namely

 Functions: This type of concept use particular values, otherwise called as arguments in the right
order to execute a calculations, just identical to Excel functions. The categories included in the
functions are time intelligence, logical, statistical, parent/child, date/time, information,
mathematical, text, and more.
 Syntax: This indicates the writing format of a formula. It indicates the elements used to
comprise it. The function in Syntax is SUM. You will get an error message in case your syntax is
wrong.
 Context: Filter context and row context are two types of context. Row context is used when a
formula include a function that is used to apply the filter to pick the single row in a single table.
The filter context is used when there is a number of filters applied in a calculation that indicates
a value or result.

Q111. Mention some of the data type categories?

 File
 Power BI
 Online Services
 Database
 Azure

Q112. Mention a few data sources in the Get Data menu?

 Power BI datasets
 Text
 Excel
 Web
 SQL Server and analysis services

Q113. What do you mean by ‘Get Data’ in Power BI?

This feature provide data connectivity to a different number of data sources. The Get Data can be seen
on the Power BI Desktop’s main page menu bar. You can make use of this option to connect the app of
Power BI Desktop with the related data source. You will be able to connect this to the local system data
files, Azure SQL database, Google Analytics, Excel files, Facebook, Power BI datasets, and more. You can
also connect them to the on-premise data source and cloud-based source with the help of direct
connects, online services, and gateways.

Q114. What does Report indicate in Power BI?

It’s the collection and combination of various visualizations related to specific business topics. It’s also
the structured and graphical data presentations used in the analysis. Reports reveal the data’s important
insights and are also informative. Users can easily publish and share reports that are created in Power
BI. It can be done using Power Bi website and Power BI desktop, mobile platforms, and cloud-based
platforms.

Q115. What are the important capabilities included in the Power BI?

 Datasets
 Tiles
 Navigation pane
 Help and feedback buttons
 Ad Hoc analysis
 Mobile device compatibility
 Trend indicators
 Data visualization tools
 Complete reporting
 Customizable dashboards
 Reports
 Get Data
 Ad Hoc reporting
 Q&A question box
 Row-level security
 OLAP (Online Analytical Processing)
 Interactive reports authoring.

Q116. Where are the data of Power BI stores?

All the imported Power BI data from various sources of data are stored in either Dimension tables and
Fact tables.

 Dimension table: It’s a database table that holds every attribute values for the stored data in the
fact table. It held in a star schema that is attached to the fact table.
 Face table: It is the primary or central table of any start schema. It holds the quantitative or
measured values. It contains the primary keys and these are not normalized usually.

Q117. Why do you need a Power BI tool?

There are many reasons to state why you should go for Power BI tools. Some of them include.

 Power BI enables updates on real-time dashboards.

 It enables secure environment, hybrid configuration, and rapid deployment.
 It holds dashboards that are prebuilt and also reports for SaaS solutions.
 It provides reliable and secure connections of all your sources of data in the on-premise or
cloud.
 Enables you to explore your data with the help of a natural language query.

Q118. What are the data types supported by DAX?

Data Analysis Expression supports the following data types.

 Boolean
 String
 Numeric
 Decimal
 Datetime

Q119. Mention some of the Power BI applications?

Power BI is mostly used by

 Data & Business analyst

 IT professionals and team
 Data Scientist
 Program Management Office
 Database Administrator and Developer
 Consumer for End User Report

Q120. What you mean by Grouping and how to use it in BI?

Power BI Desktop enables the users to group the data into smaller chunks. You need to use Control +
click to select different elements in the visual for the grouping purpose. You can right-click on any of the
elements, that appear in the groups’ window.

Q121. Mention some of the Power BI Dashboard features?

 It enables you to develop different tiles like images, text boxes, web content, and combine URLs.
 It enables you to drill through the selection pane, bookmarks, and page.
 It enables you to set a report layout for the mobile view.

Q122. What is the language used in Power Query?

M-Code is the language used in Power Query. It’s a new programming language developed to be user-
friendly, case sensitive and it’s identical to other languages.

Q123. Mention some of the data shaping techniques?

 Adding Indexes
 Removing rows and columns
 Applying for a Sort order

Q124. Are there any prerequisites to connect to a database in Azure SQL Database?

Yes, you need to configure the firewall settings to enable remote connections before connecting it to the
database in Azure SQL Database.
Q125. Mention some of the advantages of using variables in DAX?

 Variables are capable of making DAX expressions more logical.inituitive to interpret.

 By evaluating and declaring a variable, it can be reused at different times in the DAX expression,
it means you can avoid any of the extra queries of the source database.
 Variables are designed to query or measure. They are not able to share them with the model
level.

126. What is BI?

The BI is an acronym for Business Intelligence which includes technologies practises and applications for
collection, integration, evaluation and presentation of organizational information using different charts.
The main motive behind this business intelligence tool is to support business managers, corporate
executives, employees, and other persons in taking more data-driven and more informed decisions.

127. What is Self-Service Business Intelligence?

Self-service business intelligence is an approach to data analytics in which the users are no need to
possess the high technical knowledge to filter, segment, and examine the data related to them. The SSBI
does not demand your technical and statistical knowledge in performing business intelligence tasks.

SSBI’s have a simplified process for end-users to access data and analyse it to make effective decisions.

The SSBI’s can be easily operated by those who have a basic understanding of data and can create
shareable dashboards and reports.

128. What do you know about Power BI?

Power BI is a cloud-based business intelligence tool, that lets you clearly visualize and analyze your
organizational information. In Power BI you can develop reports using Power Pivot, Power Query, Power
View and also you can share your insights with your co-workers. Power BI lets you upload all your excel
books into the cloud and you can grant access to a specific group of people or groups you want. Your
colleagues not only access your reports but also you allow them to interact with the reports in the form
of applying slicers and filters to improve the data. Power BI allows for seamless integration between
multiple software and services.

Power BI lets you perform below things:

 Easy to share presentations and reports with your colleagues.

 You can update the excel file from the data source that can be either in the cloud or on-site.
 You can display the dashboards on multiple devices which include PC’s, tablets and also mobiles
enabled with HTML 5 by downloading the Power BI App.
 You can use natural language processing to query your data.

129. List the data sources that Power BI can connect to?
Power BI can seamlessly connect to a wide range of data sources, below mentioned are the different
sources.

Files: Data can be sourced from Power BI Desktop files Excel (.xlsx, xlxm), (.pbix), and from the CSV files.

Content Packs: The content packs are a collection of related files or documents that are stored as a
group. We have two types of content packs available in Power BI, the first type is from the service
providers such as Google Analytics, Salesforce, and Marketo and the second type is created and shared
by the individuals in your organization.

Connectors: which includes databases such as Database And SQL, Azure SQL, comparing analysis service
tabular data, etc.

130. List the important components of Power BI?

Below mentioned are the main components of Power BI:

 Data Integration
 Data Presentation
 Data Processing

131. What are the key components of Power BI?

Below mentioned are the key components or building blocks of Power BI

Visualizations: It is nothing but a graphical representation of data. For example Pie Chart, Bar Charts,
Line Graph, TreeMap, a graphical presentation with Geographical Map, etc.

Reports: Report is a set of visualizations that comes in one or more pages. For example Sales by
Continent, Country, State, City, profit of a product, logistic performance report, etc.

Datasets: Dataset is a grouped data that is being used by Power BI for graphical representation purpose.
Ex. Excel sheets, SQL server tables.

Dashboards: Dashboard is a single screen to present multiple graphics. Dashboards allow users to bring
multiple visualizations into a single page layer. Ex. A marketing dashboard can have geographical maps,
pie charts, and bar charts.

Tiles: Tile is a visualization on a dashboard or a report. Ex. Pie Chart in a report or dashboard.

132. What is a Filter and explain different types of Filters available in Power BI?

Filters are the criteria set by us to get only the intended information from the whole data. Filters work
based on the defined criteria. For example, we have sales data from the US and you want to see only
New York information then you can give Criterion as New York to eliminate the other data.
In Power, BI FIlters can be applied at three levels which are Visual-level, Page-level, and Report-level.

Visual-level: Here Filter is applicable to only individual visualizations and reduces the data amount
shown by Visualization. Besides these visual-level filters can also filter both calculations and data.

Page-level: These are the filters that work on a page level in a report. In a report, each page can have a
different filter i.e page- level filters.

Report-level: These are the filters that work at the report level and are used to filter the pages and
visualizations presented in a report.

Filters are highly useful but they are associated with some limitations which are as follows

You can not save the filters that you have applied to a report. Whenever you open a report you need to
apply all those filters again that you have applied previously. You can not save the filters you made to a
report in a saved report.

You can not hide a filter that is being applied to a report. The filters you have applied can be seen by the
people who have access to your report.

33. What is meant by Content packs in Power BI?

The Power BI content packs are pre-built solutions that come with various objects. Content packs in
Power BI show you a path to share different power BI objects such as datasets, reports, or dashboards
with your colleagues or individual groups. The content packs are accessed or shared through the Power
BI site. Microsoft has released content packs for major services such as Marketo, Salesforce.com, Adobe
Analytics, CircuitID, Azure Mobile Engagement, comScore Digital Analytix, SQL Sentry, Quickbooks
Online, and tyGraph.

To use the content packs you should be using Power BI Pro which helps you to implement and share
content packs. Content paks include:

 Reports
 Datasets
 Dashboards
 Excel Workbooks

135. What is meant by the term ‘Report’ in Power BI?

Reports in Power BI are defined as a collection of different types of visualizations related to a single
business topic. A report contains the datasets used in the presentation of analysis. Reports are highly
informative and contain valuable insights extracted from the data. In Power BI User can easily share
reports with others via Power BI Desktop, mobile platforms, cloud-based platforms, and power Bi
website.

136. Explain about Dashboards in Power BI?

A Power BI Dashboard is a canvas using which we can present various visualizations to represent
datasets. A dashboard helps us in presenting the data of reports in a graphical format that can be easily
understood by anyone. It contains only the important elements extracted from a report. A dashboard is
a single page prepared from multiple pages of a report. Dashboards help in getting quick insights rather
than going through complete reports. Dashboards are mainly used to get quick insights out of data to
understand business functionality.

137. What is a Power BI Service?

Power BI is a cloud-based analytical solution and offers the service regarding the same. The cloud-based
service is nothing but sharing and viewing the dashboards and reports on cloud platforms. One can use a
work email to access the Power BI reports on the Power BI site.

Power BI is a web-based platform in which you can share the reports that are made of Power BI Desktop
and create dashboards. Power BI Service versions are available in three different options such as Free,
Pro, and premium. And also Power BI Service is called by names such as “Power BI Workspace” “Power
BI.com”, “Power BI Web Portal”, and “Power BI Site”.

138. Where the data is basically stored in Power BI?

To visualize the data we need to download/import data into the Power BI tool. There are two areas in
which data gets stored in Power BI when you import from data sources that are Fact tables and
Dimension tables.

Fact Tables: A fact table stores Quantitative information for analysis purpose and it is not possible to
normalize in most of the situations.

Dimension table: This table is to store dimensions and attributes that describe stored in a fact table.

139. What are the various views available in Power BI Desktop?

There are three different views available in Power BI and each one serves a specific purpose.

Report View: In the report view, a user can add additional report pages and also visualizations and
publish them on the same portal using Report View.

Data View: Data view helps in looking at your data after that has been loaded into the model.

Relationship View: A user can check to control the relationships between the data sets by using a
relationship view.

140. What are the different types of data sets available in Power BI?

Major Data sources used in Power BI Desktop are:

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

141. Explain about Responsive Slicers?

You can easily adjust the responsive slicers according to the required sizes and shapes and the data
contained in a report should be realigned according to it.

142. What is an Advanced editor in Power BI?

Advanced Editor helps in viewing the query run by Power BI to Import data sources.

The Query is written in Power Query formula language

When you wish to view the query code from the Power BI click on the Edit queries presented in the
Home tab.

You can use either the View tab or Home tab to click on Advanced editors to use. The advanced window
editor will display the code used for the selected query.

When we make modifications to data using a query editor, the steps are automatically saved in Applied
steps.

145. List the most commonly used DAX functions?

Below mentioned are some of the most commonly used DAX functions:

 SUM, MAX, AVG, MIN, DISTINCTCOUNT, COUNTROWS.

 IF, SWITCH, OR, AND,
 ISFILTERED, ISBLANK, ISCROSSFILTERED
 FILTER, VALUES, CALCULATE, ALL,
 INTERSECT, UNION, NATURALINNERJOIN, EXCEPT, NATURALLEFTEROUTERJOIN, ISEMPTY,
SUMMARIZECOLUMNS,
 GEOMEAN, DATEDIFF, MEDIAN,
 VAR (Variables)

146. What are some benefits that we get from using variables in DAX?

Following are the benefits:

A single variable can be used multiple times in DAX expression by declaring and evaluating the variable.
Which eliminates additional querying from the source database.

Variables make DAX expressions more logical to interpret.

Variables have only limited scope to their query or measure, it is not possible to define them at the
model level, or shared among measures.

147. Explain the different Power BI add-in?

Following are the major Power BI add-in to Excel:

Power Query: It helps in selecting, editing, and Importing external data.

Power Pivot: this add-in used for data analysis and modeling.

Power View: It makes the task easier in designing Interactive visualizations.

Power Map: This add-in allows you to display insights in 3D.

Power Pivot – Power BI Interview Questions and Answers

150. What is the xVelocity in-memory analytics engine in Power BI?

The xVelocity in-memory analytics engine plays is a powerful thing behind the Power Pivot. It handles
large amounts of data because it stores data in columnar databases, and in-memory processing (it loads
all data into RAM) makes data processing faster than ever before.

151. Explain about Power Query?

It is an ETL tool used to reconstruct, clean, and transform data with the help of initiative interfaces
without writing a single line of code. It helps a user in the following ways.

Power query is capable of importing data from various sources such as databases, big data, files, Social
media, etc.

Construct data based on requirements by removing and adding data.

Joins and appends data from various sources.

152. List some of the common Power Query Transformations?

The common Power Query Transformations include Filtering Rows, Changing Data Types, Grouping,
Removing Columns, Adding New Columns, Splitting columns, etc.

153. Can we use SQL and Power Query together?

Yes, we can use them together. A SQL statement can be used as a source of Power Query for making
additional logical processing. This will help in ensuring that an efficient query is passed to the source
and avoids unnecessary complexities.
154. Which language is used in Power Query?

M-code is a new programming language used in power query. M-code is a simple language and similar
to other programming languages and it is a case sensitive language.

Q155. Explain the extraordinary version of power BI?

The strength BI laptop is the software that’s used by the BI developers to build intuitive reports from the
raw statistics which may be from numerous source systems. once the reviews are equipped we will put
up the reviews to Power BI service. The energy BI desktop is free.

Strength BI provider: that is a SaaS primarily based a part of Power BI & it’s miles frequently mentioned
as strength BI online or energy BI Cloud. The cause of power BI service is to percentage the reviews and
Dashboards build by way of the builders to the quit customers. The electricity BI provider is license-
based totally.

Power BI Mobile: The power BI cell is to view the reports at the move out of your mobile telephones.
we can download strength BI mobile from Android Play keep and Apple – App store.

Power BI Report Server: Often the reviews which we construct are greater Sensitive & the reviews can’t
be posted to the Power BI Cloud. To Tackle this state of affairs and to post the reviews to a carrier that is
On-Premise, Microsoft has come with a Product referred to as Power BI Report Server.

Q156. Explain the Components of Power BI?

Power Query: The ETL (Extract, Transform & Load) Part of the BI is taken care of in Power Query. It is a
Data Transformation Engine. It allows us to Connect a range of information sources ranging from Excel
to Streaming data.

Power Pivot: The Data Modelling Part of the BI Process is performed with the assist of Power Pivot. The
Relationships between the tables are hooked up right here and Calculations are written via Power Pivot
Part of Power BI. It makes use of DAX (Data Analysis Expressions) language to mannequin the data.

Power View: It lets to create Powerful visuals, charts & Graphs from the tables. It can join to the records
sources accessible in the Models and lets you to filter information for every visual or a web page or a
complete report.

Power Map: The Geographical information can be visualized by the usage of Power Map and it used
Microsoft Bing to get the Best visuals primarily based on Latitude and Longitude, Country, State, County,
City, etc.

Power BI Desktop: It is a single device Integrated with Power Query, Power View , Power Pivot and
Power Maps.

Q157. Explain how Power BI Stores the data?

Power BI Stores Data in phrases of Fact and Dimension tables.

Fact Tables: The Fact information shops the quantitative data. The Simple way of Explaining a Fact desk
is It incorporates numeric fields which can be aggregated alongside with the Foreign keys which are used
to set up relationships between the Dimension tables.

Dimension Tables: The Dimension tables or the Lookup are in distinction with the Fact tables, this table
incorporates textual content fields which are descriptive in nature. It consists of a Primary Key which are
in Integer information kinds to join with the reality tables.

Q158. What are the operations which are frequently carried out in PowerBI Query?

1. We can join to Various records sources & extract the required records and Shape the statistics as
per our need.
2. We can Create New columns the use of the columns are existing in the dataset.
3. Duplicate, Merge, Split, Rename the columns.
4. Replace the Columns values.
5. Pivot and Transpose the columns.
6. And tons more.

Q159. What are the special modes which are on hand in Power BI to import data?

1. Direct question mode à No Limitations in the Size

2. Import mode à Up to 1Gb of facts per dataset

Q160. What do you imply via DAX?

DAX (Data Analysis Expressions) which are predominantly useful in querying & returning the information
from the tables handy in the Data model. To do calculations and to function facts evaluation in Power BI
we want to use DAX calculations.

We can Write Measures and Calculated Columns or Calculated Tables the usage of DAX Expressions.

Q161. What is the distinction between Measure and Dimension?

Measures: The Measure are used to calculate aggregations such as Sum, Min, Max, Average & Count at
the desk level. It is calculated at the time of the question which says that the Measures are no longer
saved somewhere in the database or in the model. They truely exist.

Calculated Column: The Primary distinction between the measure and the calculated column in the
Evaluation context. The Evaluation of a calculated columns is completed at the row degree the place as
the comparison context of a measure is at the desk level.

Summary:

 Use a calculated column when you choose to consider every row.

 Use a measure when you want an aggregate.
Q162. What are the special interactions handy in Power BI?

There are three one-of-a-kind Interactions on hand in Power BI.

 Filter à It definitely filters the visuals or a tile when different charts are selected.
 Highlight à It Highlights solely the associated values in a chart or visuals primarily based on the
options primarily based on the different charts.
 None à It ignores the Filter picks from the other Charts or the visuals.

Q163. What are the distinctive filters on hand in Power BI?

 There are three extraordinary Interactions reachable in Power BI.

 Visual Level à It filters facts solely on an Individual chart or a Graph . It can filter each the
information and calculations.
 Page-Level à When a Page stage filter is utilized all the charts and Graphs are filtered with the
corresponding filter which is applied. We can have specific web page stage filters utilized at
exclusive pages of the filter.
 Report Level à A single filter for the whole record which ability all the charts / Graphs at
exceptional pages are utilized with the corresponding filters.

Q164. Explain Bookmark in Power BI?

Bookmarks are sort of Shortcut used in Power BI to navigate to a sure web page that is configured at a
factor of time. We can toggle between a foremost file web page and a bookmark for handy comparison.
Bookmarks are static and all the filter or interactions which are configured won’t exchange till we edit
the bookmarks.

Q165. How can we slice and cube the information in a Power BI record Page?

We can slice and cube the facts of the usage of Slicers. Slicers are visible filters that offer interactivity to
the stop customers and permit the customers and slice and cube the data.

Q166. How many instances we can refresh a dataset in Power BI Pro and Power BI Premium?

A dataset can be refreshed eight instances a day in Power BI Pro and forty eight instances in Power BI
Premium. If we have 5 extraordinary datasets, we can refresh forty instances a day (8*5)

Q167. How can we mix records from two tables X & Y that are coming from a Teradata tables which
have equal Structure?

We can use “Append Queries” in Power Query editor to mix the tables due to the fact of the gain of
having the identical desk structure.

Q168. What is the distinction between Merge and Append Queries in Power Query Editor?

Append: Result of two greater tables are mixed as a single query. If there are two tables with 50 & one
hundred rows respectively, we can append the distinction queries as a single query. For Append it is
suggested that each the tables need to be in a identical desk structure. The quantity of columns in each
the tables must be in a equal name. In case a new column is introduced in any one of the table, the
appended question will additionally have the greater column and desk which will preserve null price for
the archives which are associated to the dataset which does now not have the extra columns. Also, the
Append question will permit duplicates.

Merge: Merge Queries are type of Combining two exceptional queries based totally on a matching
values. Merge queries will no longer appear for the Columns to mix the records however the row values.
Merge are comparable to Joins in phrases are SQL and there are 6 unique joins supplied in Power BI.

Q169. What is the Syntax of DATEADD DAX Function?

DATEADD(,,)
* Dates à A Date columns , It can be from the Calendar Table.
* Number_of_intervals à Interval to add or Subtract the date cost from the given Date.
* Interval à The Interval can be 12 months or Month or Quarter or Day.
19. What is the distinction Between Filter and Slicer?
Filters are used to restriction the records measurement or we can Target some particular areas with the
aid of Providing a filter. Filter does now not grant the interactions between the consumer and the
Dashboard. Whereas Slicer are extra interactive & it is extra for Slicing and dicing the data.

Q170. How are specific datatypes are handy in Power BI?

 Decimal Numbers & Whole Numbers

 Text
 Date & Date time
 Boolean
 Binary

Q171. What are the exclusive roles accessible in Power BI Service?

 Admin
 Contributor
 Member
 Viewer

Q172. What is Cardinality?

Cardinality capacity the relationship which is set up between two tables in the Modelling view of Power
BI. We can discover if the tables have awesome of multi cases per price for the becoming a member of
columns between the two tables.

Q173. What are the exceptional Cardinalities reachable in Power BI?

 One to One
 One to Many
Q174. What is the issue in phrases of Size of Data in Power BI Premium and Power BI Pro?

In Power BI Pro we can cope with up to 1Gb of statistics & in Power BI Premium we can Handle 10 Gb of
Data per User.

70. State the difference between Count and CounD function.

Count function returns to count, excluding NULL values whereas Countd returns distinct values which
exclude NULL values.

71. Explain DATEDD function in Power BI.

DATEDD function helps you to convert any input to a date format. This input can number, string, or a
data type input.

72. What does DATENAME function do?

DATENAME function shows the name of the specific part of the date that is given.

73. What is the DATEPART function?

It returns date function as an integer. However, DATENAME function does the same thing, except it
returns the name of the part of the date.

74. What does DATEDIFF function do?

This function gives a difference between 2 dates based on the specified Date part.

75. What is the use of ENDSWITH function?

ENDSWITH function helps you to return the logical result to the given string. In case If the sub-string is
available at the end of the sub string, then it returns TRUE.

76. What is IFNULL function?

If the value is not NULL iFNULL function result is the first expression, if it is not, then it will return the
second expression.

77. What is the use of INDEX Function in Power BI?

INDEX function helps you to retrieve the index of the respective row.

78. What is the main difference between LTRIM and RTRIM?

LTRIM function helps you to remove the white space from the LEFT of the string. RTRIM helps you to
remove it from the right the last index.
79. What is the use of MID function?

MID function returns the string character from the specified index position.

80. How can you apply percentile function in Power BI?

PERCENTILE function allows you to returns the data value of the specific percentile number.

81. What is the SIGN function?

Sign function returns the direction of the values. If it returns 1, if positive then 1, if 0 then 0.

82. What is the use of statewith function?

This function returns the logical answers TRUE if the sub-string is the starting string for the superstring.
If it is not, it will return false.

83. What is the use of split function?

SPLIT function is used to split the string database on the given delimiter.

84. What is the need of signing up with business email?

Power BI is not supporting, the email address given by telecommunications or consumer email service
providers. Thus, there is need for signing up with work email.

85. Name the market place where you can download the Power BI mobile apps.

 App store
 Google Play
 Window Store

86. In which year Microsoft invented Power BI Embedded?

In, the year 2016 Microsoft discharged new administration which is named as Power BI Embedded.

87. What area do you go to change and reshape data in Power BI?

Data Editing helps you to change and reshape data in Power BI.

88. Data Category settings should be used for mapping information to geographic areas in Power BI?

Scope and Longitude (e.g. 47.6080, - 122.3351)

89. What do you use as a consolidate inquiry in Power BI?

Join Queries are used as a consolidate inquiries in Power BI

90. What is the option to unpivot data in the question proofreader?

Unpivot line is a choice to unpivot any data in the question proofreader.

91. What is the process to refresh Power BI reports when it is uploaded into the cloud?

Power BI, reports can be refresh using Data management, gateway, and Power BI Personal Gateway.

92. What visual would you need to use to show solitary esteem?

Disseminate Plat is a visual you should use to show solitary esteem.

93. Which of the following Power BI memberships allows you the right permit to see Power BI content
with the various Power BI applications?

Power BI Premium P1 is an application which gives this type of solutions for you.

94. Which field composes can be added to the Field well of a Slicer visual?

Computing segments should be made with a DAX articulation.

95. What is the M dialect capacity to sort a segment in inquiry supervisor?

The command for this purpose is Sort-> Table

96. Which Power in BI visual is ideal for estimating execution against an objective?

Projectile Power Bi visual is most fitting for this purpose.

97. What context style is allowed by Power BI DAX?

Power BI DAX content style is both Row and Filter.

98. In, Power Bi, where do you discover the Q&A highlight?

You will get at the highest point of a report in the Power BI benefit.

99. How can you dynamically change the value measure to show multiple measures?

You can do this by using harvesting measure and switch function.

100. What is self-service business intelligence?

Self-service Business Intelligence SSBI is a method to deal with data investigation which empowers
business clients to channel, fragment, and break down their data, without top to the bottom specific
information.
101. What is Power BI DAX style?

It is both Row and Filter Context

102. How can I purchase Power BI suite?

You can purchase Power BI licenses version directly from their website www.powerbi.com.

Question 2. Explain When Do You Use Sumx() Instead Of Sum()?

Answer : When the expressions to SUM() consits of anything else than a column name.

Typically when you want to add or multiply the values in different columns:

SUMX(Orderline, Orderline[quantity], Orderline[price])

SUMX() first creates a row context over the Sales table (see 1 above). It then iterates through this table
one row at a time. SUM() is optimized for reducing over column segments and is as such not an iterator.

Question 3. What Do You Understand By New Calendarauto() Function In Dax(ssas)?

Answer : CALENDARAUTO function returns a table with a single column named “Date” that contains a
contiguous set of dates. The range of dates is calculated automatically based on data in the model.

Example: In this example, the MinDate and MaxDate in the data model are July 1, 2010 and June 30,
2011.

CALENDARAUTO() will return all dates between January 1, 2010 and December 31, 2011.

CALENDARAUTO(3) will actually return all dates between April 1, 2010 and March 31, 2012.

Question 4. Name Any 3 Most Useful Aggregation Functions Dax?

Answer : DAX has a number of aggregation functions, including the following commonly used
functions:

 SUM
 AVERAGE
 MIN
 MAX
 SUMX (and other X functions)

These functions work only on numeric columns, and generally can aggregate only one column at a time.
However, special aggregation functions that end in X, such as SUMX, can work on multiple columns.
These functions iterate through the table, and evaluate the expression for each row.
Question 5. Which Are The Three Places Where An Expression Can Be Evaluated And Hence A Specific
Context Is Set?

Answer : In a pivot table cell. Filter context is set by rows, columns, filters and slicers.

 In a row cell (calculated column) Row context is set by the row itself.
 In the measure calculation area of a table. No row, nor filter context is set.

Question 6. Name Any 3 Most Useful Text Functions In Dax?

Answer : The text functions in DAX include the following:

 CONCATENTATE
 REPLACE
 SEARCH
 UPPER
 FIXED

These text work very similarly to the Excel functions that have the same name, so if you're familiar with
how Excel handles text functions, you're already a step ahead. If not, you can always experiment with
these functions in Power BI, and learn more about how they behave.

Question 7. How Is Filter Context Propagated Through Relationships?

Answer : Filter context automatically propagates following the filtering of the relationship. It always
propagates from the one side of the relationship to the many side. In addition, you also have the
<u>option of enabling the propagation from the many side to the one side</u>. No functions are
available to force the propagation: Everything happens inside the engine in an automatic way, according
to the definition of relationships in the data model.

Question 8. What Is The Difference Between Distinct() And Values() In Dax?

Answer : Both count the distinct values, but VALUES() also counts a possible implictit virtual empty row
because of non matching values in a child table. This is usually in a dimension table.

Question 9. Which Function Should You Use Rather Than Countrouws(distinct())?

Answer : DISTINCTCOUNT()

Question 10. What Is A Pattern?

Answer : A pattern is a general reusable solution to a commonly occurring problem.In Microsoft Excel,
you use patterns every day to build tables, charts, reports, dashboards, and more.

Question 11. What Are Dax Patterns?

Answer : DAX Patterns is a collection of ready-to-use data models and formulas in DAX, which is the
programming language of Power Pivot. Create your Excel data model faster by using a DAX pattern
Question 12. Explain Related() And Relatedtable()?

Answer : RELATED works when you have a row context on the table on the many side of a relationship.
RELATEDTABLE works if the row context is active on the one side of a relationship. It is worth noting that
both, RELATED and RELATEDTABLE, can traverse a long chain of relationships to gather their result; they
are not limited to a single hop.

Question 13. Explain How A Pivot Table Can Be Viewd As An Mvc System?

Answer :

1. Model = the Data Model (incl DAX expressions)

2. View = the table (or chart)
3. Controller = rows + columns + filters + slicers

What can you say about automatic filter propagation The filters only ever automatically flow from the
"one" side of the relationship to the "many" side of the relationship; from the "arrow" side to the "dot"
side; from the lookup table to the data table—whatever terms you use, it's always downhill.

With the lookup tables above and the data tables below, it is a mental cue to help you instantly visualize
how automatic filter propagation works.

Question 14. How Does Calculate() Result In Context Transition?

Answer : When in row context it transitions to filter context: the filter on the rows of a specific table
propagates through the relationship to the related before the calculation is completed.

E.g. CALCULATE(SUM(OtherTable[column]) in a calculated column.

It extends or modifies an existing filter context, by adding a filter as its second parameter.

CALCULATE() always introduces filter context.

Question 15. What Is The Difference Between Max And Maxa Functions In Dax?

Answer : The MAX function takes as an argument a column that contains numeric values. If the column
contains no numbers, MAX returns a blank. If you want to evaluate values that are not numbers, use the
MAXA function.

Question 16. How Are Row Contexts Created?

Answer :

1. Automatically in a calculated column

2. Programmatically by using iterators.

Question 17. How Are Filter Contexts Created?

Answer :

1. Automatically by using fields on rows, columns, slicers, and filters.

2. Programmatically by using CALCULATE()

Question 18. How Can You Propagate Row Context Through Relationships?

Answer :

Propagation happens manually by using RELATED() and RELATEDTABLE(). These functions need to be
used on the correct side of a one-to-many relationship: RELATED() on the many side, RELATEDTABLE()
on the one side.

Question 19. How Does Summarizecolumns Relate To Filtering?

Answer :

1. SUMMARIZECOLUMNS is not susceptible for outer (external) filters, in contrast to SUMMARIZE

2. you can add a filter (e.g. using FILTER) as a param of SUMMARIZCOLUMNS and it will filter
accordingly. It acts as if you've added a filter in a pivot table.

Question 20. What Is The Initial Filter Context?

Answer :

The initial filter context comes from four areas of a pivot table:

1. Rows
2. Columns
3. Filters
4. Slicers

It is the standard filtering coming from a pivot table before any possible modifications from DAX
formulas using CALCULATE().

Question 21. Why Don't You Use A Calculate() In The Aggregation Expression Of A
Summarizecolumn()?

Answer : The CALCULATE() is automatically generated.

Question 22. What Is The Difference Between Having A Measure As A Second Filter Param And Having
The Original Measure Expression As A Param. Filter(table, [measure]) Vs Filter(table, Sum(...))?

Answer : A measure has always implicit filter context, so the ROW context induced by the FILTER is
transferred to filter context in the measure.<br>In case of the expression only, no filter context is
imposed on the expression, so the expression is evaluated with an empty filter context! This gives a
different result.
Question 23. Dax Nested Functions Is Equivalent To Sql What?

Answer : SQL subqueries.

SQL Interview:
Q5. What are joins in SQL?

A JOIN clause is used to combine rows from two or more tables, based on a related column between
them. It is used to merge two tables or retrieve data from there. There are 4 types of joins, as you can
refer to below:

 Inner join: Inner Join in SQL is the most common type of join. It is used to return all the rows
from multiple tables where the join condition is satisfied.

 Left Join: Left Join in SQL is used to return all the rows from the left table but only the matching
rows from the right table where the join condition is fulfilled.
 Right Join: Right Join in SQL is used to return all the rows from the right table but only the
matching rows from the left table where the join condition is fulfilled.
 Full Join: Full join returns all the records when there is a match in any of the tables. Therefore, it
returns all the rows from the left-hand side table and all the rows from the right-hand side table.

Q6. What is the difference between CHAR and VARCHAR2 datatype in SQL?

Both Char and Varchar2 are used for characters datatype but varchar2 is used for character strings of
variable length whereas Char is used for strings of fixed length. For example, char(10) can only store 10
characters and will not be able to store a string of any other length whereas varchar2(10) can store any
length i.e 6,8,2 in this variable.
Q7. What is a Primary key?

 A Primary key in SQL is a column (or collection of columns) or a set of columns that uniquely
identifies each row in the table.
 Uniquely identifies a single row in the table
 Null values not allowed

Example- In the Student table, Stu_ID is the primary key.

Q8. What are Constraints?

Constraints in SQL are used to specify the limit on the data type of the table. It can be specified while
creating or altering the table statement. The sample of constraints are:

 NOT NULL
 CHECK
 DEFAULT
 UNIQUE
 PRIMARY KEY
 FOREIGN KEY

Q9. What is the difference between DELETE and TRUNCATE statements?

DELETE vs TRUNCATE

DELETE TRUNCATE

Truncate is used to delete all the rows from a

Delete command is used to delete a row in a table.
table.

You can rollback data after using delete statement. You cannot rollback data.

It is a DML command. It is a DDL command.

It is slower than truncate statement. It is faster.

Q10. What is a Unique key?

 Uniquely identifies a single row in the table.

 Multiple values allowed per table.
 Null values allowed.

Apart from this SQL Interview Questions blog, if you want to get trained from professionals on this
technology, you can opt for structured training from edureka!

Q11. What is a Foreign key in SQL?

 Foreign key maintains referential integrity by enforcing a link between the data in two tables.
 The foreign key in the child table references the primary key in the parent table.
 The foreign key constraint prevents actions that would destroy links between the child and
parent tables.

Q12. What do you mean by data integrity?

Data Integrity defines the accuracy as well as the consistency of the data stored in a database. It also
defines integrity constraints to enforce business rules on the data when it is entered into an application
or a database.

Q13. What is the difference between clustered and non-clustered index in SQL?

The differences between the clustered and non clustered index in SQL are :

1. Clustered index is used for easy retrieval of data from the database and its faster whereas
reading from non clustered index is relatively slower.
2. Clustered index alters the way records are stored in a database as it sorts out rows by the
column which is set to be clustered index whereas in a non clustered index, it does not alter the
way it was stored but it creates a separate object within a table which points back to the original
table rows after searching.

3. One table can only have one clustered index whereas it can have many non clustered index.

Q14. Write a SQL query to display the current date?

In SQL, there is a built-in function called GetDate() which helps to return the current timestamp/date.

Q15.What do you understand by query optimization?

The phase that identifies a plan for evaluation query which has the least estimated cost is known as
query optimization. The advantages of query optimization are as follows:

 The output is provided faster

 A larger number of queries can be executed in less time
 Reduces time and space complexity
Q16. What do you mean by Denormalization?

Denormalization refers to a technique which is used to access data from higher to lower forms of a
database. It helps the database managers to increase the performance of the entire infrastructure as it
introduces redundancy into a table. It adds the redundant data into a table by incorporating database
queries that combine data from various tables into a single table.

Q17. What are Entities and Relationships?

Entities: A person, place, or thing in the real world about which data can be stored in a database. Tables
store data that represents one type of entity. For example – A bank database has a customer table to
store customer information. The customer table stores this information as a set of attributes (columns
within the table) for each customer.

Relationships: Relation or links between entities that have something to do with each other. For
example – The customer name is related to the customer account number and contact information,
which might be in the same table. There can also be relationships between separate tables (for example,
customer to accounts).

Q18. What is an Index?

An index refers to a performance tuning method of allowing faster retrieval of records from the table.
An index creates an entry for each value and hence it will be faster to retrieve data.

Q19. Explain different types of index in SQL.

There are three types of index in SQL namely:

Unique Index:

This index does not allow the field to have duplicate values if the column is unique indexed. If a primary
key is defined, a unique index can be applied automatically.

Clustered Index:

This index reorders the physical order of the table and searches based on the basis of key values. Each
table can only have one clustered index.

Non-Clustered Index:

Non-Clustered Index does not alter the physical order of the table and maintains a logical order of the
data. Each table can have many non-clustered indexes.

Q20. What is Normalization and what are the advantages of it?

Normalization in SQL is the process of organizing data to avoid duplication and redundancy. Some of the
advantages are:
  Better Database organization
 More Tables with smaller rows
 Efficient data access
 Greater Flexibility for Queries
 Quickly find the information
 Easier to implement Security
 Allows easy modification
 Reduction of redundant and duplicate data
 More Compact Database
 Ensure Consistent data after modification

Apart from this SQL Interview Questions Blog, if you want to get trained from professionals on this
technology, you can opt for structured training from edureka!

Q21. What is the difference between DROP and TRUNCATE commands?

DROP command removes a table and it cannot be rolled back from the database whereas TRUNCATE
command removes all the rows from the table.

Q22. Explain different types of Normalization.

There are many successive levels of normalization. These are called normal forms. Each consecutive
normal form depends on the previous one.The first three normal forms are usually adequate.

 First Normal Form (1NF) – No repeating groups within rows

 Second Normal Form (2NF) – Every non-key (supporting) column value is dependent on the
whole primary key.
 Third Normal Form (3NF) – Dependent solely on the primary key and no other non-key
(supporting) column value.

Q23. What is the ACID property in a database?

ACID stands for Atomicity, Consistency, Isolation, Durability. It is used to ensure that the data
transactions are processed reliably in a database system.

 Atomicity: Atomicity refers to the transactions that are completely done or failed where
transaction refers to a single logical operation of a data. It means if one part of any transaction
fails, the entire transaction fails and the database state is left unchanged.
 Consistency: Consistency ensures that the data must meet all the validation rules. In simple
words, you can say that your transaction never leaves the database without completing its
state.
 Isolation: The main goal of isolation is concurrency control.
 Durability: Durability means that if a transaction has been committed, it will occur whatever
may come in between such as power loss, crash or any sort of error.
Q24. What do you mean by “Trigger” in SQL?

Trigger in SQL is are a special type of stored procedures that are defined to execute automatically in
place or after data modifications. It allows you to execute a batch of code when an insert, update or any
other query is executed against a specific table.

Q25. What are the different operators available in SQL?

There are three operators available in SQL, namely:

1. Arithmetic Operators
2. Logical Operators
3. Comparison Operators

Apart from this SQL Interview Questions blog, if you want to get trained from professionals on this
technology, you can opt for structured training from edureka!

Q26. Are NULL values same as that of zero or a blank space?

A NULL value is not at all same as that of zero or a blank space. NULL value represents a value which is
unavailable, unknown, assigned or not applicable whereas a zero is a number and blank space is a
character.

Q27. What is the difference between cross join and natural join?

The cross join produces the cross product or Cartesian product of two tables whereas the natural join is
based on all the columns having the same name and data types in both the tables.

Q28. What is subquery in SQL?

A subquery is a query inside another query where a query is defined to retrieve data or information back
from the database. In a subquery, the outer query is called as the main query whereas the inner query is
called subquery. Subqueries are always executed first and the result of the subquery is passed on to the
main query. It can be nested inside a SELECT, UPDATE or any other query. A subquery can also use any
comparison operators such as >,< or =.

Q29. What are the different types of a subquery?

There are two types of subquery namely, Correlated and Non-Correlated.

Correlated subquery: These are queries which select the data from a table referenced in the outer
query. It is not considered as an independent query as it refers to another table and refers the column in
a table.

Non-Correlated subquery: This query is an independent query where the output of subquery is
substituted in the main query.
 SQL Interview Questions
Q30. List the ways to get the count of records in a table?

To count the number of records in a table in SQL, you can use the below commands:

SELECT * FROM table1

SELECT COUNT(*) FROM table1

SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2

Q31. Write a SQL query to find the names of employees that begin with ‘A’?

To display name of the employees that begin with ‘A’, type in the below command:

1SELECT * FROM Table_name WHERE EmpName like 'A%'

Q32. Write a SQL query to get the third-highest salary of an employee from employee_table?
1SELECT TOP 1 salary

2FROM(

3SELECT TOP 3 salary

4FROM employee_table

5ORDER BY salary DESC) AS emp

6ORDER BY salary ASC;

Q33. What is the need for group functions in SQL?

Group functions work on the set of rows and return one result per group. Some of the commonly used
group functions are: AVG, COUNT, MAX, MIN, SUM, VARIANCE.

Q34 . What is a Relationship and what are they?

Relation or links are between entities that have something to do with each other. Relationships are
defined as the connection between the tables in a database. There are various relationships, namely:

 One to One Relationship.

 One to Many Relationship.
 Many to One Relationship.
 Self-Referencing Relationship.
Q35. How can you insert NULL values in a column while inserting the data?

NULL values in SQL can be inserted in the following ways:

 Implicitly by omitting column from column list.

 Explicitly by specifying NULL keyword in the VALUES clause

Q36. What is the main difference between ‘BETWEEN’ and ‘IN’ condition operators?

BETWEEN operator is used to display rows based on a range of values in a row whereas the IN condition
operator is used to check for values contained in a specific set of values.

Example of BETWEEN:
SELECT * FROM Students where ROLL_NO BETWEEN 10 AND 50;
Example of IN:

SELECT * FROM students where ROLL_NO IN (8,15,25);

Q37. Why are SQL functions used?

SQL functions are used for the following purposes:

 To perform some calculations on the data

 To modify individual data items
 To manipulate the output
 To format dates and numbers
 To convert the data types

Q38. What is the need for MERGE statement?

This statement allows conditional update or insertion of data into a table. It performs an UPDATE if a
row exists, or an INSERT if the row does not exist.

Q39. What do you mean by recursive stored procedure?

Recursive stored procedure refers to a stored procedure which calls by itself until it reaches some
boundary condition. This recursive function or procedure helps the programmers to use the same set of
code n number of times.

Q40. What is CLAUSE in SQL?

SQL clause helps to limit the result set by providing a condition to the query. A clause helps to filter the
rows from the entire set of records.

For example – WHERE, HAVING clause.

Apart from this SQL Interview Questions Blog, if you want to get trained from professionals on this
technology, you can opt for a structured training from edureka! Click below to know more.

Q41. What is the difference between ‘HAVING’ CLAUSE and a ‘WHERE’ CLAUSE?

HAVING clause can be used only with SELECT statement. It is usually used in a GROUP BY clause and
whenever GROUP BY is not used, HAVING behaves like a WHERE clause.
Having Clause is only used with the GROUP BY function in a query whereas WHERE Clause is applied to
each row before they are a part of the GROUP BY function in a query.

Q42. List the ways in which Dynamic SQL can be executed?

Following are the ways in which dynamic SQL can be executed:

 Write a query with parameters.

 Using EXEC.
 Using sp_executesql.

Q43. What are the various levels of constraints?

Constraints are the representation of a column to enforce data entity and consistency. There are two
levels of a constraint, namely:

 column level constraint

 table level constraint

Q44. How can you fetch common records from two tables?

You can fetch common records from two tables using INTERSECT. For example:

Q45. List some case manipulation functions in SQL?

There are three case manipulation functions in SQL, namely:

 LOWER: This function returns the string in lowercase. It takes a string as an argument and
returns it by converting it into lower case. Syntax:

LOWER(‘string’)

 UPPER: This function returns the string in uppercase. It takes a string as an argument and
returns it by converting it into uppercase. Syntax:

UPPER(‘string’)

 INITCAP: This function returns the string with the first letter in uppercase and rest of the letters
in lowercase. Syntax:
INITCAP(‘string’)

Q46. What are the different set operators available in SQL?

Some of the available set operators are – Union, Intersect or Minus operators.

Q47. What is an ALIAS command?

ALIAS command in SQL is the name that can be given to any table or a column. This alias name can be
referred in WHERE clause to identify a particular table or a column.

For example-

Select emp.empID, dept.Result from employee emp, department as dept where

emp.empID=dept.empID

In the above example, emp refers to alias name for employee table and dept refers to alias name for
department table.

Q48. What are aggregate and scalar functions?

Aggregate functions are used to evaluate mathematical calculation and returns a single value. These
calculations are done from the columns in a table. For example- max(),count() are calculated with
respect to numeric.

Scalar functions return a single value based on the input value. For example – UCASE(), NOW() are
calculated with respect to string.

Q49. How can you fetch alternate records from a table?

You can fetch alternate records i.e both odd and even row numbers. For example- To display even
numbers, use the following command:

Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0

Now, to display odd numbers:

Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=1

Q50. Name the operator which is used in the query for pattern matching?

LIKE operator is used for pattern matching, and it can be used as -.

1. % – It matches zero or more characters.

For example- select * from students where studentname like ‘a%’

_ (Underscore) – it matches exactly one character.
For example- select * from student where studentname like ‘abc_’

Q51. How can you select unique records from a table?

You can select unique records from a table by using the DISTINCT keyword.

Select DISTINCT studentID from Student

Using this command, it will print unique student id from the table Student.

Q52. How can you fetch first 5 characters of the string?

There are a lot of ways to fetch characters from a string. For example:

Select SUBSTRING(StudentName,1,5) as studentname from student

Q53. What is the main difference between SQL and PL/SQL?

SQL is a query language that allows you to issue a single query or execute a single insert/update/delete
whereas PL/SQL is Oracle’s “Procedural Language” SQL, which allows you to write a full program (loops,
variables, etc.) to accomplish multiple operations such as selects/inserts/updates/deletes.

Q54. What is a View?

A view is a virtual table which consists of a subset of data contained in a table. Since views are not
present, it takes less space to store. View can have data of one or more tables combined and it depends
on the relationship.

Q55. What are Views used for?

A view refers to a logical snapshot based on a table or another view. It is used for the following reasons:

 Restricting access to data.

 Making complex queries simple.
 Ensuring data independence.
 Providing different views of same data.

Q56. What is a Stored Procedure?

A Stored Procedure is a function which consists of many SQL statements to access the database system.
Several SQL statements are consolidated into a stored procedure and execute them whenever and
wherever required which saves time and avoid writing code again and again.
Q57. List some advantages and disadvantages of Stored Procedure?
Advantages:

A Stored Procedure can be used as a modular programming which means create once, store and call for
several times whenever it is required. This supports faster execution. It also reduces network traffic and
provides better security to the data.

Disadvantage:

The only disadvantage of Stored Procedure is that it can be executed only in the database and utilizes
more memory in the database server.

Q58. List all the types of user-defined functions?

There are three types of user-defined functions, namely:

 Scalar Functions
 Inline Table-valued functions
 Multi-statement valued functions

Scalar returns the unit, variant defined the return clause. Other two types of defined functions return
table.

Q59. What do you mean by Collation?

Collation is defined as a set of rules that determine how data can be sorted as well as compared.
Character data is sorted using the rules that define the correct character sequence along with options
for specifying case-sensitivity, character width etc.

Q60. What are the different types of Collation Sensitivity?

Following are the different types of collation sensitivity:

 Case Sensitivity: A and a and B and b.

 Kana Sensitivity: Japanese Kana characters.
 Width Sensitivity: Single byte character and double-byte character.
 Accent Sensitivity.

Q61. What are Local and Global variables?

Local variables:

These variables can be used or exist only inside the function. These variables are not used or referred by
any other function.
Global variables:

These variables are the variables which can be accessed throughout the program. Global variables
cannot be created whenever that function is called.

Q62. What is Auto Increment in SQL?

Autoincrement keyword allows the user to create a unique number to get generated whenever a new
record_is_inserted_into_the_table.
This keyword is usually required whenever PRIMARY KEY in SQL is used.

AUTO INCREMENT keyword can be used in Oracle and IDENTITY keyword can be used in SQL SERVER.

Q63. What is a Datawarehouse?

Datawarehouse refers to a central repository of data where the data is assembled from multiple sources
of information. Those data are consolidated, transformed and made available for the mining as well as
online processing. Warehouse data also have a subset of data called Data Marts.

Q64. What are the different authentication modes in SQL Server? How can it be changed?

Windows mode and Mixed Mode – SQL and Windows. You can go to the below steps to change
authentication mode in SQL Server:

 Click Start> Programs> Microsoft SQL Server and click SQL Enterprise Manager to run SQL
Enterprise Manager from the Microsoft SQL Server program group.
 Then select the server from the Tools menu.
 Select SQL Server Configuration Properties, and choose the Security page.

Q65. What are STUFF and REPLACE function?

STUFF Function: This function is used to overwrite existing character or inserts a string into another
string. Syntax:

STUFF(string_expression,start, length, replacement_characters)

where,
string_expression: it is the string that will have characters substituted

start: This refers to the starting position

length: It refers to the number of characters in the string which are substituted.

replacement_string: They are the new characters which are injected in the string.

REPLACE function: This function is used to replace the existing characters of all the occurrences. Syntax:

REPLACE (string_expression, search_string, replacement_string)

 DATA Modeling

1. What Are the Three Types of Data Models?

The three types of data models:

 Physical data model - This is where the framework or schema describes how data is physically
stored in the database.
 Conceptual data model - This model focuses on the high-level, user’s view of the data in
question
 Logical data models - They straddle between physical and theoretical data models, allowing the
logical representation of data to exist apart from the physical storage.

2. What is a Table?

A table consists of data stored in rows and columns. Columns, also known as fields, show data in vertical
alignment. Rows also called a record or tuple, represent data’s horizontal alignment.

3. What is Normalization?

Database normalization is the process of designing the database in such a way that it reduces data
redundancy without sacrificing integrity.

4. What Does a Data Modeler Use Normalization For?

The purposes of normalization are:

 Remove useless or redundant data

 Reduce data complexity
 Ensure relationships between the tables in addition to the data residing in the tables
 Ensure data dependencies and that the data is stored logically.

5. So, What is Denormalization, and What is its Purpose?

Denormalization is a technique where redundant data is added to an already normalized database. The
procedure enhances read performance by sacrificing write performance.

6. What Does ERD Stand for, and What is it?

ERD stands for Entity Relationship Diagram and is a logical entity representation, defining the
relationships between the entities. Entities reside in boxes, and arrows symbolize relationships.

7. What’s the Definition of a Surrogate Key?

A surrogate key, also known as a primary key, enforces numerical attributes. This surrogate key replaces
natural keys. Instead of having primary or composite primary keys, data modelers create the surrogate
key, which is a valuable tool for identifying records, building SQL queries, and enhancing performance.
8. What Are the Critical Relationship Types Found in a Data Model? Describe Them.

The main relationship types are:

 Identifying. A relationship line normally connects parent and child tables. But if a child table’s
reference column is part of the table’s primary key, the tables are connected by a thick line,
signifying an identifying relationship.
 Non-identifying. If a child table’s reference column is NOT a part of the table’s primary key, the
tables are connected by a dotted line, signifying a no-identifying relationship.
 Self-recursive. A recursive relationship is a standalone column in a table connected to the
primary key in the same table.

9. What is an Enterprise Data Model?

This is a data model that consists of all the entries required by an enterprise.

Intermediate Data Modeling Interview Questions

10. What Are the Most Common Errors You Can Potentially Face in Data Modeling?

These are the errors most likely encountered during data modeling.

 Building overly broad data models: If tables are run higher than 200, the data model becomes
increasingly complex, increasing the likelihood of failure
 Unnecessary surrogate keys: Surrogate keys must only be used when the natural key cannot
fulfill the role of a primary key
 The purpose is missing: Situations may arise where the user has no clue about the business’s
mission or goal. It’s difficult, if not impossible, to create a specific business model if the data
modeler doesn’t have a workable understanding of the company’s business model
 Inappropriate denormalization: Users shouldn’t use this tactic unless there is an excellent reason
to do so. Denormalization improves read performance, but it creates redundant data, which is a
challenge to maintain.

11. Explain the Two Different Design Schemas.

The two design schema is called Star schema and Snowflake schema. The Star schema has a fact table
centered with multiple dimension tables surrounding it. A Snowflake schema is similar, except that the
level of normalization is higher, which results in the schema looking like a snowflake.

12. What is a Slowly Changing Dimension?

These are dimensions used to manage both historical data and current data in data-warehousing. There
are four different types of slowly changing dimensions: SCD Type 0 through SCD Type 3.

13. What is Data Mart?

A data mart is the most straightforward set of data warehousing and is used to focus on one functional
area of any given business. Data marts are a subset of data warehouses oriented to a specific line of
business or functional area of an organization (e.g., marketing, finance, sales). Data enters data marts by
an assortment of transactional systems, other data warehouses, or even external sources.

14. What is Granularity?

Granularity represents the level of information stored in a table. Granularity is defined as high or low.
High granularity data contains transaction-level data. Low granularity has low-level information only,
such as that found in fact tables.

15. What is Data Sparsity, and How Does it Impact Aggregation?

Data sparsity defines how much data we have for a model’s specified dimension or entity. If there is
insufficient information stored in the dimensions, then more space is needed to store these
aggregations, resulting in an oversized, cumbersome database.

16. What Are Subtype and Supertype Entities?

Entities can be broken down into several sub-entities or grouped by specific features. Each sub-entity
has relevant attributes and is called a subtype entity. Attributes common to every entity are placed in a
higher or super level entity, which is why they are called supertype entities.

17. In the Context of Data Modeling, What is the Importance of Metadata?

Metadata is defined as “data about data.” In the context of data modeling, it’s the data that covers what
types of data are in the system, what it’s used for, and who uses it.

Post Graduate Program In Data Science

The Ultimate Ticket To Top Data Science Job RolesExplore Course

Advanced-Data Modeling Interview Questions

18. Should All Databases Be Rendered in 3NF?

No, it’s not an absolute requirement. However, denormalized databases are easily accessible, easier to
maintain, and less redundant.

19. What’s the Difference Between forwarding and Reverse Engineering, in the Context of Data Models?

Forward engineering is a process where Data Definition Language (DDL) scripts are generated from the
data model itself. DDL scripts can be used to create databases. Reverse Engineering creates data models
from a database or scripts. Some data modeling tools have options that connect with the database,
allowing the user to engineer a database into a data model.
20. What Are Recursive Relationships, and How Do You Rectify Them?

Recursive relationships happen when a relationship exists between an entity and itself. For instance, a
doctor could be in a health center’s database as a care provider, but if the doctor is sick and goes in as a
patient, this results in a recursive relationship. You would need to add a foreign key to the health
center’s number in each patient’s record.

21. What’s a Confirmed Dimension?

If a dimension is confirmed, it’s attached to at least two fact tables.

22. Why Are NoSQL Databases More Useful than Relational Databases?

NoSQL databases have the following advantages:

 They can store structured, semi-structured, or unstructured data

 They have a dynamic schema, which means they can evolve and change as quickly as needed
 NoSQL databases have sharding, the process of splitting up and distributing data to smaller
databases for faster access
 They offer failover and better recovery options thanks to the replication
 It’s easily scalable, growing or shrinking as necessary

23. What’s a Junk Dimension?

This is a grouping of low-cardinality attributes like indicators and flags, removed from other tables, and
subsequently “junked” into an abstract dimension table. They are often used to initiate Rapidly
Changing Dimensions within data warehouses.

24. If a Unique Constraint Gets Applied to a Column, Will It Generate an Error If You Attempt to Place
Two Nulls in It?

No, it won’t, because null error values are never equal. You can put in numerous null values in a column
and not generate an error.