Concept of Keys in DBMS

In a database management system (DBMS), keys help to uniquely identify rows of records in a table that may contain duplicated records too. Besides that, keys help to manage relationships between tables. There are many different keys in the DBMS, and each of them has its own meaning. Let’s explore them with some picture illustrations that I get from the Internet to help us understand and differentiate the keys.

Super Key

It is an attribute (key) or a set of attributes (keys) that can be used to identify rows of data in a table is called Super Key. A super key can have additional attributes that are not needed for unique identification.

Candidate Key

A minimal subset of a super key which can be used to uniquely identify rows of data in a table. Candidate Key is a super key with no repeated attributes. According to the Educa website, there are few rules that need to follow when it comes to the selection of a candidate key:

  • A Candidate Key should comprise of distinctive values.
  • A Candidate Key can have various attributes.
  • A Candidate Key cannot comprise of null values.
  • A Candidate Key must uniquely identify each row in the table.

Then, the Primary Key is carefully chosen from the given Candidate keys.

Primary Key

A primary key is a column or group of columns in a table that uniquely identify every row in that table. The Primary Key cannot be a duplicate. This means the same value cannot appear more than once in the table. A table cannot have more than one primary key. It is also known as Entity Integrity Rule.

Rules for defining Primary key:

  • Two rows cannot have the same primary key value.
  • It must for every row to have a primary key value.
  • The primary key field cannot be null.
  • The value in a primary key column can never be modified or updated if any foreign key refers to that primary key.

Alternate Key

The alternate key is a column or group of columns in a table that uniquely identify every row in that table. A table can have multiple choices for a primary key but only one key can be set as the primary key. All the keys which are not primary key are called an Alternate Key.

The illustration below is taken from guru.99.com that clearly differentiate the candidate key, primary key and alternate key from a table that has identified that StudID, Roll_No, and Email are the candidate keys, however, the StudID is chose to be the primary key mainly because it is a unique running number that can be used to identify a student in the table. Then, Roll_No and email are the alternate keys for this table.

Foreign Key

The foreign key is a column that creates a relationship between two tables. It is added into a table to establish this relationship. It acts as a cross-reference between two tables as it references the primary key of another table. The purpose of Foreign keys is to maintain data integrity and allow navigation between two different instances of an entity. It is also known as Referential Integrity.

The differences between primary key and foreign key

  • Primary key helps to uniquely identify a record in the table while foreign key is a field in the table that is the primary key of another table.
  • Primary key cannot be null values and a foreign key may accept multiple null values.
  • Primary key is a clustered index and data in the DBMS table are physically organized in the sequence of the clustered index. A foreign key cannot automatically create an index, clustered or non-clustered. However, you can manually create an index on the foreign key.
  • Can have the single Primary key in a table but can have multiple foreign keys in a table.

Composite Key

A composite key is a combination of two or more columns that uniquely identify rows in a table. The combination of columns guarantees uniqueness, though individually uniqueness is not guaranteed. Hence, they are combined to uniquely identify records in a table.

Compound Key

A compound key has two or more attributes that allow you to uniquely recognize a specific record. Another word, it is a primary key that does not consist of a single column but two or more columns that uniquely identify a row. It is possible that each column may not be unique by itself within the database. However, when combined with the other column or columns the combination of composite keys become unique. The purpose of the compound key is to uniquely identify each record in the table.

The differences between composite key and compound key

The difference between compound and the composite key is that any part of the compound key can be a foreign key, but the composite key may or maybe not a part of the foreign key.

Surrogate Key

An artificial key which aims to uniquely identify each record is called a surrogate key. These kind of key are unique because they are created when you don’t have any natural primary key. They do not provide any relation to the table data in the table. Surrogate key is usually an ordered integer.

Integrity Rules

  • Entity Integrity Rules.
  • Referential Integrity Rules.
  • Business Logic Integrity.

Integrity (validation) pertaining to the business logic, for example, the postal code shall be 6-digit within a certain range.

References

Mouth Restaurant

The days just before the Singapore Government made the announcement to implement the circuit breaker on April 7, 2020, I went out for dinner with a few friends at the Chinese restaurant located at nearby the Maxwell food court.

It was a farewell dinner for an ex-colleague and last gathering before we could not have any more. I know this sounds a bad idea. We practised our distancing and made sure we used the common spoons to get our dishes.

