Error displaying Google Map using Yellowfin BI


After our upgrade of our Yellowfin BI instance to the latest build, we found that the Google Maps on our reports stopped working. We received the following error: Sorry! Something went wrong. The page didn't load Google Maps correctly. See the JavaScript console for technical details.



Fun Fact: The inventor of the Waffle Iron did not like waffles.

Find below print screen of my error message:



























After working through the JavaScript Console, we found that Google no longer support keyless access. If you want to read up more about the new updates to the Google Maps Standard Plan, please go to https://maps-apis.googleblog.com/2016/06/building-for-scale-updates-to-google.html.

 In summary:

  • Google no longer support keyless access
  • Google have implemented a simple 25000 map loads per day free limit
  • Google have reduced the daily map load maximum limit you can purchase for Google Maps JavaScript API, Static Maps API, and Street View Image API from 1000000 to 100000 requests per API.
  • Google now count Google Maps JavaScript API client-side requests towards the daily limit.
To get a free API Key from Google, you must register your app project on the Google API Console by using a Gmail account. Steps involved below:

When you complete the above steps, you will get to the following screen where Google generated a API key for your project:




















Now that we have a API Key, we need to configure Yellowfin to use your API Key. Login to your Yellowfin instance with administrator rights, open the Administration menu item and click on Content Settings, then click on Chart Settings which is normally the 2nd tab, expand Map Settings, search for Google Maps API Key, and paste your new key into the textbox. When done, click on Save. There is no need to restart Yellowfin.

Administration > Content Settings > Chart Settings > Map Settings > Google Maps API Key

























Another Fun Fact: If you leave everything to the last minute, it will only take a minute.

Tuesday, October 03, 2017
, , , ,

Yellowfin Version 7.2 Build 20160426 upgrade to Version 7.3 Build 20170907


We're using Yellowfin as our Business Intelligence (BI) tool. We had to upgrade to the latest version of Yellowfin. We were on Version 7.2 Build 20160426 and the latest version was Version 7.3 Build 20170907. When we ran the upgrade process, we received An error occured during installation: java.lang.NullPointerException. Yellowfin created a log file in the root directory. We've worked through the log file but couldn't identify the reason why we're receiving this error. After reading up, this was a bug in the latest build.

Fun Fact: An apple in the morning will keep you more awake than a cup of coffee.

Below is a print screen of the error we received:











To solve this, we have a work around. You need to installed a previous Build 20170728 for Version 7.3 before installing Build 20170907. Find below steps to install.

First you need to download Yellowfin 7.3 Build 20170728. Before you install this build, backup your Yellowfin website and database. After your backup, if your Yellowfin instance starts up using a Yellowfin Windows Service, you need to stop the Service. Run you upgrade using build 20170728 as follows:

Run yellowfin-20170728-update.exe:






























Click on Next:






























Select I accept the terms of this licence agreement and then on Next:




























Click on Browse and select your path where you installed Yellowfin, then click on Next:



























After you clicked on Next, the installation will start to upgrade your Yellowfin instance. When the upgrade is done, you will get the following screen:






























This means your installation was successfull. Now you follow the same steps but executing yellowfin-20170907-update.exe. You will see that you upgraded to the latest build successfully. Your last step will be to start the Yellowfin Windows Service and you can start working/developing again.

Another Fun Fact: Bruce Lee moved with such speed in fights they had to slow down the movie so people could see his moves.

Friday, September 22, 2017
, , , , ,

The ORDER BY Clause in SQL

What is the ORDER BY clause in SQL? An ORDER BY clause in SQL specifies that a SQL SELECT statement returns a result set with the rows being sorted by the values of one or more columns. The sort criteria do not have to be included in the result set. You can specify one or more sort columns. The first sort column you specify determines the column used for the primary sort.



 Fun Fact: Mel Blanc (the voice of Bugs Bunny) was allergic to carrots.

Let's start with the basic syntax of the ORDER BY clause. Find below example:

SELECT columns
FROM tables
WHERE clause
ORDER BY expression [ ASC | DESC ]

Without the ORDER BY clause, you have no guarantee that the rows returned from your SQL database will be returned in the insertion order or even the PRIMARY KEY order. By using the ORDER BY clause, you control the order your records will be returned in. To sort your results in ascending order, you can leave the attribute blank after your expression or use expression ASC. The default order is ASC. Below is a SELECT without the ORDER BY clause from our cellphone providers SQL table:

