QlikView

I had attended an awesome training on QlikView where they  showed how to easily visualize data and create views using it.

The only limitation with it is that it gets too slow after about a million or so rows of data.

Didn’t get a chance to work on it much post that, but it was a nice experience.

 

 

–>

Advertisements

OData Basics

OData is a standard pioneered by Microsoft and subsequently  has been taken up as a standard by the government and other vendors.

Version 1.0, 2.0 and 3.0 are released under the MicrosoftOpen Specification promise.  Version 4.0 is currently being  standardized by OASIS.

 The OData protocol is used for creating Representational State Transfer (REST)-based [REST] data services, which enable resources, identified using Uniform Resource Identifiers (URIs) and
defined in an abstract data model, to be published and edited by web clients within corporate networks and across the Internet using simple Hypertext Transfer Protocol (HTTP) messages.  The OData protocol uses and extends the messaging semantics defined in AtomPub. 
 OData is based on the Entity Framework (EF) developed by Microsoft. EF is the Microsoft ORM Framework that insulates OO developers from knowing intimate details of the database and its storage. The Entity Framework enables developers to work with data in the form of domain-specific objects and properties, such as customers and customer

addresses, without having to concern themselves with the underlying database tables and columns where this data is stored. Use OData on the Microsoft platform using Visual Studio:

  • Start Visual Studio and create a new ASP.NET application
  • Model the entities that would be exposed by OData.
    There are two ways to do that

    1. Code First – Define you Entity classes (e.g. Account,
      Client) and Visual Studio will create the database tables that correspond
      to the classes
    2. Database First – Browse tables in an existing database
      and create Entity model that reflects the database model.  E.g. if
      you select the Account table, you will get a entity model for that table.
  • Add a WCF endpoint
  • In the WCF .svc file set the protocol to DataServiceProtocol
  • The OData service is done..

Note: This can also be done without using WCF.  Instead using the ASP.NET WEB API sample here.

 As you can see it is pretty simple to expose a database as an OData service provider. Note that there is no middle tier logic,
all OData requests will be send directly to the Entity Framework, which will query the database to return the information.

 URI Syntax for OData follows a standard format: http://www.abc.com/ services/Account(101)?$top=2&orderby=name

‘$’ is a reserved keyword for OData

See the query options for OData using a sample hosted on the OData web site.  You can click on the link to see results.

URI

Result

http://services.odata.org/OData/OData.svc/

Return All Entities for the OData service

http://services.odata.org/OData/OData.svc/$metadata

Return Metadata for the OData service entities.  This
includes the property names and their types

 Many more options for query syntax.  More details here

You can use the API explorer to see results at the OData site here.

By default the response is in ATOM format. To get JSON output add the $format=JSON to the query string.

OData URI Syntax has rich functionality and it is a subset of SQL.  This makes it easy to use for people familiar with SQL.  It allows the Entity Framework to use the URI syntax directly against a database
that has a LINQ provider (LINQ is out of scope for this discussion).

 OData is a quick and easy way to publish a Web API to an existing database.  If not controlled it allows web applications to execute a subset of SQL directly against the database.  This can quickly become a database scalability issue as the number of queries increase since there is no control over how expensive the queries can be.  e.g. if a user calls/Accounts?$select=*&$expand=Positions/Position_Details from the web it will impact the database performance.

 OData also has standard support for batching.
Batch request is a single request sent to the $batch endpoint of the OData service.  The batch request must contain a Content-Type header specifying a content type of “multipart/mixed” and a boundary specification. The body of a Batch Request is made up of an ordered series of retrieve operations and/or ChangeSets. A ChangeSet is an atomic unit of work that is made up of an
unordered group of one or more of the insert, update or delete operations.  The response has the response to the batch request in a single response.  More details here.

Google cloud storage uses something very similar to OData batching.  Here is a list of other libraries for all platforms.

Web API Best Practice

OData API

NA

../$metadata

/Accounts/101

/Accounts(101)

/Accounts/101/Activity?type=external&date_range=current_month

