Prepare - Get data Flashcards

Question

Types of storage mode?

Answer 1

Import: Queries submitted to the Power BI dataset that return data from Import tables can be fulfilled only from cached data. The Import mode allows you to create a local Power BI copy of your datasets from your data source. Direct Query: Tables with this setting aren't cached. Queries submitted to underlying data source in query language used by the data source. Create direct connection to the underlying data source Dual: Tables with this setting can act as either cached or not cached, depending on the context of the query that's submitted to the Power BI dataset.

Answer 2

In Model view you can toggle the table properties

Answer 3

Query performance: visuals and DAX send queries to the data model. By caching data in storage mode can boost the query performance and interactivity of your reports Large datasets: tables that aren’t cached don’t consume memory for caching purposes. Decide which tables are worth caching Data refresh optimisation: don’t need to refresh tables that aren’t cached. Reduce refresh times by only refreshing data that needs to be refreshed. Near real time requirements: tables with near real time requirements (e.g. instant updates) may benefit from not being cached to reduce data latency Writeback: Writeback enables business users to explore what-if scenarios by changing cell values. Custom applications can apply changes to the data source. Tables that aren't cached can display changes immediately, which allows instant analysis of the effects. Security: databases have added security features

Answer 4

The most popular way to use data in Power BI is to import it into a Power BI dataset. Importing the data means that the data is stored in the Power BI file and gets published along with the Power BI reports. This process helps make it easier for you to interact directly with your data

Answer 5

Limited DAX functions and some M language transformation constraints for DirectQuery

Answer 6

Azure Analysis Services is a fully managed platform as a service (PaaS) that provides enterprise-grade data models in the cloud. The data model provides an easier and faster way for users to perform ad hoc data analysis using tools like Power BI and Excel. Use advanced mashup and modelling features to combine data from multiple data sources, define metrics, and secure your data in a single, trusted tabular semantic data model.

Answer 7

Azure Analysis Services is an Azure product that allows you to: • ingest data from multiple data sources • build relationships between the data • creates calculations on the data (the calculations are built using data analysis expressions (DAX)) •Azure Analysis Services is similar to the data modelling and storage technology in Power BI. Uses the tabular model and DAX to build calculations

Answer 8

Get data -> Azure -> Azure analysis services database Sever and database parameters Connection option (Connect live, Import, Mixed/Direct Query) Authentications (Windows, basic, Microsoft account) Load

Answer 9

Similarities to SQL server Authenticate to the server. Pick the cube you want to use. Select which tables you need. Differences to SQL server Analysis Services cubes have calculations already in the cube, which will be discussed in more detail later. If you don’t need an entire table, you can query the data directly. Instead of using Transact-SQL (T-SQL) to query the data, like you would in SQL Server, you can use multi-dimensional expressions (MDX) or data analysis expressions (DAX). If you don’t need an entire table use MDX or DAX to query the data directly

Answer 10

Import Likely to directly import the data into Power BI However, we recommended you use live connections because Import mode does have some limitations; most notably, server performance might be impacted during import Connect Live Helps keep the data and DAX calculations in their original location without having to import them into Power BI Refreshed in the service so fast refresh schedule so no need to initiate a Power BI refresh schedule Improves Timeliness – above point Best practice is to import all the data into the Azure Analysis Services Model into Power BI and use a live connection. Using this approach the data modelling and Dax measures are all performed in one place which is easier to maintain your solution Similar to SQL databases you can choose which tables you are able to query the Azure Analysis services model using DAX or MDX

Answer 11

When attempting to import data into Power BI there can be a number of error messages that pop up

Answer 12

Relational source systems often have many people who are concurrently using the same data in the same database. Some relational systems and their administrators seek to limit a user from monopolizing all hardware resources by setting a query timeout. These timeouts can be configured for any timespan, from as little as five seconds to as much as 30 minutes or more.

Answer 13

This error indicates that you’ve pulled too much data according to your organization’s policies. Administrators incorporate this policy to avoid slowing down a different application or suite of applications that might also be using that database. Solutions: a) pull fewer columns or rows from a single table b) use groups and aggregations in SQL c) join multiple tables in one SQL statement