SELECT ID, Name
FROM dbo.CellphoneProviders

When we execute the above query, we have the following result set:

ID Name
5 Telkom
6 Cell C
7 Vodacom
8 MTN
9 Virgin Mobile

As you can see, the record set is returned in a random order. It might seem like the output is sorted by the ID column, but that is not guaranteed. What could be more confusing is that if you run the query repeatedly, it seems like the result keeps being returned in the same order; but again, that’s not guaranteed. When the database engine processes this query, it knows that it can return the data in any order because there is no explicit instruction to return the data in a specific order. To return the cellphone providers by their name in ascending order, you can add the ORDER BY Name clause using any of 2 below examples:

SELECT ID, Name
FROM dbo.CellphoneProviders
ORDER BY Name

OR

SELECT ID, Name
FROM dbo.CellphoneProviders
ORDER BY Name ASC

Both the queries will return the same result set in the same order. Find below example of the output from the above queries:

ID Name
6 Cell C
8 MTN
5 Telkom
9 Virgin Mobile
7 Vodacom

Now by looking at the results set, the output is ORDER BY the cellphone provider name in ascending order. To sort descending, you need to explicitly specify DESC. Find below an example query:

SELECT ID, Name
FROM dbo.CellphoneProviders
ORDER BY Name DESC

Now the output will be returned in a descending direction. Find below output from you SQL Table:

ID Name
7 Vodacom
9 Virgin Mobile
5 Telkom
8 MTN
6 Cell C

In SQL, you can sort by ordinal positions of columns in the SELECT list, but it is considered a bad practice. Let's say you need to order the rows by ID which is the first expression in the SELECT list and then order it by cellphone provider name which is the second expression, you could use the following SELECT example using ordinal positions:

SELECT ID, Name
FROM dbo.CellphoneProviders
ORDER BY 1,2

Using the ORDER BY clause, you can specify the sorting direction for each column by using the ASC and DESC attributes. Note that you can order the result rows by elements that you’re not returning. So remember, a query without an ORDER BY clause returns a relational result, and hence doesn’t guarantee any order. The only way to guarantee order is with an ORDER BY clause. 

Another Fun Fact: An apple, potato and onion all taste the same if you eat them with your nose plugged.


Thursday, September 14, 2017
, ,

The WHERE Clause in SQL Server - LIKE and DATE

I've create a Post where we discussed the beginner development of the WHERE SQL clause, the link to the post is The WHERE Cause in SQL Server Start. As per my previous post, what is a SQL WHERE clause? A WHERE clause in SQL specifies that a SQL statement should only affect rows that meet specified criteria. The criteria are expressed in the form of predicates. WHERE clauses are not mandatory clauses of SQL statements, but can be used to limit the number of rows affected by a SQL statement or returned by a query.

Fun Fact: The total number of steps in the Eiffel Tower are 1665.

Now, let's start with the first WHERE clause. If you have a query where you need to return results from your Vehicles table where your vehicle makes start with the letter V.  You can achieve this by using a LIKE WHERE clause. So what is a LIKE OPERATOR? The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards used in conjunction with the LIKE operator:

  • %
  • _

Below is an example where we return all vehicle makes starting with the letter V.

SELECT Make, Series, LicenseNumber, LicenseExpiryDate, Odometer, ServiceInterval
FROM dbo.Vehicles
WHERE Make LIKE 'V%'

When we execute the above query, we will see example data as follows:

Make Series LicenseNumber LicenseExpiryDate Odometer ServiceInterval
Volkswagen Pasat EE55EEGP 2017/12/31 40000 10000
Volvo XC90 II99IIGP 2018/04/30 80000 15000

As you can see, the query only returned the records where the vehicle makes starting with V. The reason for the results is because we only used the wildcard (%) after the character. If you want results where you need all vehicles that contains the character V, doesn't matter where in the vehicle make name, you can use the wildcard on both sides of the character. Example below:

SELECT Make, Series, LicenseNumber, LicenseExpiryDate, Odometer, ServiceInterval
FROM dbo.Vehicles
WHERE Make LIKE '%V%'

If we run the above query, we will get a different results set.

Make Series LicenseNumber LicenseExpiryDate Odometer ServiceInterval
Volkswagen Pasat EE55EEGP 2017/12/31 40000 10000
Volvo XC90 II99IIGP 2018/04/30 80000 15000
Chevrolet Volt JJ10JJGP 2018/05/31 90000 150000