During this COVID-19 period, there were no crowds at all in the restaurant. The waiter took the temperature of each of us and gave us hand sanitizer before we seated. That night, there were 3 tables only, so the services were top best, yet there was still some waiting time for the cook to prepare our food.

My friend ordered the roasted spring chicken and duck. Both of them are nice and delicious. I liked the roasted spring chicken the most. The crispy chicken skin fried to its golden colour, the chicken meat was tender and juicy. It was well-marinated, and it did not need to dip with any sauces.

Unfortunately, I forgot to take a picture of the roasted duck that was served last among all the dishes. The roasted duck was served in two different styles. The crispy duck skin wrapped with popiah skin (spring roll wrapper), while the duck meat cooked separately with some sauce. The duck meat was tender as well.

The barbecue pork was succulent and quickly melt in my mouth. It is delicious, but I felt it was a bit sweet to my liking. To balance the tastes, we ordered vegetables and tofu to eat with the fried rice.

For desserts, I ordered the mango sago pomelo. This dessert supposed to be sweet and nice. However, this round, the dessert was a disappointment because they used a bitter pomelo.

The bill for this dinner was 25% discounted. It is worth to return to this restaurant someday later when the situation gets better to have the roasted spring chicken again.

Address: 38 Maxwell Rd, #01-01/02 Airview Building, Air View Building, Singapore 069116.

Dragon Boat Festival

The Dragon Boat Festival is traditionally celebrated by the Chinese during the summer solstice. According to Wikipedia, the festival occurs on the 5th day of the 5th month of the traditional Chinese calendar. This year, it fell on the 25th of June 2020. I totally forgot about this event after being so frustrated with my teammate regarding some work updates. Luckily, my housemate reminded me and I bought one piece of the glutinous rice stuffed with ingredients such as mushroom, meats, etc.

There are many people in the Singapore communities shared their making of glutinous rice, also known as bak chang in Singapore. I got a summarized picture from the Internet to show the differences of each available type in Singapore. Being a Cantonese, I always love to enjoy the Cantonese glutinous rice than any other types.

I took a picture of the salted bak chang that my housemate bought from Joo Chiat Kim Choo. Seriously, I did not know the brand because all these years I ate those homemade Cantonese bak chang, including the ones that my ex-big boss’ mother made. I think it is my first time I bought bak chang also and thought I have to put into the water to boil it. Actually, it just needs to steam.

This glutinous rice consists of pork, mushroom and chestnut. Two years back, my ex-big boss’ mother made some glutinous rice for all of us in the office. According to one of my ex-colleagues, it was one of the best moments when receiving the glutinous rice from the big boss because it was one of the nicest glutinous rice mainly because it was home-made. Similarly, it has the same ingredients, and it added dried shrimp and dried scallop. The glutinous rice is always generous with ingredients and the taste closest to the home-made glutinous rice that made by my aunt last time. So, which one is your favourite glutinous rice?

Modifying Data in Temporal Table – SQL Server

After an hour of discussion with the Business Analyst and System Data Administrator on the topic of the temporal table, I decided to take a step back to study more on the characteristics of a temporal table in Microsoft SQL Server. I have created three temporal tables in the Microsoft SQL Server 2016 recently for my project. And what is the difference between the temporal table and the temporary table? In one of my posts, I wrote the Introduction to Temporal Table, and I shared that,

Every temporal table has two explicitly defined columns, each with a datetime2 data type. These columns referred to as period columns. SysStartTime and SysEndTime (in the UTC zone) represent the current and historical data for every row of the table.

Period columns cannot be modified

The temporal table has period columns that use to audit SQL data, whereas, the temporary table does not have such columns. The Microsoft website explains that when you run a data modification using the regular DML (Data Manipulation Language) statements on the temporal table, the period columns data cannot be modified. For example, when we perform an insert statement, you can exclude the period columns from the insert statement. If you wanted to include the period columns into the insert statement, then you can set the values as default. For example, from the Microsoft website, the below code shows that the SysStartTime and SysEndTime assigned value with default.

INSERT INTO [dbo].[Department]
  (  [DeptID]
        , [DeptName]
        , [ManagerID]
        , [ParentDeptID]
        , SysStartTime
        , SysEndTime
  )
     VALUES
       (  11
        , 'Sales'
        , 101
        , 1
        , default
        , default) ;

