Note on precision & scale in SQL Server

Generally for storing a variable like 123.45 we can use a decimal or numeric data type. The usual notation for these data types are decimal [ (p[ ,s] )] & numeric[ (p[ ,s] )], p stands for precision & s stands for scale.

Precision is number digits in variable

Scale is of digits to the right of the decimal point

For ex: 12.345 p = 5 & s = 3

The sample query explains the behavior of the decimal, float & int

declare @t table(

     c1 int,

     c2 float,

     c3 decimal(18,2)

)

insert into @t (c1,c2,c3) select 12.345, 12.345, 12.345

insert into @t (c1,c2,c3) select 10/3, 10/3, 10/3

insert into @t (c1,c2,c3) select 10/3.0, 10/3.0, 10/3.0

insert into @t (c1,c2,c3) select 10/3.0, 10/3.0, round(10/3.0, 2)

select * from @t

c1        c2                    c3

12        12.345                12.35

Note: The value in C3 is truncated to two decimal places, C1 completely ignored the fraction part.

3           3            3.00

Note: The fraction part is completely ignored because the denominator is an integer

3           3.333333           3.33

Note: The fraction part is retained correctly

3           3.333333           3.33

Note: The fraction part can also be achieved by rounding the value using round function

TSQL Rambling

Wanted to share my thoughts on three interesting things stumbled across in TSQL

1. SQL Unary Operator

Lets create a simple table as follows

declare @TestPlus table

(

Type varchar(2),

TypeName varchar(10),

code int

)

insert into @TestPlus values ('P', 'Procedure', 1)

insert into @TestPlus values ('T', 'Table', 2)

insert into @TestPlus values ('V', 'View', 3)

insert into @TestPlus values ('F', 'Function', 4)

What is the output for the following select query?

select Type from @TestPlus where Code in ( +1, +++3, +4)

select +++Type from @TestPlus where Type in (+'P', +++'T')

When I saw this select query for a second I thought its trick question on how to use ++ operator, however it turns out that the result has nothing to do with the “+++” operator. Quickly browsed through MSDN and found that it has no effect on the evaluated expression

“Although a unary plus can appear before any numeric expression, it performs no operation on the value returned from the expression.”

http://technet.microsoft.com/en-us/library/ms174362.aspx

SQL Row Constructor

I was surprised to see a query like this

;with cte as

(

select dddid, payorid

from (values  ('100100', 1), ('100101', 2), ('100102',3 ) ) f(ClientID, SeqNo)

)

select * from cte where SeqNo = 3

turns out that this type of query is called row constructor a new feature introduced in SQL 2008.

3.  SQL Strict Names

Check out the following queries, spot the odd man out

select top 10  * from [dbo].[TestUpSurdTable]

select top 10  * from [dbo].[TestUpSurdTable]_Final

I was expecting two different result sets however the same result set was repeating twice, turns out that the “[ ]” ignored what ever is after the underscore

Best Data Visualization Libraries

Best Data Visualization Libraries

I just observed a  trend for inforgraphic style of reporting in business. Infographic reports very tangible, click, drag, drop, add elements which makes the report more like a stand alone app.

I complied a short list visualization libraries based on javascript which I personally used. I will be updating this list as I find something interesting.

1. Google Charts

https://developers.google.com/chart/

2. Data Driven Documents

http://d3js.org/

3. Protovis

http://mbostock.github.io/protovis/

Visual Studio 2012 Designer Issues

I recently uninstalled visual studio 2012 from my PC which already had VS2010 installed. The uninstall messed up the UI designer dll’s and I got the following errors in SSIS & SSMS

Couldn’t open SSIS packages

The database diagram designer had trouble opening..

The cause the dll’s at the following location are corrupted

C:\Program Files (x86)\Common Files\microsoft shared\MSDesigners8

The fix:

1.       Use the installation CD  of vs2010 and repair or

2.       Just copy the MSDesigners8 files from a PC which doesn’t have this issue.

The second choice is easier, I copied the files from my colleagues PC and replaced the contents in the MSDesigners8  folder. 

You if you have the same issue, you can download  the dll’s  here

https://drive.google.com/file/d/0Bz1hNSwS5Wi4WU95OG5ERTQ4cG8/edit?usp=sharing