As you can see, we have 1 more record in our record set. That is because Chevrolet has the character V in the middle of the string. If you want results where the vehicle make ends with a specific character, you can place the wildcard before your search character.

Our next discussion on the WHERE clause is handling Date and Time fields. Lets start with a basic example. Lets say you want to return all the vehicles from your SQL database where the vehicle license expires at the end of the month. Below is an example SELECT:

SELECT Make, Series, LicenseNumber, LicenseExpiryDate, Odometer, ServiceInterval
FROM dbo.Vehicles
WHERE LicenseExpiryDate = '2017-09-30'

If we run the above query, we will get the following results set.

Make Series LicenseNumber LicenseExpiryDate Odometer ServiceInterval
Nissan Leaf BB22BBGP 2017/09/30 10000 10000

As you can see, we have 1 vehicle where the license will expire at the end of the month. The above query will return only the records where the license expire on a specific day. Lets say you want to return all vehicles that will expire in the September month for year 2017, below is an example query:

SELECT Make, Series, LicenseNumber, LicenseExpiryDate, Odometer, ServiceInterval
FROM dbo.Vehicles
WHERE YEAR(LicenseExpiryDate) = 2017
AND MONTH(LicenseExpiryDate) = 9

The above query is not using best practices. The reason is because we apply manipulation to the filtered column, the predicate is not considered a search argument and SQL won't be able to rely on index order. Best practice for the above query is as follows and it will return the same record set:

SELECT Make, Series, LicenseNumber, LicenseExpiryDate, Odometer, ServiceInterval
FROM dbo.Vehicles
WHERE LicenseExpiryDate >= '20170901'
AND LicenseExpiryDate < '20171001'

By using the above method, the predicate is considered a search argument and there's the potential for SQL to rely on index ordering. You can use the BETWEEN for the above query but because my column datatype is DATE, it is best to use GREATER THAN OR EQUALS TO and LESS THAN because using BETWEEN, the column holds both date and time elements. That means, BETWEEN is used when your column datatype is DATETIME. Below is an example:

SELECT Make, Series, LicenseNumber, LicenseExpiryDate, Odometer, ServiceInterval
FROM dbo.Vehicles
WHERE LicenseExpiryDate BETWEEN '20170901' AND '20170930 23:59:59'

If you don't add the TIME part on the WHERE clause, your query will exclude vehicles that expires on 30 Sep 2017.

Another Fun Fact: Recycling one glass jar saves enough energy to watch TV for 3 hours.

Friday, September 08, 2017
, , , , ,

The WHERE Clause in SQL Server - Start

After my Post where we discussed the SELECT and FROM clauses, I thought, what is the next category using Microsoft SQL Server queries? The answer is the WHERE clause. If you want to read up about the SELECT and FROM clause first, go to my Post by clicking on the following link The Select and From Clause in SQL Server. So, what is a WHERE clause? A WHERE clause in SQL specifies that a SQL statement should only affect rows that meet specified criteria. The criteria are expressed in the form of predicates. WHERE clauses are not mandatory clauses of SQL statements, but can be used to limit the number of rows affected by a SQL statement or returned by a query. In brief SQL WHERE clause is used to extract only those results from a SQL statement, such as: SELECT, INSERT, UPDATE, or DELETE statement.

Fun Fact: Movie trailers were originally shown after the movie, which is why they were called "Trailers".

Lets work through a list of WHERE clause types and examples of each. First, lets to a simple EQUALS example where we select all employees where their gender is male:

SELECT ID, Name, Surname, EmailAddress, Gender
FROM dbo.Employees
WHERE Gender = N'Male'

If you run the above query you will see results as follows. As you can see, there are no records return from the database where the gender is equals to Female or NULL.

ID Name Surname EmailAddress Gender
7 Jon Yarber jonyarber@amazon.com Male
9 Carri Buehler carribuehler@cocacola.com Male
12 Kris Addie krisaddie@google.com Male
14 Lenny Hemmer lennyhemmer@mobil.com Male
16 Milo Batchelor milobatchelor@yahoo.com Male

Now lets do the opposite, the below query will return all the employees where the gender is NOT EQUAL to male:

SELECT Name, Surname, EmailAddress, Gender
FROM dbo.Employees
WHERE Gender <> N'Male'