To support further support the above statement, the create table statement that I shared in my introduction post showed that these period columns has declared “GENERATED ALWAYS AS ROW END NOT NULL”. It defines as the period columns are ALWAYS generated automatically by the SQL Server rather than being populated manually.

CREATE TABLE dbo.employee(
  EMPNO   INT,
  ENAME    VARCHAR(10),
  JOB      VARCHAR(9),
  MGR      INT,
  HIREDATE DATE,
  SAL      NUMERIC(7,2),
  COMM     NUMERIC(7,2),
  DEPTNO   INT,
     CONSTRAINT EMP_PK PRIMARY KEY (EMPNO),
   SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
  ,SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
  ,PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)) WITH (SYSTEM_VERSIONING = ON);

An updated row is inserted into the history table

When you perform update statements, data is updated, and it is versioned, with the previous version of each updated row is inserted into the history table. The update statements are the regular update statement, and period columns do not need to be included. If you do, the SQL Server throws an error message as below:

Msg 13537, Level 16, State 1, Line 3 Cannot update GENERATED ALWAYS columns in table 'dbo.tablename'

Important note: However, you cannot update PERIOD columns and you cannot directly update data in the history table while SYSTEM_VERSIONING = ON.

The deleted rows are moved into the history table

When you perform delete statement, data is deleted, but the delete is logical deletion. What it does is the row moved into the history table from the current table – it is not permanently deleted.

Important note: You cannot directly delete rows from history table while SYSTEM_VERSIONING = ON.

There is an interesting part about modifying data in the temporal table. In the Microsoft website, it shares how we can use the MERGE operation to modify the data. For example,

CREATE TABLE DepartmentStaging (DeptId INT, DeptName varchar(50));
GO
INSERT INTO DepartmentStaging VALUES (1, 'Company Management');
INSERT INTO DepartmentStaging VALUES (10, 'Science & Research');
INSERT INTO DepartmentStaging VALUES (15, 'Process Management');

MERGE dbo.Department AS target
USING (SELECT DeptId, DeptName FROM DepartmentStaging) AS source (DeptId, DeptName)
ON (target.DeptId = source.DeptId)
WHEN MATCHED THEN
    UPDATE
   SET DeptName = source.DeptName
WHEN NOT MATCHED THEN
   INSERT (DeptName)
   VALUES (source.DeptName);

The MERGE statement works best when the two tables have a complex mixture of matching characteristics. For example, inserting a row if it does not exist, or updating a row if it matches. This link helps to understand how the MERGE operation works. If you are familiar with MongoDB, this operation is similar to the upsert statement.

I am going to explore further when the dataset that I have on my local machine to examine the behaviour of a temporal table and how it helps to solve the delta detection that mentioned by the Business Analyst and System Data Administrator.

Setup and Run Any OSes in VirtualBox

You can run multiple operating systems (OSes) simultaneously by using the VirtualBox. Oracle VM VirtualBox is a free and open-source hosted hypervisor for x86 virtualization that developed by Oracle Corporation. Alternatively, you can use VMware Workstation that developed by VMWare. Both enable users to set up virtual machines (VMs) on a single physical computer and use them simultaneously along with the actual physical computer. Each virtual machine runs its operating system that can be either Microsoft Windows or Linux. In my previous working experience, I used the VMware Workstation, and today, I want to try out using VirtualBox for a change on my laptop.

VirtualBox Installation

I have installed the VirtualBox in my machine recently, and I planned to write about the installation step for Windows machine. It is easy with the wizard and follows through the steps during the installation. First, download the installer from the website. I am using this link. Once successfully downloaded, run the .exe file. The first view of the wizard looks as below. It depends on the version that you downloaded, the interface may look different. I am using version 6.1.4 for this installation.

Setup using wizard

Click “Next” to proceed.

Next, select the location where you want to install the program. I left it as the default location. This screen shows the required disk space to install the software in your machine. Click “Next” to proceed.

Next, you can choose whether to create shortcuts on your machine. In my case, I chose to untick the checkboxes for create shortcuts on the desktop and Quick Launch. Click “Next” to proceed.

Then, it shows a warning page, you can just click “Next” to proceed and click “Install” to install the software on your machine. Make sure you allow the wizard to continue installing the software on your machine when it prompts you messages.

Launch the VirtualBox

