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.

Formatting Linked Server MDX Query Results Using CTEs

By Dan Meyers

In my previous post I described how to export MDX query results in SSIS without having to use a linked server. Even though a linked server is not required and probably not advised the majority of the time, there might be times when it is actually the best solution to your problem.

One of the biggest headaches with this approach it that the data types of all of the fields returned are either nText or nVarchar. This means that you have to deal with converting and formatting the results before you can use them in a report. There are a couple of ways to go about this.

CTEs (Common Table Expressions) are the most elegant technique to employ here. Plus, they are a bit more readable than their SQL Server 2000 equivalent, Derived Tables. CTEs are only available in SQL Server 2005 and later. If you are using SQL Server 2000 then you have to use a derived table in place of the CTE.

Below is an example of using a CTE to rename and format the results of an MDX query executed against an Analysis Services cube via a linked server:

WITH MDXResults (FiscalYear, FiscalSemester, FiscalQuarter, SalesAmount, GrossProfit)
AS
(
SELECT *
FROM OPENQUERY(SSAS_AW2008,
'SELECT
{[Measures].[Sales Amount], [Measures].[Gross Profit]} ON COLUMNS,
{[Date].[Fiscal].[Fiscal Quarter].Members} ON ROWS
FROM
[Adventure Works]')
)
SELECT
CONVERT(VARCHAR(255),FiscalYear) AS FiscalYear,
CONVERT(VARCHAR(255),FiscalSemester) AS FiscalSemester,
CONVERT(VARCHAR(255),FiscalQuarter) AS FiscalQuarter,
CONVERT(FLOAT,SalesAmount) AS SalesAmount,
CONVERT(FLOAT, GrossProfit) AS GrossProfit
FROM
MDXResults



If you are using SQL Server 2000 then you have to use a derived table instead. Below is an example using the same MDX query that is used in the example above:

SELECT
CONVERT(VARCHAR(255),FiscalYear) AS FiscalYear,
CONVERT(VARCHAR(255),FiscalSemester) AS FiscalSemester,
CONVERT(VARCHAR(255),FiscalQuarter) AS FiscalQuarter,
CONVERT(FLOAT,SalesAmount) AS SalesAmount,
CONVERT(FLOAT, GrossProfit) AS GrossProfit
FROM
(
SELECT *
FROM OPENQUERY(SSAS_AW2008,
'SELECT
{[Measures].[Sales Amount], [Measures].[Gross Profit]} ON COLUMNS,
{[Date].[Fiscal].[Fiscal Quarter].Members} ON ROWS
FROM
[Adventure Works]')
) AS MDXResults (FiscalYear, FiscalSemester, FiscalQuarter, SalesAmount, GrossProfit)

SSAS Association Algorithm and ItemSet Sparcity

Checking Data Size and Parameters while working with a Market Basket Analysis model

By Rick Durham

Recently, I was developing a Market Basket Analysis model at one of my clients, using SQL Server Analysis Services 2005 (SSAS). The reason I we built this model was to understand what items are purchased together so for physical product placement as well as how to position items in advertisements.

After spending several hours on the model and increasing the number of records in the data set to feed it, I was still not seeing many itemsets where the number of itemsets > 2. This perplexed me given the nature of the business and what I knew to be true i.e. literally there should be dozens of itemset > 2.

Size Matters

It turns out that not all data is equal. While many retail organizations have purchasing data whereby a few thousand receipts will yield many itemsets > 2 or more, you might be surprised how often this is not the case. In the Market Basket Analysis I’ve done, I’ve found that many organizations have receipts where only on occasion does the customer purchase more than two items, making the number of itemsets > 2 in the raw data sparse. As a result, if you are working with this type of data versus grocery store data, the size of the data set needs to be 10 – 100 times larger.. In my case, I finally pulled in three million rows of data to produce a meaningful model.

Parameters Matter

Using the correct SSAS algorithm parameters matters. When dealing with data sets where the itemssets > 2 are sparsely distributed, it’s important to set the parameters correctly. In my case, I had to set the Minimum_Probability value from its default of .4 to .2 and adjust the Minimum_Support to a value of .03 from 10 in order to get itemsets > 2 in the model. The reasons I chose these parameters:

- The Minimum_ Support value by default is set to a percentage of the total. Given what I knew regarding the data, I felt that at a minimum we needed ten cases of the itemset to be able to identify itemsets when the itemsets in the data were so sparse. A lower value approaching one would yield too many while going above 10 yielded started limiting the number returned.

- The Minimum_Probability value sets the probability that a rule is true. By adjusting this value lower we are willing to accept that that we may generate rules that have a lower probability of being true. Again this was necessary given how sparse the itemsets were in the data.

Conclusion
Follow your intuition. If while building your data mining model you do not at first get the results you expect, it may be that you do not have enough data, the right kind of data or the incorrect parameters in set. Expect the process to take time because data mining can be a highly iterative process and you cannot look at the raw data to gauge the output of the model.

BUT, the potential benefits of taking time to use the Association Algorithm are extensive. In the Market Basket Analysis I did using this particular model, I discovered several itemset that I would not have predicted would be purchased together.