T-SQL Fundamentals

Comparing Similar Approaches to Writing Queries

By Dan Meyers

I often get asked by clients about the “best” way to write a query. Whether they should use IN or EXISTS, table variables or temp tables, a LEFT OUTER JOIN or NOT EXISTS, etc… Most often it depends on what your data is like (does it contain NULL values for example), how are your tables indexed, and a number of other things. In order to make the best decision you really need to understand the details and internal workings of the query engine when using the various approaches that are available to you in T-SQL.

Below are some links to some blogs posts that I think do a good job of explaining the details about some of the most common questions I get when working at a client. Most of them are from Gail Shaw and her SQL in the Wild blog. The others are from SQL Server Central.

LEFT OUTER JOIN vs NOT EXISTS
EXISTS vs IN
IN vs INNER JOIN
NOT EXISTS vs NOT IN
Table Variables vs Temp Tables
JOINs - ON clause vs WHERE clause

Overcoming Calendar Limitations in PerformancePoint Planning

Some financial analysts may require to be able to present their budget and forecasts in both fiscal and calendar years. This becomes somewhat of a design challenge when it comes to PPS Planning since the application is set to handle only one Time dimension which is built at the onset of the application’s creation. Moreover, once generated, the time dimension that was created using the “calendar” wizard, is then very hard if not impossible to change.

Nevertheless, if a company does have a valid business reason to plan their budget using the calendar as well as the fiscal year, or important business requirements could not be accommodated using the existing application-generated time dimension, then the PPS Planning developer could consider the design solution suggested below.

In this example, the initial time dimension was built based on a fiscal calendar with July as the first month. However, the business user’s requirements are to create an alternative hierarchy where the months would be arranged in the order suggested below (a calendar year). Using the existing application’s Fiscal Calendar, this is not an easy task to be achieved. Therefore, we need to create a new dimension with the Calendar specification below using SSAS.

Using SSAS the following settings had been applied to create the additional time dimension for the budget application:

The figure below shows the relationship configured between time dimension (created using SSAS) and measure Group Tables in the PPS Planning application database.

URL Encoding in Reporting Services

Handling Special Characters Using HttpUtility.URLEncode()

By Dan Meyers

Reporting Services allows you to create hyperlinks in your reports using the Go To URL action. This is a very useful feature that provides you with the ability to make almost anything on the report a hyperlink. When creating links to other reports, this action type provides many advantages over using the Go To Report action type. For example, you can embed some javascript into the link so that the report opens up in a new window that is a specific size in a specific location on the screen. You cannot do this using the Go To Report action type.

Of course there are disadvantages too. When using the Go To URL action type you have to manually handle any special characters in you URL string. This is something that the Go To Report action type handles for you when it comes to the parameter values passed to the report. In my opinion, the advantages outweigh the disadvantages. So I always use the Go To URL action type and manually handle the special characters using the HttpUtility.URLEncode() function.

Before we can use the the HttpUtility.URLEncode() function we have to first add a reference to System.Web assembly.

Go to Report –> Properties –> References. Click [Add].

Scroll down a select ‘System.Web’ in the list and click [OK].

You should now see that the reference has been added.

Now that we have added the reference to System.Web, we now have to create a function in the report’s Code window that uses the URLEncode() function. Click on ‘Code’ in the left panel and enter the code below.

Public Function URLEncode (ByVal inURL As String) As String
Dim outURL As String
outURL = System.Web.HttpUtility.UrlEncode(inURL).ToString
Return outURL
End Function

We can now use our new report function in an expression anywhere in our report.

As you can see in the screenshot below, the function will convert any special charters and encode the URL. In this example, I am passing in a string for a MDX member for Barnes & Noble. You can see the before and after values below.

A Common Mistake
One common mistake that many make when taking this approach is that they try to use the HttpUtility.URLEncode() function directly from an expression. The expression will basically ignore the reference and not work. You have to use the function in the report’s Code window and then reference the report’s function in the expression for it to work.

Query the Members of an AD Group using a Linked Server

Using LDAP (SQL Dialect) Queries from SQL Server

By Dan Meyers