You can begin to use the VirtualBox once you have downloaded some of the images to run it here. If your machine is running in Linux, you can install VirtualBox from this link. It has the command lines that install the software. Choose the correct Linux version to begin the installation.

How to install CentOS 7 using the GUI in Virtual Box

It took me a while to get this installation works on my machine. First, I have to install the VirtualBox on my computer. When launching the VirtualBox, it looks empty as above, or you may have other virtual machines (VMs) set up before. I need to set up a new VM by clicking at the “New” button. Next, follow through the wizard, a guided mode to set up the new VM. If you are familiar with using VirtualBox, you can use the Expert Mode to set up.

There are few things to be done in the above screen.

  • Name the VM
  • Set the folder path.
  • Select Type: Linux
  • Version: Red Had (64-bit)

Note: Since CentOS is the clone of Red Hat and it uses the similar architecture.

In this screen, it allocates the amount of memory to the virtual machine. In my set up, I leave it as default. You can allocate more memory if you have enough memory in your machine.

The above screen, it chooses to create a virtual disk (vdi) and proceed to create.

Choose the storage size on the physical hard disk. There are two options:

Fixed-size of the disk is not recommended in any scenario because you will be downloading many packages to run various applications.

Dynamically allocated will use space on the hard disk as it fills up. Select the dynamically allocated, make sure that your hard drive has enough free space. The 15GB space is sufficient enough to start with.

Click “Next” to proceed.

Click “Create” to proceed and finish the set up. Upon successfully created the virtual machine, the screen appears as below:

Running CentOS using ISO image

You can run the virtual machine now… You need to download the CentOS ISO image and link up the image with the newly created virtual machine. ISO image is a disk image that contains an archived file of everything that would be written in an optical disk.

Where to download the ISO image?

I downloaded the ISO image from this link. The download may take a while to complete due to the file size. The file comes with a .iso file extension.

Link up ISO image with VM

From the screenshot above, click on the “Setting” button and go to “Storage”. Under the optical drive (Empty), select the ISO image (.iso) file that you have downloaded earlier. Also, you need to enable the network adapter so that it can use the internet to download the required packages.

Start the virtual machine

Click on the “Start” button to start the virtual machine. There are different option of running the virtual machine in the VirtualBox. Select the option “Install CentOS Linux 7” and proceed to install. Again, it will take a while to load the required packages to complete the installation.

Once it is ready, you will see the opening screen of the server. It requires basic information to set up the server such as language, timezone and user account. You can set up accordingly.

Using Snapshot

Snapshots allow you to save the working state of a virtual machine and roll back to that state (of the snapshot) later after changes have been made in a virtual machine. For example, if your installed application in a virtual machine is not working correctly, you can revert the virtual machine by using a snapshot that has been created. Snapshot is convenient and helpful. It is recommended to create snapshots if we are performing application testing on different versions. However, a snapshot is not the same as making a backup.

Create Snapshot

  • Select the virtual machine.
  • Click on Machine menu, select Tools and select Snapshots.
  • It opens a section with snapshot options.
  • Click the “Take” icon to create a new snapshot.
  • Name the snapshot accordingly.

Closing a virtual machine

There are 3 options for closing a virtual machine in the VirtualBox:

  • Save the machine state. It saves (“freezes”) its current state of the virtual machine on your physical machine’s disk, for example, you may have some opened applications. When you start your virtual machine again, it returns to the same state where you left off.
  • Send the shutdown signal. This will send an ACPI shutdown signal to the virtual machine, which has the same effect as if you had pressed the power button on a real computer. 
  • Power off machine. It stops running the virtual machine, but without saving its state.

VirtualBox supports importing appliances saved in the Open Virtualization Format (OVF).

The Ubuntu and Windows 10 that I downloaded from the Internet are using OVF file. I am able to demonstrate how to import the OVF file in the VirtualBox. From the File menu, click “Import Virtual Appliance”. A dialogue window shows up, and select the file to import.

Click “Next” to proceed. You see the Appliance Setting. You can leave the settings as it is, and click “Import” to complete the import. The Importing of the virtual disk image takes some time to complete. Once the import is completed, you can start the virtual machine by selecting the Operating System that you can to launch, and click the “Start” button. You may need to set up some setting on the Operation System during the launch, you can follow accordingly to complete the setup.

Example of Import Virtual Appliance of Windows 10

Example of Import Virtual Appliance of Ubuntu 18.04

