Postgresql Basics

I’m back to tell you a few basic things about Postgresql for anyone struggling or just looking for answers in general.

The main basics still apply!  SELECT, FROM, JOINs, WHERE, HAVING, GROUP BY, ORDER BY.

SUBQUERIES:  All subqueries need an alias.

COALESCE:  Used like ‘NVL’ or ‘ISNULL’.  Here’s the syntax:  COALESCE(<column name>, <value to be used if it is null>, <next value to be used if that value is null>).  Here’s an example:  COALESCE(Invoice_Amount, 0).  So this example means in the event the ‘Invoice_Amount’ column is null, you would like a zero placed there.  You can also use cascading COALESCE values.  Here’s the syntax:  COALESCE(<column name>, COALESCE(<value to be used if it is null>, <next value to be used if that value is null>)).  Here’s an example:  COALESCE(Trip_Purpose, COALESCE(Exempt_Reason, ‘None’)).  Note:  Don’t enclose the field names in single quotes; only do this with a value you are identifying like ‘None’ in this example.

REPLACE:  Super helpful.  Allows you to replace something in a field with something else.  When to use?  Well, if you’re going to export the query to a csv file and some of your fields have commas, you’ll want to use this handy feature.  Here’s the syntax:  REPLACE(<column name>, ‘<value in field to replace>’, ‘<value to put in its place>’).  Here’s an example:  REPLACE(Job_Title, ‘,’, ‘ ‘).  May be hard to see but in this example we want to replace the comma with a space.  The value to be replaced and the value to replace it with need to be enclosed in single quotes.  An example of a before and after:  Value before REPLACE:  Manager, Operations.  Value after REPLACE:  Manager Operations.

Well that’s all I have time for today.  Stay tuned for more helpful info on various topics including Postgresql, Business Objects, Crystal Reports, Cognos, and Tableau!

BITietje 🙂

 

Postgresql Got You Down?

I’ve been working with Postgresql now for a few months.  It’s not as scary as I thought!

I wanted to post a couple of things I learned along the way in the hopes that it might help others looking for answers.  One of the things that kind of bugs me about some message boards or help docs is that some of them don’t explain what the syntax means.  If you’re new to sql, this can be a brain crusher because you’ll want to copy and paste into your editor but you will keep getting some type of error and won’t be able to figure out how to fix it.  I hope to explain those things here to make it easier for anyone trying to learn or just stuck on a specific piece of syntax.

Feel free to leave comments or ask questions!

BITietje 🙂

 

Tableau Server Kerberos SSO setup

I have recently had to setup SSO with Kerberos on Tableau Server 9.0 and discovered that there is one step that is missing from otherwise fairly straight forward process.

I discovered it when I got following error after completing the setup outlined in administrator guide

After doing a fiddler trace I have found that when Kerberos ticket is being passed to Tableau server I am getting HTTP 400 (BAD REQUEST) error.  What this means in our case is that HTTP header is getting cut off by the web server because its size exceeds what it can accept. This is fairly common scenario with Kerberos because the ticket is fairly lengthy.

After digging through online help I have found no mention of increasing MAX HTTP HEADER size anywhere and none of the SSO setup guides mentioned anything about it.

So I ended up pinging Tableau folks and explaining the issue to them. It turned out that it is exactly what I suspected – bad request due to HTTP request getting cut off and they have provided following TABADMIN commands that you should run at any point during Kerberos SSO setup:

open command line and navigate to %your server install location%\9.0\bin\

tabadmin set gateway.http.request_size_limit 32768

tabadmin set tomcat.http.maxrequestsize 32768

 

However, I would recommend setting the value to 65536 to be on a safe side. So:

tabadmin set gateway.http.request_size_limit 65536

tabadmin set tomcat.http.maxrequestsize 65536

 

Don’t forget to run tabadmin restart command after you run these for the changes to take effect.

 

Good luck!

 

Embedded Tableau Oscars Dashboard

(This post was originally published on BIHappy Blog)

