-
Lessons learned whilst working with Microsoft Fabric – Part 2: Power BI and Semantic Modelling
In part 1 of our initial lessons learned in Fabric blog, we looked at Data Engineering components. Our transformed dimensions and fact tables are now stored in Delta Parquet format within the Fabric Gold Lakehouse. Now its time to look at what we can do with the Power BI Fabric functionality. Lets build our Semantic… Read more
-
Lessons learned whilst working with Microsoft Fabric – Part 1: Data Engineering
Any time there is a big change in technology, there is a steep learning curve to go with it. Since Microsoft announced Fabric in May 2023 We have been working hard on getting up to speed with how Fabric works and how it changes the nature of what we do. What new architectures are available… Read more
-
The Microsoft data Journey so far. From SQL Server, Azure to Fabric
Having lived and breathed Microsoft for over 20 years, it is important to sometimes stop and think about all the changes over those years and all the growth and learning gained from each change to the analytics space. I started working with on premises Microsoft Products. We had a large room full of Microsoft 2000… Read more
-
Microsoft Fabric Part 18. Taskmaster Project. Adding more meta data to check throughout the Dimension and Fact run. More changes to the Pyspark code
Now we have updated our ProcessedFiles delta parquet, we can update the dimensions accordingly. It might be nice to have another delta parquet file here we can use to collect meta data for dims and facts. We will also want the processedDate in each dim and fact table. We are going to start with Delta… Read more
-
Microsoft Fabric Part 17. Taskmaster Project. Adding more Pyspark code and meta data to the Transformed Data file
In Part 16 we created a pipeline to run through our 5 Notebooks. We also ran some sql to check the data and found that Series 1 2 and 3 had been added 4 times into the delta parquet files. We want to add more information through the run so we can error check the… Read more
-
Microsoft Fabric Part 17. Taskmaster Project. The Pipeline. Run multiple notebooks from one Notebook activity using the foreach activity
In Part 16 we processed Series 4 of Taskmaster. we had a lot of issues along the way to resolve. Now we can create a pipeline to process all of the Notebooks we have created. We will start as simple as possible and try and build in better processes as we go along. New Data… Read more
-
Microsoft Fabric Part 16. Taskmaster Project. Update to Taskmaster transformed Notebook for Contestants
In Part 15 we created a delta load of transformed dims and facts in the gold layer of our Fabric Architecture. The Taskmaster file contains source_filename. e.g. Taskmaster_S1_01092015.csv Which we have used to partition the data. We also have a transformed Contestants file. Each contestant belongs to a series. the file isn’t big enough to… Read more
-
Microsoft Fabric Part 15. Taskmaster Project. Creating a Delta Load on Dims and Facts
In Part 14 we created a delta load of transformed data in the Silver layer of our Fabric architecture. We now want to try and delta load the dimensions and the fact table Our data is always new. It comes in and then doesn’t change so we don’t need to worry about updates. Just new… Read more
-
Microsoft Fabric Part 14. Taskmaster Project. Creating a Delta Load on Transformed Notebook
Previously, Our Notebooks take every csv file in the folder to transform. We don’t want to do this. We want to Load the Files that haven’t been processed. And further down the line we want to be able to do every file, Or just the unprocessed ones. We want to do a Delta Load. This… Read more
-
Microsoft Fabric Part 13. Changing Structure of Lakehouses
Before continuing on with this project, lets look at an amendment to the lake house structure. mostly because we decided we wanted to keep the structure of the Task Flow. Instead of having one Lakehouse for Gold Silver and Bronze. We want three lake houses. One each for Bronze Silver and Gold. 3 new lakehouses… Read more
-
Microsoft Fabric Part 12. Taskmaster Project. Pyspark Broadcast
In Part 10 we created the reporting and Project PBIP file. In Part 11 we looked at new Task flows and added our items into a medallion architecture. Its now time to do some small tweaks to the project so far. One thing I discovered is the Pyspark broadcast function. This function is used to… Read more
-
Microsoft Fabric Part 11. Taskmaster Project. New Fabric Feature, Task Flows
In Part 11 we created Power BI reporting and changed the usual PBIX to a PBIP Project file. Before moving on, lets have a quick look at a new feature in Fabric. Task Flows Back in the Fabric Workspace We can now build a task flow. But what is it? Its a new workspace feature… Read more
-
Microsoft Fabric Part 10. Taskmaster Project. Creating the Reporting and the Project File (PBIP)
So far we have created our Delta PARQUET files in the Delta Lake using Notebooks with Pyspark. We have created a Semantic Model – Storage mode Direct Lake in fabric Its time to create some visuals. but the question is. Do we create them in Fabric. or in a bpix file? Reporting Fabric or Pbix… Read more
-
Microsoft Fabric Part 9. Taskmaster Project. Creating the Semantic Model
The Semantic Model moving straight on from Part 8. We were in the SQL Analytics Endpoint. At the bottom of the screen we have a model view. This is the Default Model view This takes us to the Semantic Model Semantic Modelling in Fabric Lets update the default semantic model. Set the relationships. Hide Keys,… Read more
-
Microsoft Fabric Part 8. Taskmaster Project. Data checks using the SQL analytics Endpoint
SQL Analytics Endpoint Lets switch to the SQL analytics endpoint Only the Delta table are available with the SQL endpoint. Lets try some options New SQL Query We can use SQL to check that we are happy with what has been created Immediately we can spot some problems. There are 146 issues. You cant manipulate… Read more
-
Microsoft Fabric Part 7. Taskmaster Project. Creating the fact table
For our Taskmaster Posts 1 2 3 and 4 we had a really good look at Pyspark using an online dataset. but abandoned it when we realised that the data wasn’t quite as we wanted it and some data sets were missing. Parts 5 and 6 we got a new data set and created a… Read more
-
Microsoft Fabric Part 6. Taskmaster Project. Creating a Silver transformed layer and Finishing of the Dimensions
In part 4. A new Data source was used at task level and the first dimension was added. Lets quickly create the rest of the dims. Then we are ready to move on to the next steps in Fabric. Creating a Silver transformed layer There are a few changes that would be good to make… Read more
-
Microsoft Fabric Part 5. Taskmaster Project. Creating and using Shortcuts from an Azure Data Lake and creating the first dimension
Parts 1 2 3 and 4 were attempting to transform data into a star schema for power BI using notebooks in fabric. However there was missing data which meant we couldn’t go to the level of detail we wanted for the star (Episode level) Now we have a new data set and the csv files… Read more
-
Power BI Story Telling in PowerPoint. New Enhancements April 2024
A Previous Project we worked on had a specific requirement. Project Requirement Create reporting once a month and allow us to tell the story in narrative via text boxes. The narrative must show against the reports. The narrative must be formattable. Bold, italic. Bullet point, etc. Our users don’t want to go to Power BI… Read more
-
Microsoft Fabric Part 4. Taskmaster Project. Adding the fact table
In parts 1 and 2 we created and updated DimContestant In Part 3 we created DimTask, DimEpisode and DimDate Its time to create the fact table. the first thing we need to do is to get an understanding of what Facts we have and how they would join to the data we have created in… Read more
-
Microsoft Fabric Part 3. Taskmaster Project. Adding Dimensions: Tasks, Episodes and Date to Notebooks and creating Delta PARQUET and PARQUET Files
In Parts 1 and 2, we did a lot of analysis on the data. Understood Contestants and the data between the csv files attempts and people. Its now time to create some of the other dimensions we need for the project. DimTasks DimEpisodes DimDate Back to our Taskmaster Notebook to see what other data is… Read more
-
Microsoft Fabric Part 2. Taskmaster Project. Updating a dim Contestants table in Notebook and Delta PARQUET File
This blog is the next step for Microsoft Fabric Part 1. Taskmaster Project. Creating a dim Contestants table in Notebook and Delta PARQUET File In Part 1, a Contestants Dimension was created from the csv file attempts csv. The data from this was transformed and added into a PARQUET file and a DELTA PARQUET file so… Read more
-
Microsoft Fabric Part 1. Taskmaster Project. Creating a dim Contestants table in Notebook and Delta PARQUET File
Its so exciting at the moment with all the changes going on in the world of Microsoft and I wanted to use the trial version of Fabric to see if I could move my ‘standard’ working practices over into Fabric. The first thing was to find a practice dataset for this exercise so of course… Read more
-
Azure Synapse – Creating a Type 1 Upload with Pipelines
Working through exercises and learning paths for Microsoft Synapse is a really good way of becoming familiar with concepts and tools. And whilst these are a fantastic source of learning. the more you do, the more questions you end up asking, Why use this option over this option for example? SO, lets look at one… Read more
-
Power Query Editor – Avoiding the creation of all the helper Queries and Folders in power BI
A current project being worked on starts with a folder in Sharepoint with multiple files that need to be imported together (All with the same Spec) There are lots of groups of files to import into Power BI We need the File Name – it will be used to create the file snapshot date There… Read more
-
Power BI. Using flags in a junk dimension
We currently have a project where the metrics are actually flags to count whether a record is true or false rather than business metrics like Amount, SaleAmount etc Is something completed? 1 or 0 Is something in Development? 1 or 0 Is something Out of Scope ? 1 or 0 Etc. Now, if you left… Read more
-
Power BI Concatenating two measures together under one column header for a table display
Another requirement came through for a table that showed the current month figures against last months figures, However, to save space the two values were in the same column, last month contained in a () So part one was to create the date time intelligence for last month, And then to attempt to concatenate the… Read more
-
Power BI Composite Modelling (Multiple datasets Report) – Couldn’t load the data for this visual
This blog is for anyone using the new App experience (August 22) and has created a report using multiple datasets and the users can’t see the data We have A workspace A Dataflow Multiple Datasets A report using all the datasets An App with testers There are two testers with access to the testing report… Read more
-
Power BI – Deployment Pipeline Quick Tips – Setting dataflow Environment sources and Publishing (Direct Query) Datasets containing multiple Datasets
You need Premium or Premium Per user to work with Deployment Pipelines This happens right at the beginning of the Deployment Pipeline process when you have just added Dev to the Pipeline and you need to deploy Test and Prod Tip – Changing Data source Rules for Dataflow You now need to deploy your dev… Read more
-
Power BI – App Viewers can’t see the data in the report
We recently had an issue where a shared dataset (pbix) had been set up over a SQL Database. This was then published to Power BI A new pbix was created. Power Platform – Power BI datasets was chosen and the shared dataset was selected. Then reports were created and published to Service. An App was… Read more
-
Power BI Datamarts (New May 2022)
Difference between dataflows, datamarts and datasets Datasets Lets have a quick look at the history of the data set Here we see everything in one pbix file. Only one person can work with the file at any one time. We cant reuse anything or work on anything separately. Our dataset is in the one pbix… Read more
-
Azure Logic App – Copying a file from Sharepoint to a Data Lake
I have been asked to set up a Logic app in Azure (That is Power Automate for anyone outside Azure) to copy specific file(s) from a Sharepoint folder and add to an Azure Data Lake. The first example file is around 16,00 rows and not likely to grow too significantly. This is the same with… Read more
-
Power BI February 2022 Updates Dynamic M Query Parameters
Now supports SQL Server and more data sources But what are Dynamic M Query Parameters and what does this mean? It feels like they have been upgraded to use with direct query data sources so you can restrict the amount of data being asked for at the lowest level. Lets have a look at a… Read more
-
Power BI Admin APIs to return a list of email subscriptions
Get Dashboard Subscriptions brings back a list of everyone who has subscribed to a dashboard What is a Power BI Subscription? Subscriptions are a great way to assign yourself and other users to get emails regarding report content. There are certain governance rules we follow. Report viewers views content via an app. We don’t want… Read more
-
Power BI Sparklines
Sparklines are small charts you can add to a table or Matrix and are new for the start of 2022. Above we have a Matrix showing Products by country and I would like to also see this measure on a timeline. Select the metric Value : Add a Sparkline Button Here we choose the measure… Read more
-
Investigating the Power BI Scanner APIs Part 1
Delegating Permissions Part 1 will be an introduction to Scanner APIs and how you delegate permissions in order to now have to use the power BI Admin Account Power BI Scanner APIs are fairly new and there have been a lot of updates happening in the September October 21 Power BI Updates. These scanner APIS… Read more
-
Power BI DAX – Create a percentage across days of week (ALLEXCEPT. ALL. DIVIDE)
We just looked at the following request We have sold specific quantities of products. Can we look at a week for a year in a slicer and see the percentage of items sold for each day. Which days do we sell the most products overall? We have the following model (Just the parts that will… Read more
-
Power BI AI Visual Smart Narratives
Power BI has given us the following AI visuals: Key influencers Q&A Visualisation Decomposition Tree And now we also have smart narratives to work with. Many companies want to create report that includes text in regards to the report they are looking at to help the user understand the key takeaways. This usually involves someone… Read more
-
Power BI October 21 Updates GetUserArtifactAccessAsAdmin API through to PowerShell
There have been some new API updates (September October Power BI 2021) and I thought I would see what they were all about and how you can use them starting with the API mentioned in the October updates. I haven’t used these APIs before and I thought it would be interesting to see how the… Read more
-
Power BI – reporting on Items NOT in a filter
We are working on a report to look at Power BI activities and objects at the moment. Lets have a look at the model This is a test model with a date dimension (When the Activity occured) A Report dimension (Details of the report used in the activity) A Dataset dimension (Details about the dataset… Read more
-
Azure SQL Database Dev to Production Part 4
I have had quite a lot of issues in regards to the whole dev to prod process with the SQL database. my last attempt which I wrote in this blog worked well until I shut the project down. Then once reopened I would always lose my Project or GIT, so I went back to the… Read more
-
Power BI Premium Deployment Pipelines and Sub Workspaces – Publishing a live report to a pro workspace from a premium workspace
I wanted to have a look at a specific set up we have in regards to our Power BI Workspaces and apps We have Power BI Premium P1 Node We use dataflows We use a Deployment Pipeline for Dev Test and Production Premium Workspaces We have a scenario where we have a sub workspace with… Read more
-
Power BI Can you use an inactive join for a slicer (USERELATIONSHIP)
Imagine the scenario. You are dealing with two dates. date A and B. You create an active and an inactive relationship to your date table. You can create Dax with USERELATIONSHIP for any measures that use the inactive join. Great for the bar charts, line charts etc. But what if you have a slicer for… Read more
-
Power BI. Created Reports in your ‘shared dataset file’ and now want to move them to a report pbix file. How do you go about this task?
The recommended approach to Power BI as an enterprise solution is to have your ‘shared data set in a pbix file where you can just create test visuals to check everything is ok. Then create the real reports in a separate pbix file live connected to the dataset. But what happens if you create great… Read more
-
Microsoft Business Applications Summit 2021. Power BI Vision and Roadmap
Driving a Data Culture This is the 6th year of Power BI. Power BI has always been about driving data culture and has been rapidly evolving throughout its data journey. Power BI is top of the Gartner Magic Quadrant and growing exponentially across users and business. Lets have a look at where we have come… Read more
-
Autoscaling with Power BI Premium Gen 2
We have been working with Power BI Premium for a few weeks. Simply switching it on and seeing how it goes. However, Its worth paying some attention to Autoscaling that you can get with Premium Gen 2 (Preview) Auto Scaling Previously our Power BI Premium capacity could struggle when there was high capacity. For example… Read more
-
Quick Guide of Power BI Premium Features
It can be quite infuriating to be a Power BI Pro user and constantly be on the verge of doing something, before realising that its only available with Premium. Thank goodness for Power BI Premium Per User for smaller companies. At just double the price per user licence, all these enterprise level goodies can be… Read more
-
Power BI March 2021 Updates – Direct Query for Power BI datasets and Analysis Services, Testing the new fixes.
Direct Query for Power BI datasets and Analysis Services feature has currently been in preview for a couple of months Switching this feature to on, should hopefully allow us to create reports over multiple data sets, which will allow use to have smaller datasets, maybe just over a single model. These can then be connected… Read more
-
Power BI external Tools – ALM Toolkit
How do you do schema changes to your model without refreshing the entire model? This is especially applicable for large models using incremental refresh where refreshing the entire data set takes a long time to process. The ALM toolkit really helps with this process The examples I am going to use aren’t incremental refresh but… Read more
-
Power BI Key Influences Error – A field in explain by is not on the ” table or a table from the same data source and related to it by a many to one relationship. Try Summarising it
The analysis is performed at the record level of the ‘table’ A field in explain by is not on the ” table or a table from the same data source and related to it by a many to one relationship. Try Summarising it This model is all about behaviour and attitudes for an event company… Read more
Follow My Blog
Get new content delivered directly to your inbox.