Big Data Camp 27/10/2013

Big Data Camp was my first un-conference in big data. It’s really different compared to a regular conferences, the sessions are created on the fly, walk in and walk out are welcome. From what I have seen, it was non-technical, focused on the noob to intermediate skill market cap. The host Dave, managed the event very eloquently. Overall, it was good event. I complied small notes on what I liked.   

Talk1: Hadoop in consumer security products

• This organization implements hadoop technology to analyze the sales of their flagship consumer security products
• Existing data warehouse products and tools were OK, although they saw lot of customer’s showing up on their site, the sales reduced.
• The product was not at fault, but the customer analytics was.  The primary touch points or data points viz, server log, click stream etc were fed to hadoop, analytics was performed using sql, hive, mahout, used linear regression for modeling their data.

Talk 2: How to choose data analytics product

This presenter works at Datapad. “There is no one right way, there is a right way for your data, for your team” kinda made sense. Advocated the use of Python and Pandas library.

Talk 3: Block box vs transparent data modeling

I was trying to figure out what his point was, this is my understanding, the functionality of any google product is basically a black box and he was not cool with that or whatever.

Talk 4: Mainframe + Hadoop

This talk was pretty interesting, the presenter works at Syncsort company in CA.  

• 75% of data stored in mainframes (fact)
• Insurance, Retails and Banks store data in mainframes
• Offload mainframe data and batch process it in hadoop
• Mainframe store in ebcdic data type, (new info)
• ADP is hiring people for mainframes seriously?? (Maybe I should learn COBAL.. heck no!!)

Talk 5: Time series database – things happening in time

This was cool too, open source database called InfluxDB - Database based on events,

• HTTP native, show and do the analytics in a browser
• Read/write, manage and security with Http
• I asked the presenter, how is this product different form Storm, Spark & StreamInsight, could not answer my question 100%

Some other random tidbits, big data GUI tools, IBM Big sheets, Datameer &Talend

Python Tools for Data Science

I believe Python is increasingly becoming the de facto language for data science applications. Of the many good feature of this language, string manipulation is ridiculously easy and it is a very simple language to pick up. In this post I put together the tools and IDE’s related to Python which I found useful for data science applications.

Scientific Computing

• NumPy http://www.numpy.org/, scientific computing toolkit
• SciPy http://www.scipy.org/, scientific computing toolkit
• Matplotlib http://matplotlib.org/, graphics and plotting
• Scikit http://scikit-learn.org/, machine learning library  

IDE for Python

• Stani Editor,  http://pythonide.blogspot.com/, decent simple editor
• PythonXY https://code.google.com/p/pythonxy/, IDE built with Spyder
• Liclipse, http://brainwy.github.io/liclipse/ standalone python IDE based on eclipse
• PyDev, http://pydev.org/ plugin for Eclipse

Basic math skills for data science  

• Linear Algebra
• Statistics
• Probability
• Calculus

Handling M:M ring in Entity Modeling

Complex business process operations can be effectively visualized with Entity Relational ER modeling. ER modeling is proven to be the best way to design and model relational databases, this design choice is based of Dr.Peter Chan’s papers published in 1976. Before discussing the M:M ring problem in ER modeling, I will brief description the basics of ER modeling.

Nouns in an english sentence are usually the entities ex: Customers, Company, Teachers etc. The physical properties of these nouns are attributes for entities. ex height, weight, date, etc.

Entities can be related to each other, the relation is usually verbs/ adverbs ex: “based on”. “for”, “basis off”, “bought from”, “operator of”, “issued for”, “stored in”, “responsible for” etc

Simple ER example would be “Customers can place Orders ”, in this example, the relation is called 1:M, one customer can place more than one order (m for many). In some cases, like social and a person would be a 1:1 relation, one person can have 1 social

Modeling employment history in entities is a bit of a pickle. For instance, consider entities person, company and position. Person can work at a company holding a position  or, we can say a company hired a person for a position, or a position is held by a person employed at a company. Visually this can be as shown below.

It becomes increasingly tricky to answer the following question for each person we want to track the position held, for what company and how long. This configuration leads to what is known as M:M ring. This issue can be resolved by adding three other small entities as shown below.