Get Data Flashcards
What is a flat-file?
A flat file is a type of file that has only one data table and every row of data is in the same structure. The file does not contain hierarchies. What is a flat-file? Likely, you’re familiar with the most common types of flat files, which are comma-separated values (.csv) files, delimited text (.txt) files, and fixed-width files.
How do you connect to data in a relational database?
You can use the Get data feature in Power BI Desktop and select the applicable option for your relational database.
What are the steps to connect to a relational database?
- Select get data
- Select the applicable option for your relational database.
- Enter your database server name and a database name in the SQL Server database window.
- Select the data connectivity mode (Import ot Direct Query)
What is a NoSQL database?
A NoSQL database (also referred to as non-SQL, not only SQL or non-relational) is a flexible type of database that does not use tables to store data.
How do you connect data in an application?
- Select Get data
- Select the option that you need from the Online Services category. (Ex. SharePoint Online List.)
- Select connect
What is Azure Analysis Services?
Azure Analysis Services is an Azure product that allows you to ingest data from multiple data sources, build relationships between the data, and creates calculations on the data. The calculations are built using data analysis expressions (DAX). Azure Analysis Services is similar to the data modeling and storage technology in Power BI
How do you connect to data in Azure Analysis Services?
You use the Get data feature in Power BI Desktop. When you select Analysis Services, you are prompted for the server address and the database name with two options: Import and Connect live.
What are the notable differences between Azure Analysis Services cubes and SQL Server?
Notable differences between Azure Analysis Services cubes and SQL Server are:
- 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).
What is Connect Live?
Connect live is a new option in Azure Analysis Services. Azure Analysis Services uses the tabular model and DAX to build calculations, similar to Power BI. These models are compatible with one another. Using the Connect live option helps you keep the data and DAX calculations in their original location, without having to import them all into Power BI.
What does Azure Analysis Services allow you to do with respect to refresh schedules?
Azure Analysis Services can have a fast refresh schedule, which means that when data is refreshed in the service, Power BI reports will immediately be updated, without the need to initiate a Power BI refresh schedule. This process can improve the timeliness of the data in your report.
How is connect live similar to a relational database?
You can choose the tables that you want to use. If you want to directly query the Azure Analysis Services model, you can use DAX or MDX.
What must you do to import a JSON type record?
JSON type records must be extracted and normalized before you can report on them, so you need to transform the data before loading it into Power BI Desktop.
After you have added your server and database names, you will be prompted to sign in with a username and password. You will have three sign-in options. What are they?
- Windows: Use your Windows account (Azure Active Directory credentials).
- Database: Use your database credentials. For instance, SQL Server has its own sign-in and authentication system that is sometimes used. If the database administrator gave you a unique sign-in to the database, you might need to enter those credentials on the Database tab.
- Microsoft account: Use your Microsoft account credentials. This option is often used for Azure services.
How to you connect your URL? (Sharepoint)
After you have selected Connect, you’ll be asked for your SharePoint URL. This URL is the one that you use to sign into your SharePoint site through a web browser. You do not need to enter your full URL file
path; you only need to load your site URL because, when you are connected, you can select the specific list that you want to load.
What do you need to after connecting your URL? (Sharepoint)
After you have entered your URL, select OK. Power BI needs to authorize the connection to SharePoint, so sign in with your Microsoft account and then select Connect.
How can you use a SQL query to import data?
Another way you can import data is to write an SQL query to specify only the tables and columns that you need.
To write your SQL query, on the SQL Server database window, enter your server and database names, and then select the arrow next to Advanced options to expand this section and view your options. In the SQL statement box, write your query statement, and then select OK.
What does SQL stand for?
SQL stands for Structured Query Language and is a standardized programming language that is used to manage relational databases and perform various data management operations.
What are additional features of using the SQL query?
The SQL query starts with a “Select” statement, which allows you to choose the specific fields that you want to pull from your database.
When using an SQL query to import data, what approach should you take with respect to the wildcard character (*) in your query.
When using an SQL query to import data, try to avoid using the wildcard character () in your query. If you use the wildcard character () in your SELECT statement, you import all columns that you don’t need from the specified table.
How do you import datasets when they are too large or when you have security requirements?
Power BI solves these problems by using the DirectQuery storage mode, which allows you to query the data in the data source directly and not import a copy into Power BI.
Why is using DirectQuery useful?
DirectQuery is useful because it ensures you are always viewing the most recent version of the data.
What are the three different types of storage modes you can choose from?
- Import
- DirectQuery
- Dual (Composite)
What is import mode?
The Import mode allows you to create a local Power BI copy of your datasets from your data source. You can use all Power BI service features with this storage mode, including Q&A and Quick Insights. However, data refreshes must be done manually. Import mode is the default for creating new Power BI reports.
What does DirectQuery allow for with respect to caching data and meeting security requirements?
The DirectQuery option is useful when you do not want to save local copies of your data because your data will not be cached. Instead, you can query the specific tables that you will need by using native Power BI queries, and the required data will be retrieved from the underlying data source. Using this model ensures that you are always viewing the most up-to-date data, and that all security requirements are satisfied.
How does DirectQuery help with large datasets?
This mode is suited for when you have large datasets to pull data from. Instead of slowing down performance by having to load large amounts of data into Power BI, you can use DirectQuery to create a connection to the source, solving data latency issues as well.
What does Dual Mode import allow you to do?
In Dual mode, you can identify some data to be directly imported and other data that must be queried. Any table that is brought in to your report is a product of both Import and DirectQuery modes. Using the Dual mode allows Power BI to choose the most efficient form of data retrieval.
What is Azure Analysis Services?
Azure Analysis Services is an Azure product that allows you to ingest data from multiple data sources, build relationships between the data, and creates calculations on the data. The calculations are built using data analysis expressions (DAX). Azure Analysis Services is similar to the data modeling and storage technology in Power BI.
How does getting data from Azure Analysis Services cubes similar to getting data from SQL Server?
Getting data from Azure Analysis Services cubes is similar to getting data from SQL Server, in that you can:
- Authenticate to the server.
- Pick the cube you want to use.
- Select which tables you need.
What is an acceptable alternative with respect to importing the data that you want (from Excel, SQL Server, and so on) into the Azure Analysis Services model and using a live connection. What are the advantages of this alternative?
You will likely import the data directly into Power BI. An acceptable alternative is to import all other data that you want (from Excel, SQL Server, and so on) into the Azure Analysis Services model and then use a live connection. Using this approach, the data modeling and DAX measures are all performed in one place, and it’s a much simpler and easier way to maintain your solution.
While importing data into Power BI, you may encounter errors resulting from factors such as?
While importing data into Power BI, you may encounter errors resulting from factors such as:
- Power BI imports from numerous data sources.
- Each data source might have dozens (and sometimes hundreds) of different error messages.
- Other components can cause errors, such as hard drives, networks, software services, and operating systems.
- Data can often not comply with any specific schema.
What is the purpose of the query timeout.
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.
What is the “Power BI Query Error: Timeout
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.
How do you resolve “What is the “Power BI Query Error: Timeout expired”?
You can resolve this error by pulling fewer columns or rows from a single table. While you are writing SQL statements, it might be a common practice to include groupings and aggregations. You can also join multiple tables in a single SQL statement. Additionally, you can perform complicated subqueries and nested queries in a single statement. These complexities add to the query processing requirements of the relational system and can greatly elongate the time of implementation.
What approach should you take if you need the rows, columns, and complexity?
If you need the rows, columns, and complexity, consider taking small chunks of data and then bringing them back together by using Power Query. For instance, you can combine half the columns in one query and the other half in a different query. Power Query can merge those two queries back together after you are finished.
What do you do when you encounter the “We couldn’t find any data formatted as a table” error while importing data from Microsoft Excel.
Occasionally, you may encounter the “We couldn’t find any data formatted as a table” error while importing data from Microsoft Excel. Fortunately, this error is self-explanatory. Power BI expects to find data formatted as a table from Excel. The error event tells you the resolution. Perform the following steps to resolve the issue:
- Open your Excel workbook, and highlight the data that you want to import.
- Press the Ctrl-T keyboard shortcut. The first row will likely be your column headers.
- Verify that the column headers reflect how you want to name your columns. Then, try to import data from Excel again. This time, it should work.
What approach should you take if you get the “Could not find file” error?
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.
Where can you pull data from?
You can pull data from files, relational databases, cubes, cloud-based applications, websites, and more.
When retrieving data sources, how do you need to treat them differently?
Retrieving data from different data sources requires treating each data source differently. For instance, Microsoft Excel data should be pulled in from an Excel table. Relational databases often have query timeouts. You can connect to cubes with Connect live which allows you to see data changes in real-time.
What do you need to consider when deciding on the correct storage mode?
It is important to select the correct storage mode for your data. Do you require that visuals interact quickly but don’t mind possibly refreshing the data when the underlying data source changes? If so, select Import to import data into Power BI. If you prefer to see updates to data as soon as they happen at the cost of interactivity performance, then choose Direct Query for your data instead.
How do you start shaping data in Power Query Editor?
To start shaping your data, open Power Query Editor by selecting the Transform data option on the Home tab of Power BI Desktop.
How does Power Query Editor track your work?
When you work in Power Query Editor, all steps that you take to shape your data are recorded. Then, each time the query connects to the data source, it automatically applies your steps, so your data is always shaped the way that you specified.
