### Maximum value of a measure from an MDX result set

Below is an MDX Query which returns the sales of different product categories: select {[Dim Category].[Category].[Category]} on 1, {[Measures].[Measure Value]} on 0 from [MY CUBE] result set returned: We can see that the maximum value is 42092.83. Using max and axis function, we can show the maximum value as a seperate measure. So, we add … More Maximum value of a measure from an MDX result set

### Understanding crossjoin, set, tuples with help of SetToStr function

MDX cross join function returns the cross product of two sets. For example, we have two sets A = {1,2} and B = {x,y}. After crossjoin, we get, A X B = {(1,x),(1,y),(2,x),(2,y)}. (1,x), (1,y) are individual tuples. The resultant set consists of these tuples. Now, let’s see the below mdx query which uses crossjoin … More Understanding crossjoin, set, tuples with help of SetToStr function

### Composite Domain Rules (CD Rules) in Data Correction

Hello all I am just learning DQS and would like to share a few things that I learned so far. One of them is the use of composite domains (CD). I will go straight into an example rather than wasting time on definitions. You can find lots of them in MSDN. Let’s say, you have … More Composite Domain Rules (CD Rules) in Data Correction

### Set Synonyms in DQS

This is my first post on the newly added Data Quality Service of MS SQL Server. It’s about setting up synonyms in Domain Values. The steps are : 1. select “domain management” activity for your knowledge base (KB) 2. select a domain from your domain list 3. select the “domain values” tab 4. select all the values … More Set Synonyms in DQS

### Load Excel File Dynamically Into Database Using SQLBulkCopy and GetOleDbSchemaTable in C#

Previously, I have posted some SSIS solutions which would handle dynamic column mapping in data loading. This time, I was wondering how to handle dynamic column mapping when the source is an excel file. I was quick to realize that it would be entirely a C# solution and the program should be able to handle … More Load Excel File Dynamically Into Database Using SQLBulkCopy and GetOleDbSchemaTable in C#

### Access SSIS variables in script task and script component

You have to write codes to access the variables in these two components in a slight different way. For Script Task: DTS.Variables[“VariableName”].Value For Script Component: Variables.VariableName Don’t forget to add these variables in task editor first. 🙂

### Find out anagram using SQL

Any word or phrase that exactly reproduces the letters in another order is an anagram. For example: “Hamlet” is an anagram of the word “Amleth”. We will show u a query today which can determine if two given words are anagram or not. We have used a Tally table for parsing the given strings. It … More Find out anagram using SQL

### Avoid Concurrency problem while running SQL Server Jobs

Hello everyone, This time my problem was to delay the start of a SQL server job if another job is already running. Obviously, the reason for this is that the jobs use the same database resources. The sysjobhistory table in MSDB database has the job ids and their status stored. First, I selected the job … More Avoid Concurrency problem while running SQL Server Jobs

### Update another table using Data Macro in MS Access 2010

Recently, I had to find a way to write a trigger in MS Access 2007. My aim was to update a field in one table (let’s say Project Table) based on Insert/Update/Delete operation on another table (Let’s say Investors table). Quickly, I realized that MS Access 2007 does not offer much when it comes to … More Update another table using Data Macro in MS Access 2010

### Cursor Using Foreach Ado Enumerator in SSIS

Recently, I have been trying to make a cursor using SSIS components and came up with this idea. The trick is explained below: Let’s say, we have a record set and we need to loop through the records to concatenate/append one after another in a sequential manner. For example, you have 6 records: A B … More Cursor Using Foreach Ado Enumerator in SSIS