Thursday, January 10, 2019

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.

Wednesday, December 05, 2018

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.

Tuesday, October 09, 2018

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 HandaSurya AtmalaSreesutha NampallyRajnish 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 

Wednesday, September 26, 2018

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.

Monday, September 03, 2018

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.

Tuesday, August 21, 2018

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 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.

Tuesday, August 14, 2018

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.

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 t...