July 09, 2014

How does SQL Server internally store the dates?

SQL Server  uses 8 bytes to store a datetime value—the first 4 for the date and the second 4 for the time. SQL Server can interpret both sets of 4 bytes as integers. For the date portion, the value SQL Server stores is the number of days before or after a base date of January 1, 1900. Because of this storage protocol, SQL Server assumed the date of January 1, 1900, when you didn't supply the date, SQL Server internally stored a value of 0. A negative number represents a date earlier than January 1, 1900.
SQL Server stores the second integer for the time as the number of clock ticks after midnight. A second contains 300 ticks, so a tick equals 3.3 milliseconds (ms). You can see the values for days and clock ticks by converting a datetime value to a binary(8) value and using the substring function to extract each set of 4 bytes. 

June 16, 2014

Firewall ports to open for SQL Server 2008

Firewall ports to open for SQL Server 2008

SQL Admin Connection- 1434 (TCP)
SQL Debugger - 135 (TCP)
SQL Service Broker - 4022 (TCP)
SQL TCP Browser - 2382 (TCP)
SQL UDP Browser - 1434 (UDP)
SQL Server - 1433 (TCP)  Note: This may have to be open on the client side as well
SQL Dynamic Port - 49172 (TCP)

Client ports are assigned a random value between 1024 and 5000

FROM sys.dm_exec_connections
WHERE local_tcp_port IS NOT NULL

June 13, 2014

Basic Database Terms

Basic Database Terms
A session is a single connection to SQL Server, identified by a unique SessionID value. It is initiated through an application when the open method is used on a connection object or through a tool like SSMS when the File | Connect menu item is selected. Even though multiple sessions may originate from the same application (and many query windows opened by the same user using the same SSMS instance), as far as SQL Server is concerned, these are all completely separate SQL Server sessions.
                        Locking occurs when a SQL Server session takes "ownership" of a resource by acquiring a lock, prior to performing a particular action on that resource, such as reading or updating. Locking will stay in effect until SQL Server releases the locks. Note that locking itself is not a problem; it has very little measurable impact on any aspect of our systems, including performance, except when it results in blocking or deadlocking, or when we are performing excessive monitoring of our system locks.
                        Blocking occurs when at least two sessions desire concurrent access to the same resource. One session acquires a lock on the resource, in order to perform some action, and so renders that resource temporarily unavailable to other sessions. As a result, other sessions requiring the same resource are temporarily blocked. Typically, the blocked sessions will gain control of the resource after the blocking session releases the locks, so that access to the resource is serialized. Note that not all concurrent access will cause blocking; it is dependent on the operations being performed by the sessions, which determines the type of locks that are acquired.
                A deadlock occurs when two sessions mutually block each other. Neither one can release the resources it holds until it acquires a lock on the resource the other session holds. A deadlock can also involve more than two sessions, trapped in a circular chain of dependencies. For example, session A may hold a resource that session B wants, and in turn session A is waiting for session C to release a resource. Session B may also hold a resource that session C wants. So session A is blocking B and is blocked by C, session B is blocking C and is blocked by A, and session C is blocking A and is blocked by B. None of the three sessions can proceed.
Pressure is a term used to indicate a state where competition for access to a certain resource is causing performance issues. In a database with well-designed tables and queries, SQL Server acquires and releases locks quickly and any blocking is fleeting, and undetectable by the end-user. However, in certain circumstances, such as when long-running transactions hold locks on a resource for a long time, or where a very high number of sessions all require access to the same shared resource, blocking issues can escalate to the point where one session that is blocked, in turn blocks other sessions, which in turn block others. As the "queue" of blocked sessions grows longer, so the load on the system increases and more and more users start to experience unacceptable delays. In such cases, then we say that the resource is experiencing pressure.

Transactions The simplest definition of a transaction is that it is a single unit of work; a task or set of tasks that together form an "all-or-nothing" operation. If some event interrupts a transaction in the middle, so that not all of it was completed, the system should treat the transaction as if it never occurred at all. Transactions can apply to other kinds of systems besides databases, but since this is a database-specific book, we'll be concerned only with database transactions. A transaction can be short, like changing the price of one book in the inventory, or long, like updating the quantity sold of every inventory item at the beginning of an accounting period.

May 20, 2014

QlikView vs Tableau