After running through the two different virtual machine installation methods, I personally prefer to use the OVF file because it is cleaner set up compared to the ISO image. I did not make any changes on the setup. Therefore, import virtual appliance saves some configuration time. Meanwhile, using the ISO image and set up the virtual machine gives us the flexibility to set the RAM and storage size on the physical hard disk.

Note: ACPI shutdown is a signal sent to the OS by an ACPI compliant chipset, e.g. when you push the PCs power button.

Reference: https://resources.infosecinstitute.com/installing-configuring-centos-7-virtualbox/

Jinjja Chicken

On Friday’s evening, my housemates and I ordered a food delivery from Jinjja Chicken, Korean fast food and casual restaurant that has few outlets in Singapore. Since we were working from home, the nearest branch to order the food delivery was the East branch in The Jewel.

The food was delivered to our house before 5p.m. I knew it is too early to call it dinner, however, all of did not eat much for lunch to save our stomach for this party food. I ordered two side dishes and one of them was the mozzarella cheese sticks. I took the first bite before taking the photo. It was nice! In the end, I took two pieces of it.

Then, we started our feast by enjoying the fries, chicken wings, drumsticks, etc. In the party box, there were 16 pieces of the signature JINJJA Wings, 4 pcs JINJJA Drumsticks, one roll of Kimbap, six pieces Seafood Mandu, two regular fries, and four regular sodas. From the website, it says it feeds four or five people. Now I think back, and I guess we are quite hungry at that time. It has been a while I did not eat Korean fried chicken, and this party set is a sinful yet satisfying food to end the working day on a Friday’s evening.

Another side dish that we added on was the teokkbokki. I did not know why there was two bowls of teokkbokki, one with fries and four teokkbokki, and another bowl just all teokkbokki.

Since it is our cheat day, I did not think much and started with eating the fries. The sesame seaweed fries quickly got soggy because it left too long inside the paper bag. Still, I liked it. Next, I tried the chicken kimbap. It tasted good, and it balanced the saltiness of the fries. I wonder how does the beef bulgogi kimbap taste? It has a choice to choose between chicken kimbap or bulgogi kimbap with additional cost.

Then, I took one of the deep-fried seafood mandu. It tasted so different than the frozen mandu that I bought from the supermarket. Why? What is different?

Finally, I started to try the drumsticks, the wings and the drumlets. The chicken skin coated with the thick sauce and black sesame. I did not know which sauce that my friend ordered. There are four different favours to choose from the menu. I wrapped up the feast with the iced coke.

Overall, it is a satisfying Korean fried chicken. With other food inside this party set, I got to try much different food and satisfy my outside food craving although, it is high in sugar, oils, and carb. If you ask me whether I will do it again, I think Yes, for Korean fried chicken.

This dinner was sponsored by my housemates’ company for their celebration of hitting the sales target. They invited me to join them for the Friday’s evening celebration. Thanks for inviting and sharing the food.

Temporal Table – SQL Server

I am working on a project that requires the ETL to generate daily delta changes. The delta change is based on the last extracted date-time that stored in the log table and filtered the records that meet the criteria from the main table. Based on the performance testing done by my teammate, it was not optimized for millions of records that we need to handle daily. My teammate told me that my manager suggested using the temporal table.

SQL Server 2016 introduced support for temporal table, also known as system versioned temporal tables. The introduction of the temporal table as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time.

The evolution of the data tracking mechanism started with Change Tracking (CT), Change Data Capture (CDC) and now Temporal Tables. I have no experiences using the temporal table. I did some research to find out more information.

Introduction

The temporal table allows SQL Server to maintain and manage the history of the data in the table automatically, and allow historical time analysis. It is a critical feature used to audit SQL Server data. Every temporal table has two explicitly defined columns, each with a datetime2 data type. These columns referred to as period columns. SysStartTime and SysEndTime (in the UTC time zone) represent the current and historical data for every row of the table. The DateTime range has a value that is appropriate during that timeframe.

How does it work?

By querying the table, we can easily see the transitioning of the data to its various states within the specific date-time intervals. In addition to these period columns, a temporal table also contains a reference to another table with a mirrored schema. The system uses this table to automatically store the previous version of the row each time a row in the temporal table gets updated or deleted. This additional table is referred to as the history table, while the main table that stores current (actual) row versions is referred to as the current table or simply as the temporal table. During temporal table creation, users can specify the existing history table (must be schema compliant) or let the system create a default history table.