/Accounts(101/)/Activity?$filter=type eq ‘external’ and
date gt ’11/01/2013′ and date lt ’11/30/2013′

/Clients??limit=10,offset=10

/Clients/?$top=10%$skip=10

So what is the best practice for OData?

  • Do not directly expose an OData interface from your database without understanding the performance implications.
  • Use OData for data services and not for business level services (e.g. use for ../Clients/?$top=10%$skip=10 and not for ../Trading)
  • OData URI syntax is standard and well understood. Selectively implement an OData API in your data servces and use the middle layer to parse the OData query options and call stored procedures or SQL to retrieve the data
  • If batching requests is a requirement, implement OData batch processing.

–> Continue reading OData Basics

SQL Server Coding Tips

 

Note

Use ANSI syntax for Joining tables

Remove references to undocumented system
tables

VARCHAR / NVARCHAR and other datatypes
should always be created with the size

No use of defauly keywords as column/table
Names

No Variables declared with @@ prefix

Wildcard pattern usage based on the
documented recommendations

Usage of SCHEMABINDING in UDF without table
access

Add exception handling in the SPs and
functions

Avoid usage of nolock/readpast hint

Use Isnull over coalesce wherever possible

Length and data type of procedure
parameters should match with the base comparison tables

Trim and Len function needs to be used as
appropriate. 

Join needs to be improved and needs to have
all the relevant columns from the joined columns.

Repetitive queries need to be converted
into the temporary tables/table variable to avoid running the queries
multiple times

Make the transaction as small as possible
to avoid any blocking deadlocking and have better concurrency

String breaking functions should be
executed once and values should be stored in a table variable for the further
reuse in the stored procedure

Opportunity to split the big query into
smaller chunks.

Use Quotename function instead of manually
putting Square brackets around the columns.

 

 

–>

OLTP vs OLAP

OLTP (Database, DB, rdbms) vs OLAP (Data Warehouse, DW)

READ:

http://www.cbsolution.net/techniques/ontarget/olap_vs_oltp_what_makes

http://it.toolbox.com/blogs/opensource-analytics/database-vs-data-warehouse-8286

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

best: http://datawarehouse4u.info/OLTP-vs-OLAP.html

OLAP

OLAP is part of the broader
category of 
business
intelligence
, which also encompasses relational
database
, report writing and data
mining
.[2] Typical
applications of OLAP include 
business
reporting
 for sales, marketing,
management reporting, 
business process management (BPM),[3] budgeting and forecastingfinancial
reporting
 and similar areas, with new applications coming
up, such as 
agriculture.[4] The
term 
OLAP was
created as a slight modification of the traditional database term OLTP (Online
Transaction Processing).
[5]

 OLAP