Qlikview is a vastly more capable tool for a technical person, the scripting language is amazingly powerful. Qlikview is a brilliant tool with amazing ETL capabilities and flexibility.

Associative Experience, In-Memory, Compression of data, on the fly - calculations and huge customziation in terms of pixel perfect dashboards. create layering of your charts and images, actions, navigation controls and show/hide options and several more. Decoding of other person work is easier.

1) The ease with which self-service BI can be achieved via a small scale trail deployment

2) The SQL scripting which is easy to understand by end-users

3) The Pivot functionality, because it is so very familiar to the high-end Excel users and allows for an extremely fast adoption rate

4) It can be deployed with limited training to end-users

a. For the “developer” end-user with a bit of previous VBA skills it is a breeze to create new applications

b. For the pure end-user the interaction with the data is amazingly simple

c. The “cloning” capability allows users to develop different views of the same data until they have the right dashboard look and feel

5) The fact that it brings a DASHBOARD to the end-users in mere minutes with the minimum of installation time and development time

6) Nothing beats showing a sales team the results of 3 million invoice records on a normal i3 pc with 2 GB ram (took +/- 10 minutes to develop for zero to a full blown dashboard that showed revenue by location, by customer, by salesman, by product, by year over a 3 year time axis)

Tableau has found its sweet-spot as an agile discovery tool that analysts use to create and share insights. It is also the tool of choice for rapid prototyping of dashboards. Tableau - Purely self service BI and Visualization tool. Drag and drop feature. VizQL and Show Me Options. It teaches you visualization. Visualization is much better here. But decoding of other person work is difficult.

1.From the rapid development and data discovery point of view Tableau is surpassing. You need only a db connection and you are on the way.

2.Tableau is very flexible with its data import. Tableau's data blending capability is very intuitive. This capability is useful when you have data spread across several different sources that has not gone through ETL processes. This is a problem analysts deal with routinely. They are unable to wait for the data warehouse team to develop ETL processes to provide the physical models they need to build an analysis.

3. The Tableau interface is Excel-like and has a low barrier to entry for analysts that are used to working in Excel.

4.Building a dashboard by mashing up visualizations in a Tableau worksheet is extremely simple. Users are able to build good presentation-quality dashboards in a very short time.

5.Tableau's annotations capabilities and its time and geographical intelligence are key differentiators.

6. Tableau has overcome limitations in data sharing with the introduction of a Data Server in Tableau 7.0. The Data server allows Data sources and extracts to be shared securely and opens up interesting new possibilities.

If your application can take advantage of the above characteristics, consider Tableau.

May 07, 2014

QlikView: Client Access Licenses

QlikView: Client Access Licenses
To connect to a QlikView Server (QVS), each client needs a Client Access License (CAL). The CALs are tied to the server serial number.

CAL Types:
1. Named User CAL
A Named User CAL is assigned to a unique and identified user who may access as
many QlikView documents as may reside on the server or server cluster to which
the Named User CAL is assigned. A Named User CAL may be transferred to
another user pursuant to the software licensing agreement, in which case there is
a 24-hour quarantine before the Named User CAL can be transferred to another
user. There is no time limit for how long a user assigned a Named User CAL can
access a QlikView document.

2. Document CAL
A Document CAL is assigned to a unique and identified user who may access only the one
QlikView document to which the Document CAL is assigned. Multiple
Document CALs can be assigned to a particular user. For example, if a user
connects to two QlikView documents, the user will have been assigned two
Document CALs. A Document CAL may be transferred to another user pursuant
to the software licensing agreement, in which case there is a 24-hour quarantine
before the Document CAL can be transferred to another user. There is no time
limit for how long a user assigned a Document CAL can access the QlikView
document to which the CAL is assigned.

3. Session CAL
A Session CAL allows any user, identified or anonymous/unidentified, on one
QlikView client to access as many QlikView documents as may reside on the
server or server cluster to which the Session CAL is assigned for a minimum
period of 15 minutes. For Session CALs, the QlikView client refers to each
unique instance of the QlikView client (for example, the AJAX client, QlikView
Desktop, or the Internet Explorer plugin) on the user’s machine. The minimum
session time for a Session CAL is 15 minutes, which means that sessions that
end in less than 15 minutes will still consume the session until the 15 minute
mark is passed; those which terminate after 15 minutes will consume their actual
session length.