Answer 14

Power BI expects to find data formatted as a table from Excel. Solution: format excel as table

Answer 15

This is caused when a) moving files or b) renaming files or worksheets Solution: find the step that is causing the problem in Query Settings Usually, this error is caused by the file moving locations or the permissions to the file changing. If the cause is the former, you need to find the file and change the source settings. Open Power Query by selecting the Transform Data button in Power BI. Highlight the query that is creating the error. On the left, under Query Settings, select the gear icon next to Source.

Answer 16

Sometimes, when you import data into Power BI, the columns appear blank. This situation happens because of an error in interpreting the data type in Power BI. Solution: The resolution to this error is unique to the data source OR get SQL to change type

Answer 17

There is over 100 connectors and growing Most common are files, database and web services

Answer 18

Get data > Folder > Combine & Transform Format of the files needs to be consistent (e.g., CSV) Tables need to have the same format (e.g., sheet1) Target queries and helper queries support the folder data connection

Answer 19

Data sources and queries that are dependent on one another There is a top-bottom layout (which you can toggle)

Answer 20

``` Query settings (cog) - Change settings at the individual query level ``` Data source settings - Change the source for all queries at the same time - May need to edit and clear permissions

Answer 21

Credential and privacy levels required

Answer 22

Build permission

Answer 23

- Missing or greyed out buttons - No Power Query - No data view - Cannot change data source settings - Report-level measures will not be shared

Answer 24

- Data from files and folders

Answer 25

- Import Mode: caches it and can use all of the modelling features - DirectQuery: original data source is queried every time you interact with a visual However not all databases support DirectQuery

Answer 26

- A special type of DirectQuery for Analysis Services (data models) and Power BI Service (datasets) - It has tabular and multidimensional data - All calculations take place in the data model

Answer 27

- Import mode loads a copy into the .pbix file - The data is loaded into .pbix file in a compressed state - Power BI is built on in-memory columnar database engine - Consumes RAM and disk-space because it is saved in the .pbix file

Answer 28

- Development Machine: uses RAM and disk-space on machine | - Publish: RAM and disk-space on the server

Answer 29

- Can't load more data than Power BI or your hardware allows (e.g., limited import restrictions)

Answer 30

- VertiPaq Compression Technology

Answer 31

Depends on many factors including: - data type - values - cardinality of columns

Answer 32

- Use all functionality of Power BI: all Power Query transformations, DAX functions on models - Can combine import data from different sources (e.g., database and excel) - Speed of queries: little to no latency when accessing in-memory data - Can see Data and Model views

Answer 33

- No data is cached in Power BI - All data (apart from metadata) remains at the data source - Select the entities to connect to (these become tables in the data model)

Answer 34

- Metadata: column + table names + data types + relationships between tables

Answer 35

- .pbix file is smaller - Not limited by hardware of development machine or virtual server - Can use Model view

Answer 36

- Query performance slower: latency can be a couple seconds at best - Cannot see Data view - Virtual server could time out: takes too long or too many people using the server

Answer 37

DirectQuery has implications on functionality: Report performance - Depends on underlying data source hardware: a) not quicker than .pbix b) could event time out - May not be able to handle demand: lots of users sending queries from visuals to data source at once Not every query type usable - Not possible to use native queries with a) Common Table Expressions b) Stored procedures Limited data transformation functionality - Transformation limited: transformation steps performed every time a visual sends a query (compared to once every refresh with import) - "Not supported in DirectQuery mode": only most efficient transforms used in DirectQuery Data modelling limitations - No date tables which are required for Time Intelligence calculations (date/time columns in Import mode by default) - Calculate columns limited: a) only use current row of table on many-to-one relationships (this rules out data aggregation functions) - Only Calculate column functions that can be translated into native language supported

Answer 38

- Enhanced data modelling capabilities - Improved security features at data source: security always considers username of user viewing the report (unlike databases) - Can refresh as frequently as needed (Power BI Service limited to 8 or 48 with Premium)