As you can see from the below results, the SQL query returned only the employees where the gender is not male. If there are records where the value is NULL for the gender column, by running the above query, it will return all the records where the gender is equal to female and NULL because NULL is not equals to Male:

Name Surname EmailAddress Gender
Jana Meadow janameadow@burgerking.com Female
Magaret Bedolla magaretbedolla@firestone.com Female
Lore Flax loreflax@hooter.com Female
Danielle Weible danielleweible@samsung.com Female
Kiana Battle kianabattle@youtube.com Female

If you want to return all the records that is not equals to Male and that is NOT NULL, you can update the query as follows:

SELECT Name, Surname, EmailAddress, Gender
FROM dbo.Employees
WHERE Gender <> N'Male'
AND Gender IS NOT NULL

By applying the IS NOT NULL where clause, the only records that will be returned by the SQL query is where the gender is equals to Female.

Ok, lets say you're Gender column allows nulls and you want to show 'Not Specified' for gender when you return records from the database and the Gender Column IS NULL. You can use ISNULL. What is ISNULL? Microsoft's ISNULL function is used to specify how we want to treat NULL values. Find below example:

SELECT ID, Name, Surname, EmailAddress, ISNULL(Gender,'Not Specified') AS Gender
FROM dbo.Employees
WHERE Gender <> N'Male'

You will see on the below results that employee Phil has a NULL value for gender and because we used the ISNULL, the NULL is replace with 'Not Specified':

Name Surname EmailAddress Gender
Jon Yarber jonyarber@amazon.com Male
Carri Buehler carribuehler@cocacola.com Male
Phil Crites philcrites@facebook.com Not Specified
Kris Addie krisaddie@google.com Male
Lenny Hemmer lennyhemmer@mobil.com Male

The above ISNULL function can also be replaced with the following the 3 functions that will have the same result set:

  1. COALESCE
  2. NVL
  3. IFNULL

Lets say you need to compile a query where you need to return results from the employee SQL table where the name is Danielle or Lenny. We can use the OR operator as part of your WHERE clause. What is an OR operator? An operator is a reserved word or a character used primarily in an SQL statement's WHERE clause to perform operation(s), such as comparisons and arithmetic operations. These Operators are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement. Find below query:

SELECT Name, Surname, EmailAddress, Gender
FROM dbo.Employees
WHERE Name = N'Danielle'
OR Name = N'Lenny'

Because we used the OR operator, if the name equals Danielle or the name equals Lenny, the SQL query will return the records as per the below example:

Name Surname EmailAddress Gender
Lenny Hemmer lennyhemmer@mobil.com Male
Danielle Weible danielleweible@samsung.com Female

Lets say you need to compile a query where you need to return results from the employee SQL table where the name is Jon and the surname is Yarber. We can use the AND operator. The AND operator displays a record if all the conditions separated by AND is TRUE.

SELECT Name, Surname, EmailAddress, Gender
FROM dbo.Employees
WHERE Name = N'Jon'
AND Surname = N'Yarber'

The results for the above query is as follows:

Name Surname EmailAddress Gender
Jon Yarber jonyarber@amazon.com Male

I will create another post where we will go into more advanced WHERE clauses, coming soon.

Fun Fact: A baby spider is called a spiderling.
Thursday, August 31, 2017
, ,

The SELECT and FROM Clause in SQL Server

I was talking to a colleague at work about all the different types of SQL SELECT and FROM clauses you get and thought it will be a good post for my blog. So, what is SQL Server? SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. A Select query must have a  SELECT and FROM Clause. Lets start with the FROM clause.

Fun Fact: Bananas are curved because they grow towards the sun.

FROM Clause

The From Clause is the first clause to be evaluated logically in a SELECT query. Below are an example of the SELECT and FROM clause in SQL

SELECT Column1, Column2, Column3
FROM SchemaName.TableName

The From clause has a two-part name to refer to a SQL Table. The first part is the schema name, it is considered best practice to always indicate the schema name, if no schema name is used in the From Clause, SQL will take the default schema name that is normally dbo. By indicating the schema name, you prevent yourself from referencing the incorrect SQL Table. The second part of the From Clause is the SQL Table Name. What is a SQL Table? A table is a set of data elements (values) using a model of vertical columns (identifiable by name) and horizontal rows, the cell being the unit where a row and column intersect. A table has a specified number of columns, but can have any number of rows.

