All posts by admin

Analyze your Spotify Streaming History!

When you go into the privacy panel of your account settings, you can turn off sharing your Facebook data with Spotify and personalized advertisements. You can also download your streaming history from the last 90 days. It took about a week for Spotify to collect the data and notify me in an e-mail. The data you receive is a couple of JSON files.

I have only used the StreamingHistory.json file. It contains the DateTime of the song played and the length you listed to it in milliseconds. I filtered out the songs that played for less than 2 minutes to only keep the songs I liked. I created a calculated column that takes you to the given song’s spotify page where you can play it.

My list contains the Marvelettes song that Fury recommended too!

Check out the embedded report in a full page window here.

From Dido’s new album Still On My Mind
Download the Power BI file from my GitHub page and create yours!

SQL Saturday in Prague

My  new Twitter profile.

A couple of months ago I had been reading twitter while eating my lunch at my workplace in Budapest when I saw a tweet informing about Alberto Ferrari going to Prague to hold a training day about data modeling in Power BI, SSAS Tabular and Excel.

I want to thank the organizers of the SQLSaturdayPrague event held at 2018.12.07-08 (with the pre-cons included), they were friendly and attentive. The event was thorougly organized and managed.  The sponsors and the fellow attendees deserve a big Thank You (Děkuji vám!) for the event’s friendly, fun atmosphere.

I was a bit nervous before the pre-con. I was afraid of being late so I visited the venue the evening before the event and set-up 3 different alarms to wake me up in time.  The event started at about 9 AM and finished about at 5 PM in the beautiful building of the czech savings bank.
It was great to meet Alberto Ferrari in person after reading the books and blogs authored by him and Marco Russo.   As Mr. Ferrari said, building a good data model comes first because when you have a well-designed model, you don’t have to write so difficult DAX measures.  We talked about different data modeling scenarios and the dangers of using the bi-directional relationship feature of Power BI. I really recommend attending a course taught by Mr. Ferrari, even if you have to travel a bit to get to the venue. Try to finish reading the Definitive Guide to DAX by 2019 february becasue that’s when the 2nd edition is going to be published!


We had three different presentations at the same time so everyone had to choose 7 presentations from the available 21.

Claudio Silva 
Administrating SSRS/PBIRS was a boring task! Sorry… did you said WAS?

The main message was that implementing even a single change is quite slow using the UI of SSIRS/PBIRS.  Imagine how much time you can save by doing those using powershell!

Alberto Ferrari
Inside the VertiPaq engine

I thought I knew this topic very well because of my relevant MCP exams (70-768) but there were some new elements such as hash encoding and materialization. I am going to research these. If you have resources about a deep dive into the VertiPaq engine, please let me know on LinkedIn.

Jirí Neoral
Monitoring your BI solution with Power BI.

This presentation got a strong applause from the audience because the the solution is easy to implement and very useful. You can use dynamic management views and system views to analyze the logs to check the usage and performance of your reports.

Cédric Charlier
Automating the testing of your BI solutions with NBi. Everyone knows how important testing is.  With the open-source NBi
you can create and store tests for different tools such as SSIS and SSAS.

Marcos Freccia
Dbatools – The Swiss army knife for the DBA.  Marcos also got a great applause because his presentation was very useful for anyone managing databases. Powershell is awesome, dbatool is a Powershell library if I am not mistaken.

Martin Bém
Data Modeling for Beginners

Well, modifying a date model already implemented in a database or DWH is one thing that I have experience in but I never suspected that data modeling is this complicated. Life is complicated, the business processes of banks and other large companies are particulary complicated and databases are trying to store events in a way to be more easily queried so it is no surprise that a lot of data models are more complicated than a starchema.

Grzegorz Stolecki 
Predictive Reporting Services Performance Monitoring

The idea is very valueable. Don’t wait for the users to complain. Use R or Python and data science such as clustering to quickly find the problematic reports and their troublesome properties.

After the presentations and the raffle we went out to have dinner with the fellow attendees and organizers.

Combine Power BI and Python!

I had a presentation about using Python in the Microsoft Power BI reporting tool last week at the Meetup.

Meetup event page:

I think most of my readers are advanced Power BI users but I hope I can interest some Python developers to try  Power BI out.  The main interest is that since the 2018 August release Power BI can use Python script to connect to data sources and also to create visualizations.

You can download my  presentation, my Power BI – Python demo report and the tutorial from my GitHub page.

I want to thank @Datacamp for the training I received that made me able to create this report.  I recommend listening to the DataFramed podcast   to every BI developer, business analyst and data scientist aspirant. Engage!


Road to the MCSE

After my last MCP exam I got my MCSA in BI Reporting and my MCSE in Data Management and Analytics.  This was my 5th.

There are different advantages of these exams.  My first exam was the 70-768 about OLAP Cubes. I took that in July of 2017,  before the end of a long project that involved creating and managing lots of MOLAP cubes. I read 3 books for the exam not only to make sure that I pass but to make sure I also master the advanced level topics.  After I passed the exam I started to study other reporting tools knowing that I can move on from studying OLAP all the time.

