Category Archives: Business Intelligence

SSRS 2008 R2 Permissions

I spent a couple of hours this morning trying to figure out the permission with one of our SSRS 2008 R2 servers. Here is how the problem started, and the steps I took to fix the issue. When I RDP to the server, I can open the Reporting Services “Site Settings” and view the reports that I have published. However, when I am on my local computer, there was a permission issue which said the following:

“does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed”

I was wondering what the problem was because I was already added to the “System Administrator” role in the site setting. That did not resolve the problem. After looking at the permission in the ReportServer Database, I added myself to RSExecRole. That still did not fix the problem. Finally it came down to Adding myself to the root level Folder Permission.

SSRS 2008 Permission

Data Mining 101

I am taking a class in data mining and learned a few things I wanted to share. As you might know, data mining is a process of finding previously unknown, non-trivial patterns from high volumes of data. It is highly used in industry where actionable results are required to make business decision. I will start with two different process of data mining.

Unsupervised Data Mining: There is no target variable. One of the examples is Association Analysis (AA), which is also called Market Basket Analysis. Association analysis basically tells you what things occur together.

Supervised Data Mining: In this technique, target variable is present. For instance: neural network and decision tree. Decision tree has a target variable set, and it helps us understand the path is takes to reach to the leaf from the root note.

SSIS Lookup Transformation

I encountered a weird problem with SSIS lookup transformation a few weeks back and today when working on a DW project. Lookup transformation, when run in full cache mode, caches the results during the pre-execution of the data flow into the memory. I prefer to use this method because Merge Join needs a sorted data set. The cost of sorting, whether we do it on SQL or SSIS, is expensive.

So, I was trying to join on smart key we were using on one of our legacy systems. This transformation did a looking for AccountID from the payment table, using the smart key (which is AlternateAccountID), and put the values in the payment table (child table). I found more than 1000 records for which I could not get any match. Before mapping this to the unknown dimension (-1), I decided to poke around and see why such a large result set would be present.

Ran a query against this orphaned data and the base table to see that many results are matching. It immediately hit my mind that SSIS lookup transformation, when run in Full Cache Mode is case sensitive, unlike SQL Statements. After looking at the speed of Partial Cache and No Cache Mode, I decided to change the smart keys to UPPER(smartkeys) in T-SQL. Presto! This seemed to bring down the problem to 200 orphaned records.

To sum it all up, SSIS Lookup Transformation when run in Full Cache Mode is case sensitive.

Non-Trivial Recommendation Systems

Collaborative Filtering, recommendation systems, intelligent systems, etc are very fascinating to me. During my free time, I read about these systems and look for ways how I can utilize this to build great products. I have been following recommendation systems build by Amazon and other companies for a long time. Recently, I had to work on one of these projects where I had to generate item-to-item recommendation system based off of statistics collected from IIS logs. As this is still a proprietary product, I cannot discuss codes or process used to generate this. But the application was heavily influenced by Slope One algorithm and implemented via T-SQL and SSIS. If you are looking forward to build something of this nature, http://en.wikipedia.org/wiki/Slope_One and the corresponding links might be a good place to start with.