You can alias the table if you want to reference the table using another name. Find below example:

SELECT ID, Name, Surname
FROM dbo.Users AS U

By using the AS U after the table name Users, you gave the table Users another name U. You can from that point on use U to reference Users, find below 2 examples. Both will return the same results:

SELECT Users.ID, Users.Name, Users.Surname
FROM dbo.Users

SELECT U.ID, U.Name, U.Surname
FROM dbo.Users AS U

What we basically did was renamed the Users table to U. You can't use table name Users because it is not visible for the query anymore. Now that we know where we want to get our data from, we need to tell the query which data columns we want.

SELECT Clause

The Select clause is used to determine the columns from your SQL Table. If you want all the attributes from your table, you can use asterisk (*) but is not best practice. By using asterisk , you return all the column data that also means sending unnecessary amount of data over the network, slowing down your application. You can also alias your column names when you select, the same as the table alias, find below example of column alias:

SELECT Users.ID AS UserID, Users.Name AS UserName, Users.Surname AS UserSurname
FROM dbo.Users

If you need to select unique data from your SQL Table, no duplicate records, you can use the DISTINCT clause as part of your Select. Below example will show you how to select distinct records:

SELECT DISTINCT ID, Name, Surname
FROM dbo.Users

If you need to concatenate 2 columns into 1 column with an alias, for example you want to concatenate the Name and Surname columns into a column named NameAndSurname. Below example will show you how:

SELECT DISTINCT ID, (Name + ' ' + Surname) AS NameAndSurname
FROM dbo.Users

If you want to add another column to your record set but with a hard coded value for all rows, it can be done using a hard coded value with an alias. Below is an example:

SELECT DISTINCT ID, (Name + ' ' + Surname) AS NameAndSurname, 'String Value' AS HardCodedValue 
FROM dbo.Users

You can also use integers as hard coded value. The above example will return all the Users with an extra column called HardCodedValue, all with the same value of String Value

Basically everything is a table. Below is an example where we select from a Derived Table:

SELECT derUsers.ID, derUsers.NameAndSurname, derUsers.HardCodedValue
FROM (SELECT DISTINCT ID, (Name + ' ' + Surname) AS NameAndSurname, 'String Value' AS HardCodedValue 
      FROM dbo.Users) derUsers

Another Fun Fact: A lions roar can be heard from 5 miles away.
Monday, August 21, 2017
, , , , ,

Backup SQL Database on another drive using SSMS

I came across a problem where I has two hard drives in my PC where my C drive was used to install Microsoft Windows and Microsoft SQL Server. My C Drive was full and I had to make backups of one of my SQL Databases and found that I didn't have enough space available to complete the backup. When I tried to make a backup using Microsoft SQL Server Management Studio (SSMS), only my C drive was available to make backups on but I had another hard drive called D in my PC with enough space. I found the following solution that worked for me.

Did you know 11% of people are left handed.

Before we start with the before and after, what is SSMS? SQL Server Management Studio (SSMS) is a tool included with Microsoft SQL Server for accessing, configuring, managing, administering and developing all components within Microsoft SQL Server. The tool includes both script editors and graphical tools which work with objects and features of the server.

This is how my view looked like before the change. I've opened Microsoft SQL Server Management Studio (SSMS), right clicked on my database that I want to backup and clicked on Back Up under the Tasks menu item:


Then I've set my back up to Disk under the Destination section and click on Add:

As you can see I only have my C drive available to backup to. Now to add drive D to the list of drives. firstly you need to enable xp_cmdshell command because it is by default disabled. To enable the xp_cmdshell command, use the sp_configure command:

EXEC sp_configure 'show advanced options', 1;
GO

RECONFIGURE;
GO

EXEC sp_configure 'xp_cmdshell',1
GO

RECONFIGURE
GO

Now that command xp_cmdshell is enabled, you can setup drive D using the following command:

EXEC XP_CMDSHELL 'net use D:'

Now, close your Microsoft SQL Server Management Studio (SSMS), reopen SSMS and follow the same steps as above to backup your database and you will see the following backup locations:
Now I have the D drive available to use to backups my databases on.

Did you know African elephants only have 4 teeth.
Tuesday, August 15, 2017
, ,
Posts ( )

Blog Archive

Popular Posts

Powered by Blogger.