Free #Databases and Data Visualisations course for kids aged between 9 to 14 years week 2 update

It was good to see all the kids come back to hear all about databases for the 2nd week.  There were a few new enrolments.
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 all the others 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.  Most of them had hands on experience creating a database named schools and a table named school.

I also wanted to cover how to insert data in this week but ran out of time so will do that next week.

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.  . #sqlfamily #microsoftnz #mvpbuzz

Databases and Data Visualisations Course for kids Season 2 Week 1

Databases and Data Visualisations Course for kids Season 2 Week 1
I have started the second leg of Databases and Data Visualisations course for kids aged between 10 to 14 years yesterday.

This time I have asked the kids to fill out a survey to understand their current level of knowledge regarding what data is and what visualisation is. I also asked what they want to get out of this course.

The answers ranged from "understanding the basics of data" to "Learn about the basics of data visualization and what I can do about it in the future "

The best answer I liked was "I want to learn more about databases online. I also want to learn a little bit of coding since that is definitely not my best factor. I want to learnt things that I didn't know before to extend my thinking and become more all-rounder."

From the survey, I was confident that the kids already knew what data means and they could easily give some examples.

To explain about Relational Databases and Normalisation, I have asked each one of them to type in some information about their student ids names, classes, their emails, teachers and teacher's emails and schools.

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

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

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

My Microsoft MVP Award

At the beginning of this month, I received an email from #microsoft telling me that I have received the Most Valuable Professional (MVP Award). #mvpbuzz

Following that in the 2nd week, I received my MVP Award Gift package!

I believe that this award is for my contribution to the community in Microsoft technologies.

I never thought that my passion for sharing my knowledge would end up receiving this award one day. I would like to thank the Almighty for all the support in my life. I would like to thank my husband, my parents and 2 lovely daughters without whose support, this would not have happened.

I would like to thank @reza and @leila  for believing in me and nominating me.

Finally I would like to thank all the visitors to my blog posts, attendees of my various presentations, webinars and conferences.

My presentation materials for the power bi virtual days webinar

@powerbidays thanks for the opportunity to present at the virtual conference yesterday. My topic was 'Sentiment Analysis Using Cognitive Services in Power BI'.
Below is the link to presentation material and json code on github.
https://github.com/indupriya9/PowerBIDays-April-2019


Here is a link to the webinar on the same topic I did on PASS Marathon that you can use to follow my demo

https://www.youtube.com/watch?v=Trtrgy59vSw&feature=youtu.be 

Global AI Night

I’ll be lucky enough to be part of the team hosting the 1st Global AI Night at the Auckland Microsoft Offices Auckland with the amazing team of Auckland Artificial Intelligence User Group.  The Global AI Night is a free evening event organized by local communities all over the world that are passionate about Artificial Intelligence on the Microsoft Azure.

You can register at https://www.meetup.com/meetup-group-LyenJaPw/events/259373751/

#GlobalAINight  #GlobalAIBootcamp

My first webinar session in PASS Marathon

My session from the hashtag#passmarathon this morning is here.  The topic is Sentiment Analysis using Cognitive Services in Power BI.


You can download the slides from this link beforehand
https://lnkd.in/gXSAmav

I will post the link to the webinar as soon as it is available,

My Presentation for SQL Saturday Wellington 2019

Had a really great time presenting at hashtag#sqlsatwellington last Saturday on the topic Top 10 tips every hashtag#PowerBI Developer should know.
Had really great questions from the audience. as well.

You can download the presentation from here

Thanks to Anupama Natarajan Phil Seamark and Dave Dustin for this opportunity.

Tutorial -- Steps to upgrade Power BI Report Server

This is a tutorial detailing step by step process to upgrade Power BI Report Server to Power BI Report Server January 2019

Before you begin the actual Upgrade you need to take the following backups

1. Backup your encryption keys
2. Backup your databases.
3. Backup config files needed
4. Backup powerbi files

1 -   So how do you back up the encryption keys:

• Go to Reporting Services Configuration Manager from the Microsoft Power BI Report Server option as shown below

• Click on Yes when you are prompted by the below screen.

• Connect to the PBIRS instance when prompted as shown below

• Click on the Encryption Keys and Backup button as shown below

• Choose the file to backup the keys along with the passwords as shown below

2-   Backup your Power BI databases

The next step is to back up the databases -- PBISReportServer and PBISReportServerTempDB:

Follow the below steps to do this

• Login to SSMS and right click on the PBISReportServer database and choose -- Tasks -- Backup 
• Add the file name to indicate the location of backup as shown below and click ok

3-   Backup the configuration settings

• Navigate to the folder (C:\Program Files\Microsoft Power BI Report Server\PBIRS\RSHostingService)  and copy the files named config.json , RSHostingService.exe.config to a backup folder
• Navigate to the folder C:\Program Files\Microsoft Power BI Report Server\PBIRS\ReportServer and copy the files Rsreportserver.config, Rssvrpolicy.config and Web.config to a backup folder
• Navigate to the folder C:\Program Files\Microsoft Power BI Report Server\PBIRS\ReportServer\bin and copy the files Reportingservicesservice.exe.config to a backup folder
• Navigate to the folder C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config and copy the files Web.config and Machine.config files to a backup folder.  These files are for ASP.NET application