4. Usage CAL
A Usage CAL allows any user, identified or anonymous/unidentified, to access
only one QlikView document, residing on the server or server cluster to which
the Usage CAL is assigned, from one client (for example, the AJAX client,
QlikView Desktop, or the Internet Explorer plugin) for a time period of 60
minutes per 28-day period. If a user exceeds the 60 minute time limitation, the
user will have consumed two Usage CALs without any warning being given to
the user. Every 28 days, the Usage CAL is refreshed and the user may once again
view a new QlikView document for 60 minutes, using the same Usage CAL.
Usage CALs are continuously recharged (at a pace corresponding to 1/28 of the
total number of Usage CALs assigned to the QlikView Server per day)

License Lease
A QlikView client that does not have a registered license is allowed to connect to QlikView Server and
“borrow” a license, so that the user can work offline for a period of 30 days. The QlikView client must
then make an authenticated log on (not anonymous) and obtain a Named User CAL. Each time QlikView
is started, QlikView tries to contact QlikView Server and renew the license lease. If the client cannot
reach the server after 30 days, the license lease expires.
A license lease can only be used with QlikView Desktop and the Internet Explorer plugin. This means a
license lease cannot be obtained when using an AJAX client.

In QlikView 10 open QEMC and follow this tree : tab System -> Licences -> then click on QlikView Server -> Client Access Licences -> Assigned Cals -> then type username and click on "Assign Cal" button and then Say Apply at the bottom. Now he/she can access the application from access point .

SQL Server Stored Procedure runs slow sometimes

Sometimes, you might face a very weird issue with a stored procedure on SQL Server 2008 R2. Sometimes, about once every month, some procedures that becomes very slow, takes about 6sec to run instead of a few milliseconds.

Possible reasons:

When the sp is compiled, it is cached and this cache is reused every time I call it, and this cached version gets corrupted for some reason.

Possible Solutions:

1. When you first compile a stored procedure, its execution plan gets cached.

If the sproc has parameters whose definitions can significantly change the contained query's execution plan like index scans vs seeks, the stored procedure's cached plan may not work best for all parameter definitions.

One way to avoid this is to include a RECOMPILE clause with the CREATE PROCEDURE statement.


 -- SP Body

By doing this, a new plan will be generated each time the procedure is called. If recompile time < time lost by its using the wrong cached plan, this is worth implementing. In your case, it will also save you the time/planning required to manually recompile this procedure every time you notice it is executing slowly.

2. Update your statistics:

EXEC sp_updatestats

sp_updatestats can trigger a recompile of stored procedures or other compiled code. However, sp_updatestats might not cause a recompile, if only one query plan is possible for the tables referenced and the indexes on them. A recompilation would be unnecessary in these cases even if statistics are updated.

3. Parameter sniffing
Parameter sniffing is a technique the SQL Server optimizer uses to try to figure out parameter values/ranges so it can choose the best execution plan for your query. In some instances SQL Server does a poor job at parameter sniffing & doesn't pick the best execution plan for the query.
With Parameter sniffing Sql Server uses to optimze the query execution plan for a stored procedure. When you first call the SP, Sql Server looks at the given parameter values of your call and decides which indices to use based on the parameter values.

May 05, 2014

BI: In-Memory or Live Reporting?

BI: In-Memory or Live Reporting?

In-Memory Data is Better than Live reporting in following scenarios.
1.When your database is too slow for interactive analytics
2.When you need to take load off a transactional database
Even though data is fast but some calcalation specifically Analytics can slow down the speed and need to load off a transactional database.
3.When you need to be offline
Bringing data into memory, you can work offine on database.

A Live, Direct Data Connection is better in following scenarios.
1. Database itself is fast and you just need to render the data. Avoid data silos and ensure a single point of truth by pointing your
analyses at a single, optimized database.
2.When you need up-to-the minute data
If things are changing so fast that you need to see them in real-time.

April 30, 2014

Set Analysis in Qlikview

Why use set analysis?

In QlikView the selection you make can be considered as a set, the selection not made are another set. Using set analysis these sets can be customized to make a conceptual selection so that we display the desired values on the charts. Set analysis always involves an aggregation function.

Qlikview set analysis is a powerful way for you to create dynamic data sets mainly for comparison analysis.
The QlikView set analysis is based on the set theory.