I was recently asked by a client about the best way integrate information from AD (Active Directory Services) into SQL and more specifically into Reporting Services reports. They wanted to be able to grab the userid of the person running the report and do a lookup in AD to see if the user was the member of a specific AD group or not. Based on whether or not the person running the report belonged to certain groups they wanted the report to behave a different ways. Based on the results on the AD query they wanted the reports to show or hide certain sections of the report and manipulate the data returned too.

The simplest and most effective solution for this problem is to use a linked server to query AD directly. Using the ADSI provider and a linked server in SQL Server we can query AD live and check to see if the report user is a member of a specific group or not. LDAP, the directory protocol defines the language that we need to use to query AD for user information. You can use either SQL Dialect or LDAP Dialect. I will be using SQL Dialect in my sample code. There are some known restrictions:

Known Restrictions:
· The number of records returned by the query is limited by a setting on the AD server. I believe the default value is 1000 records and can be changed by an administrator.
· The linked server cannot return multi-value fields such as the ‘memberOf’ field, but nothing restricts you from using them in the WHERE clause.

Since the client wants to do this at the report level we can use the UserID function that is built into Reporting Services to return the userid of the person running the report. This makes it easy for us to pass the userid down as a parameter to a SQL query.

It should be easy to use these examples to create a set of views, stored procedures, and/or functions that you can use to query AD over the linked server and integrate the data from AD into your reports.

Below is some sample code that gives you examples of how to:
· How to get the container/ADsPath information for specific groups
· Wildcard Searches Based on Names
· Searching for Groups and Users
· Using Multi-Value Fields in the WHERE Clause
· Returning the Members of a Group
· Checking to See If a User is the Member of a Group

--Run this to create the linked server
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

