Power BI Did You Know -- Series 1 (Contact Us button)

Did you know that you can add a contact us button in your Power BI reports ?

Here is how you add a contact us button in your power bi report.

Step 1:  Go to your Power BI report
Step 2:  Add a text box

Step 3:  Type in * as shown below: (1)

Step 4:  Change the font of the * to Windings as shown below:(2)  This will convert the * to an envelope

Step 5: Now add a mailto link to the envelope button as shown (3)

Step 6:  You can test this in Power BI Desktop by using CTRL+Click. This will open a new email with the default account. When you publish to the Power BI service, you can click on the envelope to open a new email with the default account as shown below:

Got this tip from Charles Sterling Microsoft Power BI Program Manager.

You can use the  decoder link to translate between normal font and Windings font and add hyperlinks to the same by using the DECRYPT button on the decoder link.  For example you can use '(' to add a phone button.

Similarly you can use the ENCRYPT button on the decoder link to find out what symbol each of the Alphabets and numbers represent in Windings font.

Power BI Competition for Kids aged between 11 and 15

Yesterday I have concluded the 12 weeks databases and data visualisation classes by running a #powerbi data visualisation competition for kids.  

Extremely happy that Reza Rad and leila Etaati have agreed to be the judges for this first ever #powerbinz competition.  

11 kids participated and I have given them the links to makeovermonday website to choose their own data and come up with their visualisations.

I sincerely thank all the kids and parents Bindu Handa, Surya Atmala, Sreesutha Nampally, Rajnish Suri, PMP and  all others who are not there on LinkedIn for all their extended support without which the event would not have been successful.  Now I feel that the kids have learnt something useful over the last 12 weeks. :)  

Below are the photos of the top 3 winners of the competition. If anyone is interested in watching the videos of the kids presentations you can access the below link  https://drive.google.com/open?id=185DKP5DxBl0Lfb9rxPcxmdKAPbhMHJHD 

Free Databases and Data Visualisations course for kids Week 8 to 11

It has been quite a while since I have posted about the free databases and data visualisation classes for 11 to 15 year old kids.  So here I am with an update.  For the last 3 weeks, I have taught basics of importing data from the Web using the URL and using a csv or a excel file.  Also gave an overview of the 3 views in Power BI Desktop, query editor, Applied steps pane, removing columns etc.

I have also covered about new columns and measures  what the main difference is between those 2. Also covered very basic DAX functions, explained about the date dimension, Bookmarks and drillthoughs.

Next week I have asked everyone to pick a dataset on their own and come up with their data visualisations.  So waiting for their creativity to unveil.

Also wanted to thank @Reza and @Leila from #Radacad for sponsoring biscuits for kids.  They have been enjoying them for the last 3 classes.

Some more happy moments of SQL Saturday Auckland 2018

I got a few of the kids whom I teach Databases and Data Visualisations to attend this event.  In fact I was trying to avoid them coming but they forced themselves to come to this event as they were very enthusiatic in attending this event.  They have attended 4 sessions where I thought they could understand what the speakers were saying.

Here are a few snapshots of the kids with the speakers.

SQL Saturday Auckland 2018 Presentation

On 1st September 2018, I had a wonderful opportunity presenting at the SQL Saturday Auckland event at UNITEC. The title of my session was Google Forms Meets Power BI via SSRS 

You can download my presentation here

It was a Full house session and lot of questions by the participants.  Thoroughly enjoyed sharing my knowledge.

I also enjoyed helping for this event organised by @Rad_Reza and @Leila_Etati.
Many thanks for this wonderful opportunity.

Free Databases and Data Visualisations course for kids Week 7

Yesterday I gave the results for the database fundamentals test that was conducted last Monday. If anyone wants to try the test you can click here
 #microsoftnz, #sqlserver,  #sqlfamily

I started off the class with an introduction to Data Visualisation.  Why we need data visualisations and type of data visualisations.  Also covered different tools for data visualisations. Finally I introduced what Power BI Desktop is. hashtag#powerbi, hashtag#powerbiusergroup.