4-   Backup Power BI files from the servers

• There are many ways to do this.  One of the easiest way is to go the repository and download each file.
• To do this, navigate to the repository usually if you are doing this on the Power BI Server the location will be something like http://localhost/pbireports 
• Click on the ellipse of each of the reports.  A small menu opens as shown below.  Click on the download to download the file 

Now the backup process is complete
Now you can begin the upgrade process

UPGRADE Power BI Report Server

• Download the latest version of the Power BI Report Server available from this link This link consists of 3 files as shown below.  Choose the appropriate files for download.

• Double click on the PowerBIRS installer and choose Upgrade Power BI Report Server as shown below

• The installer prompts with User Access dialog as shown below.  Click Yes

• The installer comes up with the security and privacy declaration.  Tick the I Agree checkbox and click Upgrade as shown below.

• The installer begins to upgrade existing files and installs the necessary files as shown below

• After the upgrade process is complete, the following screen shows up.

• Click on the Configure Report Server from the above screen
• The next step is to restore the encryption keys that you have backedup.
• To do this click on the Encryption Keys and Restore button as shown below

• Choose the saved backup file from the screen below and click OK

• After the keys are restored you get the below result

• Next click on the PBIDesktop.exe installer and install the Power BI Desktop for Report Server.  This is report authoring tool for building power bi files that you can publish on to the report server. 
• Next step is to restart the server to complete the process.

• So now you can navigate to the PowerBI repository location and access your Power BI files.

• If you have any issues accessing the data from the existing files on the power bi report server interface, try to open the file in the new Power BI Desktop for RS and republish the report to the server. 

Create a New Partition on existing multi-dimensional SSAS cube.

Below is a process that I follow every year to create a partition in existing multi-dimensional SSAS cubes that I maintain.

• Log in to the SQL Server Analysis Services server using SQL server Management Studio as shown below.

• Expand the Databases by clicking on the + sign.  Choose the database you want to create the partition for.  
• Expand the Cubes  and expand the Cube that you want to partition. Expand the MeasureGroup and expand the Measure Groups – You can see the Partitions folder there.  Expand the Partitions folder to see the existing partitions as shown below.

• Right Click on the Partitions folder and click on New Partition as shown below:

• Partitions wizard opens as shown below.  Click on Next

• Choose the MeasureGroup and table as shown below.  You must choose at least one table to proceed further. 

• Click Next

• You need to restrict rows in this partition by providing a query.  Tick the specify a query to restrict rows in the below screen.  

• Make sure your query selects rows that are included only in this partition.  I usually use the where condition that selects data in a year.  
• For example select * from [dbo].[cubeExample]  WHERE LEFT([DateKey],4) = '2019'  This query gives data only for 2019.

Click on Check after providing the query to validate the syntax as shown below.

• If the Syntax check was successful, you will get a success message as shown below.

• If the Syntax Check was not successful, it gives an error.  An example error is shown below.

• Fix the error and recheck the Syntax.  Click Next.  you may be asked for  a UserName and Password as shown below.

• Choose Your Processing Location and Storage Location as shown below and click Next

• Choose the Name of the partition on the Next screen and also the Aggregation options as shown below and then click Finish

Tips that I learnt:

1. Ensure that you take a backup of the Cube database before creating the partition.
2. Ensure that you have the permissions to create the Partitions.  Also ensure that the username you use is a generic username and not your own username 
3. Ensure that you have a look at the query for the previous partitions.  Best approach is to copy the query for the partition for the previous year and use that as a starting point.

Power BI Did you know - Series 2 (Add Refresh Date )

Did you know that you can add a refresh date to indicate when your data has been last refreshed ?

Below are the steps to do this.

• Open the report in Power BI Desktop

• Go to Edit Queries from the Home ribbon as shown below

• From the Power Query editor, choose the table where you would like to add the refresh date and click on Advance Editor as shown below

• Add the below code before the in statement 

 #"Added Refresh Date" = Table.AddColumn(#"Renamed Columns", "Refresh Date", each DateTimeZone.FixedUtcNow(), type datetimezone)

• Replace "Renamed Columns" from the above code to the name that is mentioned just above the line as shown below

For example if the below code is in the Advanced Editor, the "Removed Columns" in the step need to replace the "Renamed Columns" as shown below

let
    Source = Sql.Databases("localhost"),
    table1 = Source{[Name="table1"]}[Data],
    dbo_table = table1 {[Schema="dbo",Item="table"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(dbo_table,{"col2", "col4", "col6"}),
    #"Added Refresh Date" = Table.AddColumn(#"Removed Columns", "Refresh Date", each DateTimeZone.FixedUtcNow(), type datetimezone)
in
    #"Added Refresh Date"

• This will create a new column named Refresh Date in the table.
• Close and Apply changes.

• Now create a new measure, in order to add the last refresh date to the report 
• An example code for the new measure could be as below

Footer = CONCATENATE("Last Refreshed " , MAX(Schedule[Refresh Date]))

Use a card visual to display the measure as shown below

There you go, you have the last refreshed date in your report

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.