--Run this to get the ADsPath for each of the groups that have a names that starts with "BI_"
--Note the use of the "*" as wildcard characters
SELECT ADsPath
FROM OPENQUERY(ADSI,
'SELECT ADsPath
FROM ''LDAP://DC=<<MyDomainName>>,DC=com''
WHERE objectCategory=''group''
    AND CN=''*BI_*''
ORDER BY CN')

--Run this to get information about the "BI_" groups
--Note the use of the "*" as wildcard characters
SELECT sAMAccountName as Login, CN as Name, GivenName as FirstName,SN as LastName, DisplayName as FullName, UserAccountControl
FROM OPENQUERY( ADSI,
'SELECT sAMAccountname,givenname,sn,displayname,useraccountcontrol,CN
FROM ''LDAP://DC=<<MyDomainName>>,DC=COM''
WHERE objectCategory=''group'' AND CN=''*BI_*''
ORDER BY CN')

--Run this to get the information about the members of a specific group
--The value being used to filter on the memberOf field is the ADsPath value returned by the first query above
SELECT sAMAccountName as Login, CN as Name, GivenName as FirstName,SN as LastName, DisplayName as FullName, UserAccountControl
FROM OPENQUERY( ADSI,
'SELECT sAMAccountname,givenname,sn,displayname,useraccountcontrol,CN
FROM ''LDAP:// DC=<<MyDomainName>>,DC=com''
WHERE objectCategory=''person''
   AND objectClass=''user'' 
   AND memberOf=''CN=<<MyGroupName>>,OU=BI,OU=Administrator
Accounts,OU=MIS,OU=Corporate Users,DC=<<MyDomainName>>,DC=com''
ORDER BY CN')

--Run this to see if a specific user is a member of a specific group
--The value being used to filter on the memberOf field is the ADsPath value returned by the first query above
SELECT CONVERT(BIT,COUNT(*)) AS IsMemberOfGroup
FROM OPENQUERY( ADSI,
'SELECT sAMAccountname
FROM ''LDAP:// DC=<<MyDomainName>>,DC=com''
WHERE objectCategory=''person''
   AND objectClass=''user''
   AND memberOf=''CN=<<MyGroupName>>,OU=BI,OU=Administrator
Accounts,OU=MIS,OU=Corporate Users,DC=<<MyDomainName>>,DC=com''
   AND samaccountname=''<<MyUserName>>''')

.

Building Great Cubes: Tip 1

Less is More
By Peter Sprague


I have seen a lot of SSAS 2005 cubes that look very similar despite being created independently by different customers and partners across the US and Canada. They always look something like this:

- 5 or 6 dimensions with few or no natural hierarchies
- 70 or more (often many more...) attribute hierarchies, including the ever popular attribute [Customer Fax]
- 4 or more user defined time hierarchies
- Time attributes that contain all dates between 1901 and 2060
- 35+ measures
- Multiple measure groups
- Little relation to a specific user problem

Why is this a concern? These cubes makes it more difficult for the business user to navigate the data, and learn the tools. The complexity also makes it more difficult to understand the data that is returned. The biggest concern is that the users may misunderstand the data, and then base decisions on that misunderstanding. One of the reasons for this complexity is that analytic and monitoring tools (as opposed to reporting tools) directly surface the cube metadata as part of the user interface (Excel, PerformancePoint, ProClarity). Subsequently, multiple measure groups can be dangerous. Let me be clear... ALL of the features of SSAS 2005 are useful, they just aren't useful all the time.

These problem cubes rarely help users support a series of decisions. Seldom do they help users analyze their data to get to an actionable step.

Let me introduce Vilfredo Pareto, an Italian economist who in 1906 observed that 20% of the population in Italy owned 80% of the country's wealth. He also noted that this ratio held true for other scientific and economic distributions. A hundred years and hundreds of self-help books later, we have the Pareto Principle: 80% of the value comes from 20% of the resources. This principle holds true for cube design with the additional observation that the extra complexity from the 80% of the resources cost far greater than the 20% of the value that those resources provide. The next time you create a cube, strongly consider what design set will result in 80% functionality and stop there. In my experience, these cubes looks similar to this:

- 6 to 10 dimensions all with 1 strong natural hierarchy
- 6 or 7 exposed attribute hierarchies
- 1 time hierarchy that only contains dates relevant to the period the cube data
- All metadata expressed in business friendly terms
- Strong relation to a business problem

Remember our goal, to provide a cube that helps our users understand the data and supports further business action or decision. Consider this the first rule of cube design, Less is More.


.

Analysis Services 2008 Metadata Report Pack

Reporting on Analysis Services Metadata using DMV Schema Rowsets

By Dan Meyers


Analysis Services 2008 Metadata Report Pack Download

I often get asked by clients about the best way to get metadata about the various SQL Server Analysis Services (SSAS) objects on a particular server. As usual, there are a few ways to go about getting this information. Unfortunately for DBAs and report writers, most of them are a headache to implement and often have you writing code rather than queries. In the recent versions of SSAS we have a somewhat straightforward way to accomplish this. SSAS exposes a lot of good information via built-inschema rowsets for OLAP and data mining objects. This means that all you have to do is write some queries and slap the results in a Reporting Services (SSRS) report and you are done.

If you think this sounds too good to be true, you are half correct. Although we finally have a nice simple solutions for reporting on this information, we are left wanting more. Not everything about the objects is exposed in these rowsets. If you want every detail about your objects then the best approaches are: to buy a 3rd party tool, or to put your programming hat on since you will be using AMOMD.NET to get all of the stuff that SSAS rowsets leave out.

In a previous blog post, I discussed a method where you can use a linked server to run MDX queries against your cubes. For these reports, I am going to use the same approach to execute DMX queries against SSAS 2008 using a linked server and the OLE DP provider for Analysis Services to retrieve metadata about our cubes, dimensions, measures, etc... from the rowsets.

This download provides you with a relatively comprehensive set of pre-built reports that you can easily deploy to your Reporting Services 2008 instance.

Before the reports will run, you have to create a linked server in SQL Server that points to your SSAS 2008 instance. Below is the SQL code needed to create a linked server named [SSAS_Metadata].


EXEC master.dbo.sp_addlinkedserver
@server=N'SSAS_METADATA',
@srvproduct=N'MSOLAP',
@provider=N'MSOLAP',
@datasrc=N'localhost'


EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'SSAS_METADATA',
@useself=N'False',
@locallogin=NULL,
@rmtuser=NULL,
@rmtpassword=NULL


The download contains the entire reporting project pre-=configured to use the localhost as the data source for the SQL Server, Reporting Services, and Analysis Services instances.

The project contains the following 26 reports:
- Cube Details
- Cube Dimension Details
- Cube Dimension Relationships
- Cube Dimensions
- Cube KPIs
- Cube Measures
- Cube Perspectives
- Cubes
- Data Source Details
- Data Sources
- Database
- Dimension Attribute Hierarchies
- Dimension Attribute Hierarchy Details
- Dimension Details
- Dimension User Hierarchies
- Dimension User Hierarchy Details
- Dimensions
- KPI Details
- Measure Details
- Mining Model Column Details
- Mining Model Details
- Mining Models
- Mining Structure Column Details
- Mining Structure Details
- Mining Structures
- Perspective Details

The reports in the report pack were designed and modeled after the output provided by my favorite 3rd party documentation tool for SQL Server BI, BI Documenter. All of the reports contain many navigational links in the header as well as click-through navigation of the objects listed in the reports that you can use to drill into child objects for more detail.

As you can see by the names of the reports listed above, the reports touch all of the major objects and should satisfy the majority of the inquiries about them. One report that I find to be quite useful in the Cube Dimension Relationships report. It is an attempt to reproduce the grid on the Dimension Usage tab of the cube designer in BIDS that tells you at what granularity the dimensions relate to the measure groups.





Hopefully this report pack will save you some time and provide you with some good samples that you can use to create your own customized reports that might work better for you.

Empowering the Data Warehouse with External data

By Rick Durham

To build a powerful data warehouse you must include as much relevant data from internal and external sources as possible to optimize the decision processes that managers and “C level” executives are called to make each day.

As an example, retailers have current/historic sales data along with pricing information, but this will only provide partial insight into the determinants that are driving sales. Information such as weather, income tax distribution periods, regional or local population growth, household demography, may also play a key factor in driving sales and must be taken into consideration.

Where do you go to get data that will complement your internal data sources to provide a much richer data warehouse and BI experience?

The government and many other organizations capture and deliver this data and distribute it free or for a nominal fee. Here are some examples:

Weather: Yahoo offers an RSS fed that can be called using an http request as follows:
http://weather.yahooapis.com/forecastrss?p=48161

The parameters to the request are the following:
Parameter, Description, Examples
p, US zip code or Location ID, p=95089 or p=USCA1116
u, Units for temperature (case sensitive), f: Fahrenheit or c: Celsius

The RSS response from this request includes the following information:
· Geographic latitude/longitude
· Weather Conditions (48 distinct codes)
· Temperature (F,C)
· Forecast (Condition, High Temperature, Low Temperature)

Importing this data daily or even hourly using SSIS packages along with Sales data goes a long way in understanding if weather is a factor in why certain items were purchased at a particular time/date and determining longer term sales trends.

Using Demographic Data along with internally generated data can go a long way to enhance the data warehouse. The following are examples of where this data can be obtained:
http://www.geolytics.com/?gclid=CMeliJqrqJ0CFU1M5QodekqHkA

With limited data (address or lat/long information) you can get 60 demographic attributes for that address that include factors such as income, average number of people per home, average age, education…

Likewise another good site for demographic data and data validation is:
http://www.melissadata.com/dqt/index.htm

This site offers validation against address, phone numbers, email and perform name parsing via Web Services calls which can help accelerate the ETL development process, provided you do not have to develop the code and maintain large demographic databases onsite. Additionally, this site offers demographic data on income, media locations, reverse phone and mailing lists.

Finally, the Federal government maintains thousands of databases with data gathered from various agencies that contain information that can be coupled with internal data to make your data warehouse far more powerful. For example:
http://research.stlouisfed.org/fred2/
http://www.data.gov/catalog
http://www.census.gov/

These sites contain historic economic and demographic data the government has collected regarding income, population, interest rates, commodity prices, housing sales… and the downloads are free.

The goal of data warehouse development should be to provide the tools and data for optimal decision making. To assure this goal is achieved, make sure external source are also included in the initial and ongoing data warehouse implementation.