Tag: Power BI Developer

Super Fast Data Loads to Prod
When you have really large data models loaded in powerbi.com there are challenges around quickly loading gigs of data into a data model. This is particularly challenging when working with data models in production. When changes are made which require a full load of data tables this can take hours to refresh. This means when a production change needs to be made, this should be done in low use times, evenings or over the weekend. What if we could greatly increase the load speed? but, we can!!

TL/DR

Let us cut to the chase. How do we solve this issue? We use Power BI backups with Azure blob storage. This feature can be found here on the official Microsoft Documentation.

Below is a high level architecture and movement of the Power BI data models across the blob storage account.

The Solution

The high level solution goes something like this:
1. Create two workspaces, one in prod and one for pre-loading data
2. Connect both workspaces to premium
3. Turn both premium capacities to large dataset capacities
4. Link azure storage to both workspaces
5. Publish a modified data model into the pre-load workspace, this contains the changes to the data model.
6. kick off a full load of the data model in the pre-load workspace
7. Open SSMS connect to the backup the pre-load workspace XMLA endpoint
8. Backup the pre-loaded model to azure blob storage using SSMS
9. Open azure bob storage, copy and paste the .abf file into the prod workspace
10. Use SSMS to connect to the prod workspace XMLA endpoint
11. Use the restore feature from SSMS to load the .abf file back into the model in production
Instructions

Below are the step by step instructions as show throughout the experience.

When you link your Power BI workspaces you will see inside the Blob storage a folder for powerbi and another folder for power-bi-backup. Note, it is important that both workspaces the pre-loading data workspace and the prod workspace point to the same blob storage account.

For each workspace PowerBI.com will create a single folder for each workspace. See below we have two workspaces for this demo.

We can use the XMLA endpoint found in the workspace settings to connect and see the partitions within our data model. For this example we have a pre-loaded dataset called nyctaxi. This dataset was deployed into a workspace and fully refreshed loading all the data into all the partitions.

Below is a the same dataset, with the exception there are no partitions preloaded. We assume we are going to use the backup created from the above dataset and then load the partitions into the Prod model.

Note: in this example we are pushing all the partitions and all data in the partitions into production. Usually your production model will already have partitions. Thus, it is easier to show our model adding partitions for this demo. If you really want to confirm the data is correctly being restored from backups you would look at the row counts found in each partition.

Here we log into the pre-loaded data set using SSMS.

Right Click on the data model that you are going to backup. Select the option labeled Back Up… in the drop down menu.

After clicking Back Up… a menu will appear letting you to name the backup file.

It is recommended to allow the file to be overwritten and to apply compression to the files. Once your settings have been configured, then you can Click the OK button at the bottom of the window to accept the changes.

After the backup completes we will be able to see the .abf file inside the blob storage folder.

Using the copy and paste buttons form the azure portal you move the files between the workspace folders.

Below is the copied model .abf file from the demo-synapse (pre-loaded data) into the Prod workspace named (Demo – Test Models).

In SSMS we connect to the production workspace. Again Right click on the data model and select Restore… shown in the drop down menu.

The restore menu will appear. In this menu clicking on the Browse button will bring up another window letting the user to select the data model from the storage account.

Note: on this screen you will need to select Allow database overwrite and it is optional to select if you want to Overwrite security

Below is a screen shot of the selection screen for picking the model

Once the restore has completed we can go back to our production model and see we now have partitions loaded.

If you like the content from PowerBI.Tips please follow us on all the social outlets. Stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.

Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:

Check out the new Merch!

Hasta La Vista Data

Go Ahead Make My Data

PBIX Hat
September 20, 2022
Power BI Architecture in a Data Solution
This article will focus on Power BI architecture within a data solution.
In this context, Power BI architecture describes how Power BI can slot in as a piece of this strategy. For instance, this includes not only the reports, but the data retrieval, storage and machine learning involved. Next, it discusses different roles and responsibilities involved. This can expand on Power BI skills, looking at the entire solution.

In addition, we hope it to provide ideas for current developers looking to expand Power BI skills or change directions in their career. It can provide a look at areas of need in organizations and give thought of learning opportunities available.

Power BI is Greater than a Report

In my previous article, I discussed how Power BI should not be thought of as a separate product to ETL, AI/ML or overall data strategy. Rather, organizations need to include Power BI architecture as part of a data culture with all of the products working in union.

As a recap from the last article, a modern data platform typically has 4 steps:
- Load and Ingest – extract the data out of the source system and transform it.
- Store – Land this data somewhere so we can run analysis on it.
- Process (or transform) – Run analytics on your data and draw out KPIs, AI and predictions.
- Serve – present this data in an easily way for stakeholders to consume it.
Medium Size – Power BI Services

It is possible to implement a reporting strategy entirely in Power BI. First, we can load data using dataflows. Next, these can be stored as a dataflow in the service or backed by Data Lake Gen 2 storage. It is good practice to separate our Power Query models and reports.

Large Size – Azure Services

Sometimes, we want to use more services than just Power BI. This may be due to huge datasets, use of data in other applications, or writing custom machine learning. The above diagram shows an enterprise scale reporting solution. Azure data factory can move and transform the data. Afterwards, we can store in a variety of storage options, depending on the nature of the data. Many options are available to run machine learning on the data. This ranges from custom code to autoML. Lastly, we create data models and we can produce reports off them. This integrates the Power BI architecture into a whole reporting solution. There is also a path for streaming data – through Event hub and Stream analytics.