Answer 39

- A mixture of import and DirectQuery - Dual mode is dynamic depending on the storage mode of other tables: Composite table has the ability to lookup Import and DirectQuery tables - If you change a Dual table to Import there is no going back - To use Dual you must first activate DirectQuery

Answer 40

- When Import and DirectQuery queries are present | - DirectQuery on multiple data sources

Answer 41

- Potential security risk: database administrator could have access to Import data

Answer 42

- Fields pane in the Data or Model view | - Model view > Advanced Selection > Properties

Answer 43

For the best experience you should import data but there are scenarios when you should not import: - Data model is too large for in-memory - High data velocity: need to see the most recent data (only if latency of database is quick enough) - Database administrators may only want to query the source to ease resource on own server

Answer 44

Intelli-Sense autocomplete feature lists the following: - Cacluated tables not supported by DirectQuery - Parent-child functions for building hierachies not avaiable (does in the database) - Building clusters (which relies on DAX) not supported =

Answer 45

TAKE PICTURE page 14

Answer 46

Live Connection - Max data model size: same dataset limits as Import OR limited by underlying data source hardware - Number of sources: only 1 - Data refresh: report shows latest data - Performance: best - Data transformation: none - Data modelling: Analysis Services and PBI services measures can be created with no restriction - Security: can leverage data source security based on current users login Direct Query - Max data model size: Limited only by underlying data source hardware - Number of sources: unlimited - Data refresh: report shows latest data - Performance: varies depending on data source latency - Data transformation: transformations limited to what can be translated to data source language - Data modelling: highly restricted - Security: RLS is defined at data source but only available for some data sources

Answer 47

- All store data in entities (converts into tables in Power BI's data model) - Sign in to access entities

Answer 48

- Use server address | - Have permission to access data within entities

Answer 49

- Parameters are fed into arguments and are an easy way to easily store and manage values that can be reused - Its purpose is to change the output of queries depending on their values

Answer 50

- Change the argument values for particular transforms and data source functions - Inputs in custom functions - Switching between development and custom environments - Configure incremental refresh

Answer 51

- Step argument: You can use a parameter as the argument of multiple transformations driven from the user interface (UI). - Custom Function argument: You can create a new function from a query and reference parameters as the arguments of your custom function.

Answer 52

- Name: provide easily recognisable name that differentiates from other parameters - Description: help understand its purpose - Required: The checkbox indicates whether subsequent users can specify whether a value for the parameter must be provided. -Type: Specifies the data type of the parameter (recommend setting up data type) Suggested Values: Provides the user with suggestions to select a value for the Current Value from the available options: Current value - Any value: The current value can be any manually entered value. - List of values: Provides you with a simple table-like experience so you can define a list of suggested values that you can later select from for the Current Value. - Query: Uses a list query (a query whose output is a list)

Answer 53

- From an existing query: Right-click a query whose value is a simple non-structured constant, such as a date, text, or number, and then select Convert to Parameter. - You can also convert a parameter to a query by right-clicking the parameter and then selecting Convert To Query.

Answer 54

- PBIDS files contain source connection details - You can export PBIDS from 'Data source settings' - It is the same as connecting to a new data source in 'Get Data' - Can help report creators to connect to specific datasets quicker (however you still need credentials so make sure the user has access)

Answer 55

- Dataflows allow you to share Power Query queries outside the Power BI or Excel app - Allows you to use Power Query in the Power BI Service

Answer 56

Create New Dataflow in the Power BI Service - Add new entities - add linked entities - import - create and attach

Answer 57

- Cannot create a dataflow in 'My Workspace' - Needs to refresh dataflow once created otherwise won't contain any data - Once refreshed you can use in Power BI desktop

Answer 58

- Size of data model - How much the underlying dataset changes - Company policies (e.g., data should not be imported)

Answer 59

- Allows you to connect to PBI datasets using other client tools to edit dataset - Only available for PBI premium and must be allowed in PBI Admin Portal