Had an interactive session with a lot of questions from the kids like 'What is a Heat Map ?' 'What is the difference between pictorial and graphical presentation' etc.

Shared a movies csv file and introduced the Get Data button in Power BI Desktop and got all of them to download the movies database.  Explained how to create the main visuals -- the column chart and stacked bar charts.  What kind of columns can be used in the Values part of the visuals.
Gave them homework to create their own visuals and present them in the next class.
I have also asked them to download the country latitude and longitude dataset that is available here
https://lnkd.in/gN72ccD by using the Get Data -- web button with a view to teach them about Maps in the next class.

I am looking forward for their creations next week.

Free Databases and Data Visualisations course for kids Week 6

Week 6 update:

Yesterday the class was a bit different because it started off with a Database Fundamentals test.  There were 20 questions to answer that covered all the database concepts that were covered in the last 5 weeks.  If you would like to answer the questions yourself, the questionsare here. #microsoftnz,#sqlserver, #sqlfamily

15 kids and 2 adults took part in the test. I have marked the answers and am very pleased with the results which are as below:
 2 of the kids acheived 100 %
 4 kids achieved between 90% and 100%
 3 kids between 70% and 80%
 3 kids between 55% and 65%
 3 kids achieved 45 %.  These are the kids who joined after 3 weeks and they did not have sql server to practice at home as they had MAC and Chrome books as their devices.

After the test we discussed the answers for the questions.  Then I covered the topic on Table joins.  Below is a photo of the kids smiling after the test.

From next week I will be teaching them Data Visualisation using Power BI #powerbi, #powerbiusergroup.  I will be designing the course with the help of @reza rad book power-bi-from-rookie-to-rockstar  and @marco russo and @alberto ferrari's book- Introducing Microsoft Power BI .

Any help in designing the course with kids in mind is highly appreciated.

Free Databases and Data Visualisations course for kids Week 5

Week 5 update:

It was another full house yesterday with 17 kids and 4 parents attending the course. #sqlfamily #microsoftnz 

The first 20 minutes were spent in solving issues with questions like --
- 'I created a table but I cannot see it'
- 'I am getting a syntax error'

After this, when I did the recap of what happened last week, I was pleased to see most of the kids eagerly answering my questions regarding update and delete statements. They were keen to discuss some of the SQL functions I covered  There was a bit of difficulty in understanding the substring function. But finally everyone understood this function.

One of them wrote a substring function by passing her name as the first argument instead of the field name and tried to return a substring of her name which was quite impressive.

Free Databases and Data Visualisations course for kids Week 4

Databases and Data Visualisations course for kids aged between 11 to 15 years week 4 update. It was another full house on Monday with 16 kids and 4 parents attending the course. #sqlfamily #microsoftnz  This time a uni student Sakshi came to help.

The first 45 minutes were spent in solving issues with questions like --
- 'I created a table but I cannot see it'
- 'Why is my insert statement not working ?'
- 'I have inserted a lot of duplicate records.  How do I delete them ?'
- 'I am getting a syntax error'

After this, when I did the recap of what happened last week, I was pleased to see most of the kids eagerly answering my questions. I felt that now all the kids are able to genuinely understand what I am trying to teach them.

The agenda for this week was to explain the update and delete statements.  The Like and In operators in the where clause.  Some of the basic SQL functions like Min, Max, Count, AVG, SUM.  String functions like Left, Right and Substring.

It is interesting to see how some of the kids think.  One of them asked what if we accidentally deleted all records ?  Then I covered the Begin Transaction, rollback and commit features which I didn't even plan to cover as I thought that will be deeper and difficult for them to understand.

As usual I felt very happy to see the enthusiasm in these kids. I am looking forward for the session on next Monday.

Free Databases and Data Visualisations course for kids Week 3

Databases and Data Visualisations course for kids aged between 11 to 15 years week 3 update. It was a full house yesterday with 15 kids and 5 parents attending the course. #sqlfamily #microsoftnz