The next one was the 70-779 Power BI exam in January 2018. It was harder to prepare for this one a bit because the exam ref. book was not published yet. I would say that about 6 months of working with Power BI is enough to pass the exam.

To understand different possible reporting achitectures on-premises or in the cloud, you should have a basic knowledge of Azure solutions so preparing for the 70-473 exam provided that for me. That exam also does not have an exam ref book but it had a practice test.

After these exams I thought that I need to improve my T-SQL skills so the next one became the 70-761. This is an example where unlike the Power BI exam I did not just prove that I have the skills by working with the technology. It was the other way this time, later I could use a lot of functions, solutions in my work from the practice test or the exam ref book.

My last exam so far is the 70-778 about Excel. I took it for two reasons, one was to get the certifications and the other was that I thought that every BI developer should take this exam becasue Excel is the most popular analytical tool. You can say that you don’t know Tableau or QlikView but you can’t say that you are not good at Excel.

I have kept on studying since the last exam because every book I read every video I watch takes me further on the road of becoming a data scientist.   What is a data scientist? My opinion is that it is more than a BI developer but still has to specialize in something. How many data scientist does it take to change a lightbulb? Two! The one is specialized in using a ladder, the other is specialized in turning things counter-clockwise.

Exam 70-779 Analyzing and Visualizing Data with Microsoft Excel

I bought the Exam Ref book written by Chris Sorensen. Link   

I watched the edX course videos and read the book twice before taking the exam.  I did not do the exercises in the book. The book is good but it is for preparing for the exam not for teaching beginners.

I think if you passed the 70-778 Power BI exam, you can easily pass this one.  Most of us BI developers started our careers before Power BI gained widespread popularity so we know Power Pivot/ the Excel Data model well.  You need to know the following topics to successfully pass the exam:

  1. Basic DAX
  2. Advanced Power Query
  3. Excel visualization types
  4. Understanding basic M code
  5. Everything else from the skills measured list

This exam is recommended for BI developers and others who have never taken an MCP exam before and would like to start with an easy one.

Exam passed : 70-761 Querying Data with Transact-SQL

I have passed the 70-761 Microsoft exam on 2018-06-21.

Using T-SQL is the first programming language that you should master if you want to be a data scientist or PowerBI ninja – whichever is cooler.  There is an ocean of beginner level study sources, try to avoid the worthless kind. Try Paul Scotchford’s classes on Udemy first.

I prepared for the exam by reading the Exam ref book and practicing with the practice test from the Microsoft Press Store.   

The book is great but also very dry so I recommend reading it multiple times. Read it first quickly, not getting bogged down by very complicated topics.  I had soon realized that I can learn much better by wathing videos.  I had enrolled in one of the Edx courses about SQL. After enrolling I immediately downloaded all the videos and subtitles from the site for offline viewing.  I had found good explanation videos on other platforms such as YouTube. After watching videos about every topic of the exam,
reading and understanding the exam ref book for a second time was much easier.

The exam was very difficult. There were some questions that were tricky or just not clear for me. For some questions you are required to type in the code which is a lot more complicated than choosing the right one from multiple solutions. I had used all the 120 minutes to answer the questions.  I recommend practicing test taking by either using practice tests or learning languages with the Duolingo app. When you have 8 different possible answers you have to scan them fast for obvious and not so obvious errors. You are given a laminated pad that you can use to take notes. You can write down the letters of the possible answers and cross them one by one.

The book is not for beginners but otherwise very good, I plan to read it once in every three months because it helps you use T-SQL more effectively.

One search word, many websites

Hi there!

I have a Power BI report that I want to share with you.

The idea came after I watched two or more movies such as Lady Bird and The Lobster that I did not understand completely.  Usually I just search for other people’s interpretations of the movie on Reddit, Twitter, Rotten Tomatoes.

My report takes a parameter value as the value of the Title column and after a refresh you can look for the particular text in multiple websites.  You just click on the URL icon under the column headers and it takes you to that website’s search results.

There is a good chance that I am not the first developer coming up with this simple idea but I have never seen any blog posts using parameters this way.

How to do it?

The key idea is to replace the value of the original text in the Title column with a parameter’s value.  Yes, first create a parameter named Title.Then you create a custom column replacing the search keyword with the Title column. This way you can create new calculated columns for yourself, try addig an column!

In the Edit Queries menu, click on Edit parameters to change the Parameter from the one I used in the report. Click on Refresh to create the new URL adresses!
You can download the PBIX file and try it yourself!


Designing and Implementing Cloud Data Platform Solutions Exam Passed 70-473

Hi there!

I have passed the above mentioned exam on Monday, 12/February/2018.

First, let me direct you to the  site for a general introduction of Azure Services.

This exam is about Azure database solutions.
I have watched a lot of training videos preparing for this exam but I think reading is a much better way to get to know the Azure services.  Continue reading Designing and Implementing Cloud Data Platform Solutions Exam Passed 70-473