Why temporal tables

Below are the use cases of the temporal tables besides auditing SQL Server data changes and performing data forensics when necessary:

  • Rebuilding state of the data as of any time in the past.
  • Calculating trends over time (historical trend analysis).
  • Maintaining a slowly changing dimension for decision support applications.
  • Recovering from accidental data changes and application errors.

Examples

From the Microsoft website, a simple example to illustrate a scenario with Employer information in a database.

CREATE TABLE dbo.Employee
(
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED
  , [Name] nvarchar(100) NOT NULL
  , [Position] varchar(100) NOT NULL
  , [Department] varchar(100) NOT NULL
  , [Address] nvarchar(1024) NOT NULL
  , [AnnualSalary] decimal (10,2) NOT NULL
  , [ValidFrom] datetime2 GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

I would like to share another example from sqlshack.com, that creates an employee temporal table.

CREATE TABLE dbo.employee(
  EMPNO   INT,
  ENAME    VARCHAR(10),
  JOB      VARCHAR(9),
  MGR      INT,
  HIREDATE DATE,
  SAL      NUMERIC(7,2),
  COMM     NUMERIC(7,2),
  DEPTNO   INT,
     CONSTRAINT EMP_PK PRIMARY KEY (EMPNO),
   SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
  ,SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
  ,PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)) WITH (SYSTEM_VERSIONING = ON);

SQL Server creates the history table with following format, dbo.MSSQL_TemporalHistoryFor_<Object_ID>.

There is no delete option on temporal table

To get a delete option, we need to turn off the system_versioning setting using the ALTER TABLE statement. For example,

ALTER TABLE [dbo].employee SET ( SYSTEM_VERSIONING = OFF )
GO

Once executed the SQL statement, the temporal table and history table become a regular table. Then, can use the DROP statement to delete the table.

Summary

In this article, I cover the basic introduction of the temporal table, how do the temporal table work and some examples to create a temporal table. The data retrieval becomes simple by using the SYSTEM_TIME clause along with the associated sub-clause. The history table can grow big, and the number of transactions on the temporal table takes up space. Periodically purging the data from the history table can help to release the space.

I will continuously update this entry whenever I make any successful use cases in my current project. I will use the available dataset to perform the walkthrough and explain the concept.

References:
https://www.sqlshack.com/temporal-tables-in-sql-server/
https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15

Beautiful Sunday with Chocolate Muffin and Mint Flower Tea

The alarm rang at 8.00 am this morning, and I prepared myself to go to the supermarket to buy ingredients that I wanted to cook with the frozen scallops. I bought the frozen scallops from my friend after my friend’s parents ceased their catering services during the Covid-19 pandemic in Singapore.

Sunday’s morning was free and easy, and I took a walk from my apartment to the nearby mall. Most of the places in Singapore require every individual to check-in and check-out using the Safe Entry application. It is a handy application to capture location, date and time whenever we are outside of our homes.

Although I did not like to share my personal information, such as NRIC, I think this is a piece of valuable information for contact tracing. However, I cannot track back myself, a little drawback of the application.

Now, it is an hour before my standby call for the Covid-19 ad-hoc operation. I am going to my colleague to check the bug in the application. Meanwhile, I am writing this entry and enjoying a pot of mint flower tea. I bought the mint tea leaves sometimes ago with my friend.

Chocolate muffin

My friend baked chocolate muffins and shared one of them to me. I refrigerated the muffin for a day before eating it. It looks great when I opened the container. I paired the chocolate muffin with a cup of hot mint flower tea. Both the muffin and tea blend well.

Explore Power BI Desktop

I updated my current Power BI Desktop version via the Windows Apps Store recently, and now it is a good time to share the new user interface (UI) of the Power BI after the installation. In the year 2019, during my Specialized Diploma study, the Power BI Desktop skinning was in dark mode. I am not sure when the Microsoft team has changed the Power BI Desktop’s skinning to white mode, as well as having the Filter Pane on the right side.

Another new feature that I spotted is the Power BI has the theme options for the dashboard and reports. This theme is not referring to the Power BI Desktop’s skin. You have to enable this feature from the Power BI Settings, and it allows you to change the theme to suit your dashboard and reports presentation.

To do so, navigate to the File menu, select Options and Settings, then Options. Next, in the Preview feature section, select Customize current theme.