consists of three basic analytical operations: consolidation (roll-up),
drill-down, and slicing and dicing.
[

Wiki source

 

Unlike relational
databases
, which had SQL as the standard query language, and
widespread 
APIs such as ODBCJDBC and OLEDB, there was no such unification in the OLAP world
for a long time. The first real standard API was 
OLE DB for OLAPspecification
from 
Microsoft which
appeared in 1997 and introduced the 
MDX query language. Several OLAP vendors – both
server and client – adopted it. In 2001 Microsoft and 
Hyperion announced the XML for Analysis specification,
which was endorsed by most of the OLAP vendors. Since this also used MDX as a
query language, MDX became the de facto standard.
[16] Since
September-2011 
LINQ can
be used to query 
SSAS OLAP cubes from Microsoft .NET

OLAP, (On-line
Analytical Processing), has the mission of extracting data for decision making
support process. It is a relatively new technology and in the data warehouse
realm warrants strong attention. Here, according to Gill & Rao, are a few
points that uniquely define OLAP:

  • It presents a multidimensional,
    logical view of the data in the data warehouse. The view is independent of
    the way the data is stored.
  • It always involves interactive
    query and analysis of the data. The interaction is usually multiple
    passes, involving drilling down into successively lower levels of detail
    data or roll-ups to higher levels of summarization and aggregation.
  • It offers analytical modeling
    capabilities, including a calculation engine for deriving ratios,
    variances, etc., involving measurements or numerical data across many
    dimensions.
  • It supports functional models for
    forecasting , trend analysis and statistical analysis.
  • It retrieves and displays data in
    2D or 3D cross-tabs, charts & graphs with easy pivoting of axis (to
    see what “pivoting” does, try this feature on a Microsoft Excel
    spreadsheet ). Pivoting is key because business users need to analyze the
    data from different perspectives; and the analysis of one perspective
    leads to business questions to be examined from another perspective.
  • It responds to queries quickly,
    so the analysis process is not interrupted and the information is not
    stale.
  • It has a multi-dimensional data store
    engine, which stores data in arrays. These arrays are a logical
    representation of the business dimensions.

In fewer words,
OLAP multiplies the power of the on-line database power-user.

source

 

 OLAP
data is stored in a multidimensional database.
Whereas a relational database can be thought of as two-dimensional,
a multidimensional database considers each data attribute (such as product,
geographic sales region, and time period) as a separate “dimension.”
OLAP software can locate the intersection of dimensions (all products sold in
the Eastern region above a certain price during a certain time period) and
display them. Attributes such
as time periods can be broken down into subattributes.

OLAP
can be used for data mining or the discovery of previously
undiscerned relationships between data items. An OLAP database does not need to
be as large as a data warehouse,
since not all transactional data is needed for trend analysis. Using Open
Database Connectivity (ODBC), data can be
imported from existing relational databases to create a multidimensional
database for OLAP.

Two
leading OLAP products are Hyperion Solution’s Essbase and Oracle’s Express
Server. OLAP products are typically designed for multiple-user environments,
with the cost of thesoftware based
on the number of users.

SOURCE

 

Short
for Online Analytical Processing, a category of software tools that
provides analysis of data stored in a database.
OLAP tools enable users to analyze different dimensions of multidimensional
data. For example, it provides time series and trend analysis views. OLAP often
is used in data mining.

The chief component of OLAP is the OLAP server,
which sits between a clientand
database
management systems (DBMS)
. The OLAP server understands how data is
organized in the database and has special functions for analyzing the data.
There are OLAP servers available for nearly all the major database systems.

 

source

OLTP

 

A type
of computer processing in which the computer
responds immediately touser requests. Each request is considered
to be a transaction. Automatic
teller machines for banks are an example of transaction processing.

The opposite of transaction processing is batch processing,
in which a batch of requests is stored and then executed all at one time. Transaction
processing requires interaction with a user, whereas batch processing can take
place without a user being present.

 

SOURCE

New in SQL Server 2014, In-Memory OLTP
is a memory optimized OLTP database engine for SQL Server.
(REF)

 

 

Database

1.
Used for Online Transactional Processing (OLTP) but can be used
for other purposes such as Data Warehousing. This records the data from the
user for history.

2.
The tables and joins are complex since they are normalized (for
RDMS). This is done to reduce redundant data and to save storage space.

3.
Entity – Relational modeling techniques are used for RDMS database
design.

4.
Optimized for write operation.

5.
Performance is low for analysis queries.

Data
Warehouse

1.
Used for Online Analytical Processing (OLAP). This reads the
historical data for the Users for business decisions.

2.
The Tables and joins are simple since they are de-normalized. This
is done to reduce the response time for analytical queries.

3.
Data – Modeling techniques are used for the Data Warehouse design.

4.
Optimized for read operations.

5.
High performance for analytical queries.

6.
Is usually a
Database.

It’s
important to note as well that Data Warehouses could be sourced zero to many
databases.

SOURCE

 

 

–>

OLTP (Database, DB, rdbms) vs OLAP (Data Warehouse, DW)

OLTP (Database, DB, rdbms) vs OLAP (Data Warehouse, DW)

READ:

http://www.cbsolution.net/techniques/ontarget/olap_vs_oltp_what_makes

http://it.toolbox.com/blogs/opensource-analytics/database-vs-data-warehouse-8286

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

best: http://datawarehouse4u.info/OLTP-vs-OLAP.html

OLAP

OLAP is part of the broader
category of
business
intelligence
, which also encompasses relational
database
, report writing and data
mining
.[2] Typical
applications of OLAP include
business
reporting
 for sales, marketing,
management reporting,
business process management (BPM),[3] budgeting and forecasting, financial
reporting
 and similar areas, with new applications coming
up, such as
agriculture.[4] The
term
OLAP was
created as a slight modification of the traditional database term OLTP (Online
Transaction Processing).
[5]

 OLAP
consists of three basic analytical operations: consolidation (roll-up),
drill-down, and slicing and dicing.
[

Wiki source

 

Unlike relational
databases
, which had SQL as the standard query language, and
widespread
APIs such as ODBC, JDBC and OLEDB, there was no such unification in the OLAP world
for a long time. The first real standard API was
OLE DB for OLAPspecification
from
Microsoft which
appeared in 1997 and introduced the
MDX query language. Several OLAP vendors – both
server and client – adopted it. In 2001 Microsoft and
Hyperion announced the XML for Analysis specification,
which was endorsed by most of the OLAP vendors. Since this also used MDX as a
query language, MDX became the de facto standard.
[16] Since
September-2011
LINQ can
be used to query
SSAS OLAP cubes from Microsoft .NET

OLAP, (On-line
Analytical Processing), has the mission of extracting data for decision making
support process. It is a relatively new technology and in the data warehouse
realm warrants strong attention. Here, according to Gill & Rao, are a few
points that uniquely define OLAP:

  • It presents a multidimensional,
    logical view of the data in the data warehouse. The view is independent of
    the way the data is stored.
  • It always involves interactive
    query and analysis of the data. The interaction is usually multiple
    passes, involving drilling down into successively lower levels of detail
    data or roll-ups to higher levels of summarization and aggregation.
  • It offers analytical modeling
    capabilities, including a calculation engine for deriving ratios,
    variances, etc., involving measurements or numerical data across many
    dimensions.
  • It supports functional models for
    forecasting , trend analysis and statistical analysis.
  • It retrieves and displays data in
    2D or 3D cross-tabs, charts & graphs with easy pivoting of axis (to
    see what “pivoting” does, try this feature on a Microsoft Excel
    spreadsheet ). Pivoting is key because business users need to analyze the
    data from different perspectives; and the analysis of one perspective
    leads to business questions to be examined from another perspective.
  • It responds to queries quickly,
    so the analysis process is not interrupted and the information is not
    stale.
  • It has a multi-dimensional data store
    engine, which stores data in arrays. These arrays are a logical
    representation of the business dimensions.

In fewer words,
OLAP multiplies the power of the on-line database power-user.

source

 

 OLAP
data is stored in a multidimensional database.
Whereas a relational database can be thought of as two-dimensional,
a multidimensional database considers each data attribute (such as product,
geographic sales region, and time period) as a separate “dimension.”
OLAP software can locate the intersection of dimensions (all products sold in
the Eastern region above a certain price during a certain time period) and
display them. Attributes such
as time periods can be broken down into subattributes.

OLAP
can be used for data mining or the discovery of previously
undiscerned relationships between data items. An OLAP database does not need to
be as large as a data warehouse,
since not all transactional data is needed for trend analysis. Using Open
Database Connectivity (ODBC), data can be
imported from existing relational databases to create a multidimensional
database for OLAP.

Two
leading OLAP products are Hyperion Solution’s Essbase and Oracle’s Express
Server. OLAP products are typically designed for multiple-user environments,
with the cost of thesoftware based
on the number of users.

SOURCE

 

Short
for Online Analytical Processing, a category of software tools that
provides analysis of data stored in a database.
OLAP tools enable users to analyze different dimensions of multidimensional
data. For example, it provides time series and trend analysis views. OLAP often
is used in data mining.

The chief component of OLAP is the OLAP server,
which sits between a clientand
a database
management systems (DBMS)
. The OLAP server understands how data is
organized in the database and has special functions for analyzing the data.
There are OLAP servers available for nearly all the major database systems.

 

source

OLTP

 

A type
of computer processing in which the computer
responds immediately touser requests. Each request is considered
to be a transaction. Automatic
teller machines for banks are an example of transaction processing.

The opposite of transaction processing is batch processing,
in which a batch of requests is stored and then executed all at one time. Transaction
processing requires interaction with a user, whereas batch processing can take
place without a user being present.

 

SOURCE

New in SQL Server 2014, In-Memory OLTP
is a memory optimized OLTP database engine for SQL Server.
(REF)

 

 

Database

1.
Used for Online Transactional Processing (OLTP) but can be used
for other purposes such as Data Warehousing. This records the data from the
user for history.

2.
The tables and joins are complex since they are normalized (for
RDMS). This is done to reduce redundant data and to save storage space.

3.
Entity – Relational modeling techniques are used for RDMS database
design.

4.
Optimized for write operation.

5.
Performance is low for analysis queries.

Data
Warehouse

1.
Used for Online Analytical Processing (OLAP). This reads the
historical data for the Users for business decisions.

2.
The Tables and joins are simple since they are de-normalized. This
is done to reduce the response time for analytical queries.

3.
Data – Modeling techniques are used for the Data Warehouse design.

4.
Optimized for read operations.

5.
High performance for analytical queries.

6.
Is usually a
Database.

It’s
important to note as well that Data Warehouses could be sourced zero to many
databases.

SOURCE

 

 

–>

SQL Server Getting Started

Download Here: http://technet.microsoft.com/en-US/sqlserver/ff898410

Getting Started

1.
Lesson
1: Basic Navigation in SQL Server Management Studio

2.
http://www.quackit.com/sql_server/tutorial/

3.
http://www.wikihow.com/Write-Basic-Sql-Statements-in-Sql-Server

4.
http://www.discoverthat.co.uk/tech/program/sql/various_sql.html

Startup ERROR RESOLVING:

a.
In the Management studio object window, I tried to create a new Database
and got this error:

—————————

Microsoft Visual Studio

—————————

A network-related or
instance-specific error occurred while establishing a connection to SQL Server.
The server was not found or was not accessible. Verify that the instance name
is correct and that SQL Server is configured to allow remote connections.
(provider: Shared Memory Provider, error: 40 – Could not open a connection to
SQL Server)

I found (using this ) that my svc was
stopped. But I get this error when trying to start it:

—————————

SQL Server Configuration
Manager

—————————

The request failed or the
service did not respond in a timely fashion. Consult the event log or other
applicable error logs for details.

3.
Also tired to start svc using:

·         Goto Start > All Programs
> SQL Server 2008 > Configuration Tools > SQL Server Configuration
Manager > SQL Server Services
.

And here you’ll see all the instances and their state.

·         The state
of the instance you were trying to connect can be stopped here.

·         Double
click on the instance and then click on connect.

·         It will
connect and now go back and run your application, you will be able to connect
with no error.

But  got the same error and in eventvwr it says:

The
SQL Server (SQLSERVER) service terminated unexpectedly.  It has done this
6 time(s).

Further
below it showed Acess Issues

But
I am launching it as admin, by launching mmc.exe from C:\Windows\System32 as
Admin and from there opening the Computer Management snap-in.

 

4.
I also
tried all stuff mentioned here:
http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

5.
Default Port for SQL is 1433, open this in inbound rules

6.
And tried this too: http://www.codeproject.com/Questions/335874/A-network-related-or-instance-specific-error-occur

 RESOLUTION:

.       Well, my
Studio is unable to connect to the DB so I downloaded the SP1 from http://www.microsoft.com/en-us/sqlserver/get-sql-server/try-it.aspx#tab2

I installed it on top of the existing
installation, though with a different server name and it worked.

 

 

–>