Azure has a host of services available. If you are new to these, it can seem a lot to learn. Luckily, Microsoft are rolling out Synapse! This includes a portal that houses many of these services, enabling you to use them all in a single place. If interested, Nicola Ilic I has a great series on Synpase and Power BI.

Different Roles in a Data Solution

If we want to design a data culture, we often need more roles and skills than just designing reports. The below list looks to identify some different roles and responsibilities in a data solution. This is not intended to be a fully comprehensive list. Rather, we explore some different and common roles that could be involved within a project.

It is unlikely a single project will require all roles. Usually, one person may take on two or more of these roles. Instead, the aim is to distinguish different areas of the data strategy. This can help us to view Power BI in the bigger picture, seeing where it fits in.

We will look at the following architecture – a company that uses Synapse as well as dataflows. For simplicity, we are not looking at any streaming reports. In this diagram the dataflows storage is implied to make the diagram easier to understand.

Each role will look at some common responsibilities and skills. It will also highlight the area in the above architecture diagram that they are responsible for.

Power BI Developer

The Power BI Developer is responsible for building and owning data models for KPI and user reports. Therefore they will spend large parts of their time modeling and transforming data in Power Query or dataflows. In addition, a BI developer has good understanding of tabular models and how to write custom business logic in DAX. They may also be required to set up the Power BI architecture.

Skills

Expert in Power Query and DAX. Familiarity in tabular editor and DAX Studio. Plus knowledge on database designs and tabular modeling such as implementing good STAR schema. Great Power BI skills and ability on Power BI desktop.

Roles of a Power BI developer

UI/UX Engineer

In larger projects, report design can benefit from a UI/UX specialist. Power BI developers will often complete this, but specialists can be brought in to help design layouts, flow and brand consistency within the project. Usually, the Power BI developer will look after the models and logic. However, the UI/UX Engineer helps make sure the final reports are professional looking. In addition, they can be involved in storyboarding the design of reports. A UI/UX specialist is needed for projects with many reports, external facing reports or embedded solutions.

Skills

Design skills as in products such as Adobe Illustrator. Dashboard design (such as gestalt principles).

Roles of a UI/UX developer

Data Engineer

A Data Engineer is responsible to get the data from the source and load it into Power BI. Smaller reporting projects may use only dataflows or power query, but larger ones might require more steps. The Data Engineer will move the data using tools such as data factory into a SQL database or Synapse storage. This allows larger enterprise solutions with massive volumes of data, or for complex machine learning to be performed on the data. In addition, Data Engineers will transform and clean the data making it suitable for reporting analysis or machine learning. They will then integrate this back into the solution.

Skills

Tools such as Azure Data Factory, Stream Analytics, Data Lakes or other data storage. In some projects a Data Engineer might work exclusively with dataflows or Power Query. Databricks, Spark Analytics and SQL are important for prepping and transforming big data. Engineers also can benefit from automating in languages such as PowerShell. Strong Power BI skills.

Roles of a Data Engineer

Data Architect

Data Architects are responsible for designing, managing and maintaining the reporting solution. Architects will suggest the best selection of tools and methods used. They would be the ones to recommend the team involved, the technologies used and the correct approach. Overall, they will set up the Power BI Architecture and machine learning solution.

Skills

Architects need to have vast knowledge and experience across all the technologies that can be used. This includes deep knowledge on Azure or Synapse, Power BI and data governance methodologies. They have solid understanding how machine learning can be integrated into a solution.

Roles of an Data Architect

AI Engineer

You may incorporate an AI Engineer in large projects that have a machine learning focus. Data Scientists spend large amounts of time writing custom algorithms. In contrast, AI Engineers use tools such as Azure cognitive services or Azure machine learning studio. On large projects they may work in tandem with Data Scientists helping to merge their code into the reporting system. They often spend time cleaning, transforming and prepping data. In many cases, this job is regularly undertaken by Data Engineers. However, projects with a heavy AI focus may benefit on having an AI Engineer or both a Data and AI Engineer.

Skills

While AI Engineers may not write custom algorithms from scratch, they need a solid understanding of machine learning principles. They also need to know how to prepare and clean data ready for machine learning solutions.

Roles of an AI Engineer

Data Scientist

The data science field has exploded in popularity over the recent years. Typically, a Data Scientist will spend their time cleaning, prepping and analyzing large volumes of data. Next, they write custom algorithms that can detect deeper insights. A Data Scientist often has years of experience and training coming from various backgrounds. Data Scientists write custom code in Synapse, Databricks or Apache Spark notebooks.

Skills

Expert in an analytical programming language, typically R or Python. They will have a unique blend of programming skills and statistics. Deep knowledge of designing and implementing different machine learning algorithms. In addition, they will be proficient at cleaning and preparing data.

Roles of a Data Scientist

Conclusion

As we can see, there can be many different roles involved in a data solution. Many times, one must wear many hats. Other times, organizations can benefit from having several specialists in different areas.

Microsoft is looking to unify the Power BI architecture and overall data solution through Synapse. This portal will make these roles easier to be completed by fewer people within the same portal. Still, there will always be a need for specialists. So if you are looking to expand your power bi skills, or finding new areas to expand into, make sure you familiarize yourself with these.

If you like the content from PowerBI.Tips please follow us on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel. Or follow us on the social channels, Twitter and LinkedIn where we will post all the announcements for new tutorials and content.

Introducing our PowerBI.tips SWAG store. Check out all the fun PowerBI.tips clothing and products:
December 13, 2020