During my recap of what happened last week, two of the kids wanted to answer all of my questions.  For the homework that I have given, I could see kids create databases named 'mytrips', 'moviesworld', 'personalitytraits', 'eventmanagement', 'Hobbies' etc.

There were only 2 challenges faced by the kids when they were doing their home work.  First was when they had a space in the database or table name. Second was the table was not created in the new database they created.

The agenda for week 3 was to show them how to insert data into the tables and view the data.  I could just cover select statement and basic where clause and the time was up.

I feel very happy to see the enthusiasm in these kids. I am looking forward for the session on next Monday.

Databases and Data Visualisations course for kids Week 2

Databases and Data Visualisations course for kids aged between 11 to 15 years week 2 update. It was good to see so many responses for my last weeks post. #sqlfamily #microsoftnz It was good to see all the kids come back to hear all about databases for the 2nd week.   So first I started off with a Recap of last week, to help the 3 new kids understand the database concepts and also to test the knowledge that they have gained. It was good to see that they remembered most of the concepts except for the redundancy part. The agenda for week 2 was to show them how they could access SSMS and create a database and a table. Everyone of them had hands on experience creating a database named school and a table named student. I also wanted to cover how to insert data in this week but ran out of time so will do that next week. In the end as promised last week, I covered Excel tips as below. - Select All with One click - Open multiple excel files at a time - Move within an excel sheet (Ctrl +Home, Ctrl+End, Ctrl + arrow keys, Ctrl+Shift+Arrow Keys, - Move between different sheets (Ctrl + Tab) I have also given them some homework to ensure that they practice what was learnt in the past 2 sessions. I am looking forward for the session on next Monday.

Databases and Data Visualisations Course for kids Week 1

As mentioned last week, I have started the Databases and Data Visualisations course for kids aged between 11 to 15 years yesterday.

It was a very good and fun experience teaching these kids about database concepts.  I asked each one of them what a database means to them.  Everyone had a good basic understanding of what a database meant before I showed them what it meant.  The one response that I liked from one of them was "A database is a base where you store data"

To explain about Relational Databases and Normalisation, I have asked each one of them to type in some information about their studentids names, classes, teachers and schools.

This activity proved to be useful to make them understand the concept of redundany and the disadvantages that arise out of it.  Then it was easy for me to explain about Normalisation.

I have given them home work of installing SQL Server Express on their laptops.

Since I have used Excel for their activity there arose a need for them to know more about Excel.  So I will be doing a 15 - 20 min session on Excel every week basically giving them some Excel tips.

I am looking forward for the session of Creating databases and tables using SQL Server next Monday.

Teaching kids about databases

Every year I try to teach kids some aspects of Technology. Last year I taught Game Development using Construct 2 for a group of 12 primary and intermediate school kids. They developed 9 games and participated at a competition that involved High School kids. Two of the games got 2nd and 3rd prizes as well.

In this process I really enjoyed teaching the basics and the kids also enjoyed making the games that they liked and learned a lot.

So I was thinking of what to teach them and came up with a lot of ideas and finally settled with -- why not teach them with what I hae been doing for the last 15 years ? 

And so I came up with the course title 'Database Programming and Data Visualisations' For this I will be using the latest SQL Server Express and Power BI as the database and data visualisation tools. I have planned this to do in a span of 8 - 10 weeks.

Below is the list of topics I am going to cover for this free course.

• Introduction to databases
• Create an SQL database along with some tables 
• Insert Update and Delete data 
• Importing data
• Querying the data using DDL statements
• Introduction to Data Visualisation
• Introduction to Power BI 
• DAX basics 
• Create Visualisations 
• Usage of bookmarks and drillthroughs
• Create and share dashboards based on reports in Power BI desktop
• Using custom visuals
• Optimise the model for Natural Language Query

I would like to make it as fun and interesting as Hamish Watsonpossible. So I look to you all for ideas to help me deliver this successfully so that they can get the fundamentals fo database basics, normalisation, relational databases and visualisation concepts right.

