One useful feature in SQL Server is something called the OVER clause. The OVER clause has been available since SQL Server 2005 and allows you to perform window functions over a set of data. SQL Server provides two types of window functions: aggregate window functions and ranking window functions. I will explain what aggregate and ranking window functions are below. The Adventure Works sample database for SQL Server 2008 R2 will be used for all examples. This database models a retail store that sells biking products. Some may want to review the schema for AdventureWorks for better understanding.
Aggregate Window Functions
If you have used SQL’s GROUP BY clause, then you are probably familiar with aggregates. Aggregate functions allow you to perform a calculation over a set of data records. Each product sold by Adventure Works belongs to a subcategory which in turn…
I had a situation today where I have a stored procedure that is structured as follows:
1 CREATE PROCEDURE _Entity_GetByKeywordTypeDateRange( 2 @keyword varchar(255) = null, 3 @typeA bit = 0, 4 @typeB bit = 0, 5 @typeC bit = 0, 6 @startDate datetime, 7 @endDate datetime) AS 8 BEGIN 9 10 SELECT 11 EntityId, 12 TypeId, 13 — Field List 14 FROM 15 EntityTable 16 WHERE 17 — Where Clause 18 END
Due to the framework we’re using, I don’t have a lot of control over the argument list here, unfortunately. I was trying to find out the best way to handle the bit flags. Types A, B, and C correspond to TypeId’s 1,2, and 3 respectively.
I had toyed with using some kind of progression of OR statements…
Metadata Management- Fun stuff, right? Depending on what your relationship to an organization’s data is, it can have many different meanings. Database developers and administrators tend to focus on technical metadata, which provides descriptive information on table structures, job streams, ETL mappings, data lineage, etc.
Business users have a need for metadata as well. Attribute definitions, business rules, formulas & calculations, and system definitions provide context to your data and help turn it into real information. Often in the form of a corporate glossary or data dictionary, business metadata can provide clarity to an organization.
One of the challenges of getting your organization to buy in on the need for Metadata Management is that it is often not well understood by business stakeholders. Have you ever been in a meeting that was called because of a dispute over the definition of a…
In the month of July we’re going to be holding a FREE, half-day, hands-on workshop on MDX and Analysis Services around the country.
If you’re in the Los Angeles, New York City or Hartford, CT areas, come join us. We’ll be deconstructing real-world examples and in the process explaining important things to consider in how Analysis Services handles certain expressions.
8:30AM – 9:00AM Registration & Breakfast
9:00AM – 12:00PM Hands-On Workshop
Wed. 07/15/2009 – Anaheim, CA – Click Here to Register
Wed. 07/15/2009 – Burbank, CA – Click Here to Register
Tue. 07/28/2009 – New York, NY – Click Here to Register
Wed. 07/29/2009 – Bloomfield, CT – Click Here to Register
Here are more details about the event:
Tallan and New Horizons are pleased to present a Complimentary Half-Day, Hands-On Workshop: Real-World SQL Server Analysis Services and MDX
Overview of the Workshop
MDX is the universal query language for OLAP databases, just like…
Yesterday, I attended a seminar covering the Oracle BI landscape. My aim was to come out of this session with a clearer idea of how Oracle products correspond to the Microsoft BI stack. My impression going into this seminar was that Oracle had many, many applications bundled under the BI umbrella. Nevertheless, I was surprised by the sheer number of options available. Practically every piece of the MS BI Stack has at least two parallel products on the Oracle side, in some cases many more. My second impression was that Oracle has done some very credible work to integrate the vast number of applications they’ve developed alongside their Siebel and Hyperion purchases.
Here’s how Oracle visualizes their BI offering:
This post is intended to introduce Analysis Services 2005/2008 foundational concepts.
SQL Server Analysis Services is a component included in the Microsoft SQL Server product, and its use is fully covered under the same license (which applies to Integration Services and Reporting Services as well). Like the database engine, SSAS has a range of features stratified by Express, Workgroup, Standard Edition and Enterprise Editions.
SSAS is an OLAP, multi-dimensional database. While a relational data warehouse can also be described as OLAP, products such SSAS, Cognos and Essbase have fundamental differences. These OLAP servers:
· Aggregate data from a variety of sources into a compressed format optimized for query response
· Emphasize end-user navigation with modeling capabilities such as hierarchies, or drill-paths
· Employ MDX as the standard query language for retrieving result sets
· Use XMLA as the standard communication mechanism between clients and servers
Reporting Services Installation
Reporting Services 2008 is included as part of the installation for SQL Server 2008. To install Reporting Services, select the checkbox that says “Reporting Services” under the “Instance Features” section.
As part of the installation, a Service Account will be configured for the Report Server.
The Report Server runs under a single Windows Service using either a Built-in account or Domain Windows user account.
The report server service contains:
Report Server Web Service
Background processing application for scheduled report processing and subscription delivery
Two big pieces of news on the Microsoft BI front:
Performance Point Server is being dismantled. After the next service pack, MS will cease develop of the product as its own entity. The Monitoring & Analytics capabilities will be bundled into the enterprise version of SharePoint. The Planning application looks to be dead in the water, however.Although PPS was complicated and unwieldly, it had potential and an interesting vision. I don’t see the failure of any part of the MS BI stack as a positive thing. The official press release and blog reactions are here.
While Microsoft had previously described a major-minor release schedule as the model for SQL Server going forward, the next version sounds like an interesting departure from that. Code-named “Kilimanjaro”, the next SQL Server release has been described as minor and BI-focused.Two components described thus far are “Madison”…
Once tables grow into the millions of records, they become candidates for partitioning. Table partitioning offers many benefits, particularly in warehouse environments. Since data is split into smaller units of storage, backups can target filegroups with a higher rate of change. Systems with multiple CPUs see improved query performance as partitioned data leads to greater parallelism. Perhaps most significant is the ability to swap in huge amounts of data by partition switching, an operation that is practically instantaneous.
Tables can be partitioned horizontally or vertically. With vertical partitioning, columns are split out into separate physical tables. This post focuses on horizontally partitioned tables, which take advantage of new constructs Microsoft added in the 2005 release – partition functions and partition schemes. Table partitioning is an Enterprise Edition only feature in SQL Server 2005 and 2008.
SAP Business Objects Data Integrator XI R2/R3
The purpose of this presentation is to familiarize the team with some of the features of Business Objects Data Integrator (DI). The presentation is also aimed at providing some insight into how technology can affect the design and implementation of the ETL processes’.
While at a high level it is best that an ETL architecture be technology agnostic, the physical implementation can stand to benefit by being designed to take advantage of the features provided by the technology.