A set analysis is calculated before the chart is computed and redrawn. As you may notice, it is NOT calculated per each row/column in the chart. So, you may not guess a scope of product depending on the rows that are products. You may not guess another set of periods depending of the columns if the Period dimension is in column : the YTD or moving totals you may encounter are often valid only if the period is not a dimension of the chart. The sets let you create a different selection than the active one being used into the chart or table. The created group let you compare the aggregations of this group and the one of the current selection.
These different aggregations let you compare for example the sales of the current month and those of the
previous year, create YTD or moving totals. You may also create market shares, a percentage of the
products, of the year ….

A set modifies the context only during the expression that uses it. Another expression without any
set will get the default context, standard selection or the group of the Alternate State.
A set is always written between curly braces {…}. A set can modify the selection of one or several
dimensions. It can be composed of a single set but can include also an inner set.
 A set analysis is calculated once per chart (or table). NOT once per row. If you
want a different result in your set analysis according to the row being calculated and displayed, it will NOT
A set may be composed of:
- An identifier
- An operator
- A modifier
These 3 fields are optional. Of course, to write a set, you will need to incorporate one or several of these

1.  Set analysis begins and ends with { }

2. Modifier begins and ends with <  >
3. Operator:  =
4. Identifier is not shown but it is $ which is the default state in Qlikview.

For those of you who know SQL, set analysis is akin to a SELECT…. GROUP BY delivered in a concise expression. 
Identifier is like a SELECT keyword and Modifier is  like a WHERE clause or a filter in your SQL select statement. It works on the selected data set — based on your identifier — to add, remove or to modify existing selections.
Basic set analysis syntax consists of Identifiers, Operators and Modifiers.

Identifiers - Identifiers can be compared to a from statement in SQL. The identifier determines the set we will be operating on. Identifier begins and ends with the curly brackets. Following are the identifiers we can have in set analysis.

Represents an empty set
Represents the full set of all the records in the application
Represents the records of the current selection
Represents the previous selection
Represents the next (forward) selection
Represents the bookmark ID or the bookmark name
Group – group name (Alternate State)

Sum({Book1} [Sales]) : sales of the selection of Book1
Sum({Group1} [Sales]) : sales of the Alternate State Group1 (the syntax is identical)
Sum({1} [Sales]) : sum of everything (All dimensions are completely reset to All)
Sum({$} [Sales]) : sum of the current selection (= sum([Sales])

Operators - Operators are used to operate on the identifier. Following are the operators we can have in set analysis.

Operator Name
Returns a set of records that belongs to union of sets.
Returns records that belong to the first but
not the other of the two set identifiers.
Returns records that belong to both of the
set identifiers.
Symmetric Difference
Returns a set that belongs to either, but not
both of the set identifiers.

Example : sum({$ * Bookmark01} Sales) returns the sum of sales that is an intersection between the bookmark and current selection.

If you use the operators with the equal sign:
: add the set to the current selection
: remove the set from the current selection

Sum({1-$} [Sales]) : sum of the sales of the « database » except the current selection
Sum({GROUP1 * Book1} [Sales]) : sum if the sales for the intersection of Group1 and Book1 (the
members belonging to both)

Modifiers - Modifiers can be compared to the Where clause in SQL. These are additional or changed selections. The modifiers should always be in angle brackets.

Example - sum({$} Sales)

This will give the sum of sales for year 2010 and region US. Note that Year and Region are modifiers in the expression.

Using p() and e()

These functions returns the members that have (or have not) data. They are based on the associative
Syntax: {) [, Other
dimensions of the Set]>}
Please note that:
- Both functions p() et e() have the same syntax : one p() returns the possible values, the other e()
returns the excluded values
- They will be inserted into a greater set
- These functions do let you search the Top N products or those whose sales are greater than X :
they just return the members (ie the products) that have data, or those that have not.

Using IF statement

It may happen that we need different set analysis according to the user selection. For example, if the user
chooses one single product, we want to compute/display all of them. If he chooses several, we want to
show/compute them as they are selected.
The IF statement cannot be used within the set analysis itself. But we can use a variable … that can
contain a IF statement.
Let’s create a variable vSet that will be a formula: =if(GetSelectedCount(Product)=1, 'Product=', '$')

And we just need to use this variable in the set itself: sum({<$(vSet)>} Sales)