#sqlfamily, #powerbi, #microsoftnz

So please bring along all your creative ideas that can help me deliver this program successfully.

Migrating Sql Server Analysis Services (SSAS) Cubes from SSAS 2014 to SSAS 2017

I have finally migrated our multidimensional cubes from SSAS 2014 to SSAS 2017

Below are the steps that I have followed to achieve this.

• On a new Virtual Machine (VM!) I have installed SQL SERVER ANALYSIS SERVICES 2017.  This is our main cube storage Virtual Machine
• On the Reporting Services Virtual Machine (VM2) I have installed another instance of SQL SERVER ANALYSIS SERVICES 2017 with the aim of synchronizing the cubes between VM1 and VM2.  VM2 is being used for reporting jobs so that there is no load on the main VM! While processing of cubes. 
• In order to migrate the cubes from SSAS 2014 to SSAS 2017, I have logged into SSAS 2014 machine and scripted out all the databases using the Script Database as – Create To Option as shown below.

• Then I logged into VM1 and created the databases by running the scripts that I generated in the above step.
• The next step was to ensure that the data sources and credentials are correct.
• To do this expand the Datasources of each of the databases and right click on the datasources and click on Properties as shown below.

• Click on the Connection string and make sure you have the correct login details as shown below

The next step is to process the cube.
If the processing of the cube is successful then the agent jobs that process the cubes will also be successful.

Security issues that you need to take care of are as follows:

• Right click on the SSAS server instance and click on properties as shown below.

• Add the user that will be running the agent job of processing the cube.

• Ensure that the SSAS Service runs under that user and not NTService\MSSqlserverOLAPService

• After ensuring the above security in place most of the jobs were successful except for the synchronizing job that synchronises the databases from VM1 and VM2.  When the synchronizing jobd runs this gave an error as below