Click OK button to proceed. It may prompt you to restart the machine so that it takes effect and enables the theme feature. There is a list of built-in theme available in the Power BI Desktop, and you can refer to this link for more detail. Furthermore, you can optionally export a theme’s JSON file. You can make amendments by manually modifying the settings in that file. You can rename that fine-tuned JSON file and later import it. It gives more control to the users to customize the theme according to their dashboard and reports.

Getting familiar with the interface

From the Microsoft website, it shares the detail of each pane labelled below. I extracted the picture and its explanation.

  1. Ribbon – Displays common tasks that are associated with reports and visualizations.
  2. Report view, or canvas – It is a place where visualizations are created and arranged. You can switch between ReportData, and Model views by selecting the icons in the left column.
  3. Pages tab – This area is where you would select or add a report page.
  4. Visualizations pane – It is the pane where you can change visualizations, customize colours or axes, apply filters, drag fields, and more.
  5. Fields pane – It is the pane where query elements and filters can be dragged onto the Report view or dragged to the Filters area of the Visualizations pane.

You can collapse the Visualizations and Fields panes to provide more space in the Report view by selecting the small arrow.

The screenshot above shows an example of the collapsible pane for Filter pane. It works for Visualizations and Fields panes too.

Connect to data sources

Power BI Desktop connects to many types of data sources, you can choose from local databases, excel sheets or data on the cloud. There are about 70 different types of data sources available. Go to the Get Data from the ribbon on the Home tab to begin accessing the data. Then, select a source to establish a connection. For some data source connection, you may require to input the user credential to authenticate and accessing the data. Here is the list of data connectors available in the Power BI’s Get Data function.

It brings you to the Navigator window that displays the entities (tables) of your data source. It gives you a preview of the selected data. In the same window, you can choose to Load or Transform Data. If you are not making any changes, formatting and data transformation, then you can click on the Load button, else Transform Data allows you to perform data cleaning and conversion before importing the data into the Power BI Desktop. You are allowed to edit the data after importing too.

Transform data to include in a report

Power BI Desktop includes the Power Query Editor tool that helps you shape and transform data so that it is ready for your visualizations. To launch the Power Query Editor tool, there are two ways to bring up this window:

  1. use Transform Data button on the Home ribbon. [For April/2020 version]
  2. use Edit Queries button on the Home ribbon. [For older versions]

If you click on the Enter Data button on the Home ribbon (as shown above), a Create Table window prompts up. From this window, click the Edit button, it brings up the Power Query Editor tool. Remember, earlier I mentioned about the Load and Transform Data buttons when we load data from the Get Data button? The Transform Data button brings up the Power Query Editor too, similar function as to how the Create Table window’s Edit button works. I am not going to cover any data transformation in this blog. It is a big topic to discuss, so I think it is good to share it with some good examples and dataset in the next article.

Connect from multiple sources

Most of the time, we deal with more than one data source when we build a report. You can use the Power Query Editor tool to combine data from multiple sources into a single report. How does it able to combine into a single table? In Power BI Desktop, it has a feature called Append Queries to add the data from a new table to an existing query.

Create a visual

If I remember correctly, in Tableau, when fields are selected, the Tableau suggests the suitable visualizations to the users to use in the dashboard or reports. I am not sure whether Power BI has a similar feature. In the Report View, drag a field onto the Report View canvas, the Power BI Desktop automatically creates a table visual as default visual. This visual as a report listing because it lists the selected fields in a tabular form. You can choose to have different visuals, such as a bar chart or line graph if you wish to do so.

To create a visual, select a field from the Fields pane, you can drag the field into the data field (Values) in the Visualization pane, or you can click on the checkbox. A table visual displays on the screen, and you can choose another type of visual from the Visualization pane. There is no precedence to create a visual, and you can select a visual before selecting the fields. Each visual has a different visualization pane, for example, if you choose a dual chart, the following screenshot shows shared axis, column and line values. When you choose a pie chart, it displays legend and values.

Publish a report

After all the hard work on the dashboard or reports, you want to publish it and share it with other people. You can do so in Power BI Desktop by clicking on the Publish button in the Home menu. You will be prompted to sign in to Power BI, follow the steps and you will see the published reports after that.

