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 available to use. But first of all. When creating stored procedures to transform data on SQL DB, The stored procedures are usually split into Dims and Facts. Its time to split this notebook up into the corresponding dims and facts. Dim Contestants A copy of the original file is saved as DimContest ..read more

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 we can test the differences later. There are more csv files to look at and one specifically gives us more details for the Contestants dim people.csv The task here is to go back to the original Notebook and see if we can add this new information The first bit of code to create go ..read more

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 I went for my favorite stats based show. UK Taskmaster. Kaggle Kaggle delivered the goods with csv files of taskmaster information https://www.kaggle.com/datasets/sujaykapadnis/comedians-challenged-ridiculous-taskmaster-ds The challenge is to deliver a star schema from this data. Transforming it and creating Dims ..read more

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 of the initial Pipeline creation exercises for Azure Synapse in more detail and try and answer questions that arise. https://microsoftlearning.github.io/dp-203-azure-data-engineer/Instructions/Labs/10-Synpase-pipeline.html The end game is to take a file from the Serverless SQL Pool (Dat ..read more

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 is a header over the columns – this needs removing and then the headers promoting Get Data > Folder creates helper folders that clutter up the back end and aren’t the best solution. This is the code that gets created let     Source = Folder.Files("C:\Users\Name\Company\Project\Data"),   ..read more

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 these in the fact table you could sum them to create a measure. But you are left with thousands of rows of just 1s and 0s. against all the keys in the fact table. Also, they aren’t really metrics. They are true and false flags and as a consequence should not be in a fact table. The above solutions is not wh ..read more

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 two figures just for the table Time intelligence to see this time last month There are various ways of doing this Prev Month At Risk = CALCULATE([At Risk],PARALLELPERIOD('Dim'[Date], -1,MONTH)) Here we use CALCULATE to evaluate the SUM of At Risk (created in a base measure already) in ..read more

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 The app is published but the users only see visuals with no data. When they try to refresh they see this error This seems to be a issue with the composite model. It turns out that for users of composite model reports you need to have the following turned on. This means that the people in ..read more

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 scan, catalog and report on all the metadata of your organisations Power BI artifacts. Scanner APIs are the Admin REST APIs that can extract tenant level meta data that support the Power PI good Governance Pillar of Discoverability and Monitoring So the scanner APIs are a set of Admin REST AP ..read more

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 be required for this exercise And thanks to the Power BI Forums for giving me some help on figuring this out. What we want to end up with is: A year Slicer Product category on rows of a matrix The Day Name from Date on the Columns of the Matrix And % Quantity for that day based on all the days of the week as the ..read more