Executed as user: NT Service\SQLSERVERAGENT. Microsoft.AnalysisServices.Xmla.XmlaException: Errors in the OLE DB provider. The server returned the following error: (Either the  user does not have permission to synchronize objects on the source or target instance,

To resolve the error above ensure the following steps are taken care of

The user must have permissions for the below services as shown below

• Log on as a service 
• Replace a process-level token 
• Bypass traverse checking 
• Adjust memory quotas for a process 

If you have any issues in migrating Analysis Services Databases, feel free to contact me.

My Presentation for SQL Saturday South Island 2018

Today I thoroughly enjoyed presenting on the topic "Google Forms Meets Power BI via SSRS"

The aim of this session was to provide an end to end solution for creating and sending feedback forms and analysing the same using Power BI.

The slides can be downloaded from this link below.

http://www.sqlsaturday.com/712/Sessions/Details.aspx?sid=80613

Extracting Power BI Usage Statistics from Power BI Report Server

Today I had a thought of monitoring the usage of Power BI Reports.

So I started exploring the PowerBIReportServer database and see whether there are any statistics that I could utilise.  The database had views of executionlogs that are similar to the SSRS ReportServer database.  After studying all the available views, I came up with this stored procedure to monitor the usage of the Power BI Reports.

USE PBIReportServer
Go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[PBI_Usage_Stats] 

AS

BEGIN

SELECT [InstanceName]
      ,[ReportPath]
      ,[UserName]
      ,[TimeStart]
      ,[TimeEnd]
      ,[TimeDataRetrieval]
      ,[TimeProcessing]
      ,[TimeRendering]
      ,[Source]
      ,[Status]
      ,[ByteCount]
   FROM [PBIReportServer].[dbo].[ExecutionLog2]
  where Status = 'rsSuccess' and ReportPath != 'Unknown' and Bytecount > 0
  and username not like 'NT SERVICE%' 
End

Highlights from tonight’s meetup with Phil Seamark

Tonight we had a great presentation with Phil Seamark on DAX as detailed in the meetup group.

I wanted to summarise some of the highlights here –

Phil showed off the below features of DAX.  I tried to link to blog posts from RADACAD

•  Generate data using the randbetween function
•  Generate a date table using calendar function -- More information can be found on the blog post here
• Generate a sales table with fictitious data again using the RANDBetween , generate, addcolumns,  filter, rand etc functions.
• Create a summary table using the summarisecolumns function. He also explained the advantages of the summary table.
• He also discussed when to use a calculated column and calculated measure.  Demonstrated the differences between the two very clearly.  More information can be found on a blog post from Reza here
• Next he introduced the DAX Studio.  This is a great tool that can be used in conjunction with Excel and Power BI. There are many advantages of using the DAX Studio.  Some of them are Viewing your open Power BI data model, Formatting your DAX code, use the DMVs to analyse the performance.  You can also look at the Query Plans and server timings.
• Next he showed how you can use SQL Server Management Studio to open the Power BI desktop model by connecting to the Analysis services Database that the Power BI Desktop dynamically creates. You can browse through the Tabular model of the database using the Sql Server Management Studio.

Some great questions were also asked after the presentation like

• What is the difference between Summarise and summarisecolumn function.
• Can we export the Power BI Desktop model and create SSAS model using Sql Sever Management Studio.
• What do you think of the Common Data Services ?

And a few more…

I definitely learned a lot in this session. 

Migrating to Power BI Report Server – March 2018 from Power BI Report Server – October 2018

Last night, I found out that the Power BI Report Server – March 2018 update is available for download.  So I was very excited to install this and use it for a brand new Power BI report that I was doing.

Again, this has two steps in the migration process as outlined in my previous article of Migrating SSRS 2014 to SSRs 2017.

•       The first step is to Install the new release of Power BI Report Server on a different machine. 
•      The second step is to migrate the existing Power BI reports into the new installation.

So here you go – with a run down of the steps that I have followed to migrate.  I did not do an in place upgrade.

Step 1

Installing Power BI Report Server – March 2018

Downloaded the new Power BI Report server from the below link

https://www.microsoft.com/en-us/download/details.aspx?id=56722

When you click on the Download button – you will be prompted to choose the files as shown below.  Choose the relevant files and click next

When the download is completed, run the installer file named PowerBIReportServer.exe

Here are some screenshots from my installation.

Choose Developer from the below screen.

Click on the Configure Report Server on the below screen to configure Power Bi Report Server.

When the Reporting Services Configuration launches, make sure you choose the correct instance.

My installation Is showing two instances in the dropdown because I also have a SSRS installation on this server.

I choose the PBIRS as I am configuring the Power BI Report Server.

Service Account Tab  -  Just use the default

Web Service URL Tab  --  

Make sure you are changing the default ReportServer virtual directory to PBIReportServer and clcik Apply

Database Tab  -- On the Database -- click on change Database and create New Database

 Keep the Authentication Type -- Current User -- Integrated Security

 In the below screen ensure that the ReportServer is changed to PBIReportServer.  Then the tempdatabase will automatically change to PBIReportServerTemp database.

 Under Credentials -- Leave the Authentication Type to Service Credentials a shown below.

 Then Click Apply

Web Portal URL Tab -- Under this ensure that the virtual directory is changed to PBIReports from Reports and then click Apply

I did not change any other tabs.  Click Exit from Report Server Configuration Manager

Step 2

Migrate the existing Power BI reports into the new installation of Power BI Report Server March 2018

The next step is to migrate the existing Power BI reports into the new installation.  To do this, follow the below steps.

• 1Back up the PBIReportServer and PBIReportServerTemp databases from the old Power BI Report Server October 2017 Services installation. 

• 2.Restore these two databases on to the new Power BI Report Server installation.

Now you will find the reports from the old SSRS server in the new SSRS server if you browse through the url http://localhost/pbireports  on the new Power BI Installation box as shown below.

The next important step is to use the new PowerBIDesktopforRS executable to author the Power BI Reports to publish to this new Power BI Report Server Installation.