At this point of writing, I do not have any published report to show. Therefore, I cannot put up the steps here and show how to pin a visual to the dashboard. This feature allows you to choose whether to pin the visual to an existing dashboard or to create a new dashboard.

Conclusion

This article is a high-level walkthrough of the Power BI Desktop, that explains how to use it to create visuals and publish the dashboard and reports. I do not cover the explanation of the visualization and publication in this article, I will include them in the next article in the future.

I hope this article gives a good impression of the Power BI Desktop’s features and allows you to have some sensing of this tool. Furthermore, the Power BI Desktop’s buttons are self-explanatory, so you should not have issues or troubles to use and navigate around. Besides that, people who have been using Microsoft Excel and Tableau for data analysis may find the Power BI Desktop has some similar functions because the Power BI Desktop is another data visualization tool too.

Reference: microsoft.com

What is the difference between dashboard and report in Power BI?

I have written about Power BI Desktop and the high-level walkthrough in my previous articles. Both articles I mentioned two keywords, dashboard and reports quite often. In this article, I would like to share about the difference between the dashboard and report in the Power BI Desktop. In general, a dashboard consists of a visual that displays the most crucial information needed to achieve objectives, consolidated and arranged in a single screen. We can use a dashboard to monitor at a glance. A report, on the other hand, is any information that can be at any visuals, such as a table, chart, and etc.

Power BI dashboard is one placeholder

Dashboard displays the most important decision making facts to run a business. Power BI dashboard is a high level view of some key reports, such as Key Performance Index (KPI). It uses to provide day to day information consolidated from the detailed reports. Below is one of the example of a Power BI dashboard.

A Power BI dashboard is a single page, often called a canvas, that tells a story through visualizations. The visualizations you see on the dashboard are called tiles. The visualizations on a dashboard originate from reports and each report is based on a dataset. Readers can view related reports for the details.

Dashboards are a feature of the Power BI service only. They’re not available in Power BI Desktop.

Features of a Power BI Dashboard

  • Automatic refresh.
  • Sharing to users.
  • Power Q&A.
  • Real-time monitoring.
  • Send alerts.

Next, let us move on to the Power BI Report.

Power BI Report

Power BI reports are more detailed data presented in many formats like charts, graphs, tabular, etc. You can have more than 1 page for reports, as shown in the picture above. The data in the report can be used to perform analysis. For example, by using a standard filter function called slicer, the users slice the data according to the filtering criteria. It allows the user to drill down through selecting particular data, and then the report interactively changes. Hovering and highlighting data in Power BI reports give the same interactive changes experience to the user.

Features of Power BI Reports

  • Slicers.
  • Multi-pages.
  • Interactivity.
  • Drill down to explore data.
  • Publish to the Website.

Comparison between the dashboard and report

The Educba website provides a good visual comparison between the Power BI Dashboard and Report. You can refer to the link here.

Key Differences Between Power BI Dashboard vs Report

Below are the important differences:

  • Different data sources are used by power BI dashboard in each dashboard consists of maps, graphs, etc. Only one dataset can be used as a source for Power BI reports.
  • Power BI dashboard capabilities of pinning existing visualization are only from the current dashboard to other dashboards, called as dashboard navigation. The Power BI reports can pin or navigate to any reports or dashboards.
  • Email subscription for power BI dashboard is not available. Reports can be published on the web and subscribed through email.
  • Power BI dashboards have alerting feature where attention needed condition arise dashboards raise an alert, but in reports, we cannot have this feature.
  • Power BI dashboards cannot slice and dice, but reports have many ways to filter and slice.
  • Natural language queries available only available from the dashboard, but for reports, it’s not possible.
  • Power BI visualization cannot be changed, but reports visualization can be changed and saved, but it all depends on user role and permissions involved to access dashboards and reports.
  • Dashboards have the same features of reports, but dashboards can be exported to limited formats, so dashboards are used to visualize the important data rather than analyze data. Data can be exported for the reports only, any formats like excels tabular formats.
  • Power BI dashboards can be set as featured or as favourites but cannot create a featured power BI reports.
  • Power BI reports can be drilled down to different levels of data sets on which reports are built.

Conclusion

At a glance, a dashboard and a report serve different purposes, and they have its own unique features. For good data visualization, you may want to include both dashboard and reports because they complement each other.

References:
https://docs.microsoft.com/en-us/power-bi/service-dashboards
https://www.educba.com/power-bi-dashboard-vs-report/