A few months ago I published my Oscars dashboard (http://bihappyblog.com/2014/03/10/oscars-dashboard/). Recently, I decided to produce a new version of it, leveraging Tableau, and extending it with some additional features possible with some html5 integration. Embedding Tableau in an external web application framework is a great way to leverage Tableau terrific data exploration features like drilling, grouping and filtering with some intuitive, simple to understand and use interface suitable for a user’s portal or an executive audience. This example leverages my Oscars database file and allows exploration of Oscar nominated actors, actresses, directors as well as a free form exploration option for perusing more of the data set. Enjoy..

Tableau connected Medicare enrollment dashboard

(This post was originally published on BIHappy Blog)

Our medicare enrollment database continues to grow and now contains over 9M enrollment records across the country. I began collecting this information almost two years ago with my colleague Josh Tapley, and we used it to produce our medicare advantage dashboards using the SAP Dashboards (Xcelsius) tool, as well as our HTML5 reporting solution. Aside from being an interesting dataset, relevant to medical insurance professionals and anyone else interested in medicare and healthcare, this platform provides us the medium to demonstrate many technical advantages and techniques we often solve on projects. So, to add to our arsenal or medicare advantage dashboards, I have now added a Tableau version. This version looks and operates just like it’s siblings from SAP and our custom HTML solution, however uses completely different technology under the covers. To create it, we had to overcome several interesting challenges, from the ability to serve up Tableau content from our secure server which resides behind our firewall via secure proxy to the internet, addressing proxying, authentication and security challeneges to the ability to create visuals which do not exist natively in the tool, such as a donut chart. This dashboard is connected to the live data, and executed a query each time a state is selected. This design pattern is consistent across all three versions of this dashboard and is designed to demonstrate the ability to work with these dashboarding tools in a completely “hands free” no hassle, and no maintenance mode, where data is refreshed in the database and automatically reflected in the dashboard with no need for any intervention. Enjoy.

Business Objects 4.0 – Input Control / Select to Display Block

This gallery contains 17 photos.

Business Objects 4.1 – Setting a block to be displayed by selection within Input Controls (To follow this blog, I’m assuming you already know how to navigate within BO and you are familiar with creating and modifying documents). I’m sure you already know how to “hide when” within BO. (In case you don’t, instructions are […]

Adding custom column delimiter characters to CSV export options to WEBI

Recently I have run into needing to add more column delimiter options to CSV export in WEBI.

By default, it only has comma, semicolon and TAB as you can see in the screenshot below

Default CSV separators

After digging through some documents and searching around I didn’t find any official SAP solution to this and decided to take matters into my own hands and after fairly short search found the place where this setting is stored.

It is located in file called webiviewer.properties

There are a couple of copies of this file sprinkled around BO install, but we should only concern ourselves with ones located under webapps directory. All of the copies are identical and for simplicity and consistency sake we’ll change all of them.

Now to the procedure,

  • Stop SIA and Tomcat
  • Open all of the webiviewer.properties files you can find under webapps directory.
  • Locate following code:
    Code
  • Add your character(s) as per instructions in comments. Apply this change to all webiviewer.properties files in exact same way.
  • Save all of the files
  • Delete tomcat cache (Files located in Tomcat(#)/work/Catalina/localhost directory)
  • Start SIA and Tomcat.
  • Voila! In my case I’ve added a pipe character.
    Pipe added

This applies to both BO XI R4.0 and R3.1

An image free status indicator – using CSS3 and HTML5 alone.

When dealing with creating web applications, especially dashboards, there are many scenarios when one would like to visually represent the status of something. A nice “light bulb” or glass dot is great for this; a red or green status can easily be communicated this way.

When creating a web app, this can easily be done using an image of a colored dot. However, when dealing with mobile or modern web applications it can sometimes be desirable, or even necessary, to do this without the use of images. Luckily there is a way to accomplish this, and make it look great, without using any images at all; using HTML5 and CSS3 alone:


You can see a demo of this, and download the code, here:

To do this, first place this small amount of HTML on your page or in your app where you would like the status indicator to appear:

   <span class="dot"><span class="dot-inner"></span></span>

Second, copy and paste this CSS into your system’s style sheets:

.dot {
overflow: hidden;
border-style:solid;
border-width: 2px;
border-color: #666;
padding-top: 6px;
padding-left: 6px;
width: 32px;
height: 32px;
display: inline-block;
border-radius: 50%;
margin: 0.2em 0.4em 0.2em 0;
vertical-align:middle;
line-height: 32px;
background: #cccccc;
-webkit-box-shadow: inset 0 -6px 6px rgba(0, 0, 0, 0.5), 0 1px 0 rgba(255, 255, 255, 0.3), inset 0 1px 0 rgba(255, 255, 255, 0.3);
-moz-box-shadow: inset 0 -6px 6px rgba(0, 0, 0, 0.5), 0 1px 0 rgba(255, 255, 255, 0.3), inset 0 1px 0 rgba(255, 255, 255, 0.3);
box-shadow: inset -6px -6px 6px rgba(0, 0, 0, 0.3), inset 3px 3px 6px rgba(255, 255, 255, 0.3);
}
.dot-inner {
background: white;
opacity:0.35;
filter:alpha(opacity=35);
-webkit-box-shadow: 0px 0px 6px 3px rgba(255, 255, 255, 1);
-moz-box-shadow: 0px 0px 6px 3px rgba(255, 255, 255, 1);
box-shadow: 0px 0px 6px 3px rgba(255, 255, 255, 1);
border-radius: 50%;
overflow: hidden;
width: 6px;
height: 6px;
display: block;
line-height: 0;
}
.dot-red {
background: #990000;
}
.dot-green {
background: #009900;
}
.dot-yellow {
background: #999900;
}
/** add more colors here if you need **/

That’s it, you are done. Refresh the page and you should see the glass dot. It will be gray (or “off”) by default. To change its color, simple add an extra class to the outer span as such:

Red - <span class="dot dot-red"><span class="dot-inner"></span></span>
Yellow - <span class="dot dot-yellow"><span class="dot-inner"></span></span>
Green - <span class="dot dot-green"><span class="dot-inner"></span></span>

One reason this may be preferred over images is in the case when a user may be zooming in or out of a page/application. Because this is not an image it will always look crisp and smooth, no image degradation. Also, you have the flexibility of changing the dot’s color simply by adjusting the style sheet, no need to create a new image to represent each new color. You can even do this programmatically using something like jQuery. This can be especially handy if you need to represent a wide range of colors.

I hope you find this helpful. Feel free to let us know if you use this technique in your own application.

Google chairman notes the arrival of big data and machine intelligence everywhere

In a rare interview, Google Chairman Eric Schmidt gives Bloomberg his outlook for 2014 trends (http://www.bloomberg.com/video/ask-a-billionaire-eric-schmidt-s-2014-predictions-pmV~qd7qTeipbjKx6_wW1Q.html). In this 2 minutes video, Schmidt talks about several obvious trends, like “everyone will have a smart phone… essentially connected to a super computer”. Another important remark Schmidt made is about the “arrival of big data and machine intelligence everywhere”.

It is not about the invention of big data and machine intelligence. It is about the arrival. I find that to be the essence of the big data revolution. The idea that we can leverage computers and technology to process data and derive information and intelligence from thatdata is certainly not new. However, it has been hampered by slow processors, lack of storage capabilities, and inability to amass sufficiently significant amounts of data to deliver this intelligence. Past attempts to overcome the technology shortfalls have been painful and could only deliver meaningful results when applying large investments. Well, all that is changing now. It is now affordable and relatively simple to perform analytical tasks that were considered extremely challenging only a few years ago. My news analytics experiment is one example of that (http://bihappyblog.com/2013/12/05/predicting-the-news-with-sap-hana/). IT departments and organizations are finding that barriers that prevented them from delivering google like level of services are quickly disappearing, and the bar set by consumer web sites for level of service, sophistication and relevance can now be met with of enterprise applications. This is the challenge we in the BI industry will have to face and meet over the next few years. I believe that leveraging the knowledge and understanding developed during decades of data warehousing development to overcome technological impossibilities positions us, the BI industry veterans in a unique position to help drive the big data revolution in the corporate world. Modeling, abstraction skills, problem solving and technical know-how are the building blocks needed for any big data related project, and these are the traits of the trade for us in the BI industry.