NOT IN or IN ?

I have been having a hard time believing my eyes when I noticed that these queries give different results:

1. select * from TableA a where a.a_id not in (select b.a_id from TableB b)

2. select * from TableA a where not exists (select b.a_id from TableB b where a.a_id=b.a_id )

3. select a.a_id, b.a_id from TableA a
left join TableB b
on a.a_id = b.a_id

where b.a_id is null


1. returns nothing whereas 2. and 3. return the same result set

Isn't it that in theory 1., 2. and 3. must give exactly same result?

Why is that 1. is giving different result than 2. and 3.?

As it turns out TableB contained some entries with NULLs in a_id column, so when NOT IN was evaluated, the presence of NULL in the list of returned values broke the logic, and statement was incorrectly interpreted

Googling gave me the reference to a similar post: http://sqlwithmanoj.wordpress.com/2011/02/15/not-in-not-exists-joins-with-null-values/

oh, devious NOT IN, how art thou misleading...

T-SQL formula for %

I have needed a formula that will get me a number if it is <=100 and 100 for anything greater than 100, because clients required that % cannot be greater than 100%

I wanted a formula without IF or CASE statements.

And, here it is!

DECLARE @rr FLOAT

SET @@rr = 120

@rr - 0.5*(ABS(@rr - 100) + (@rr - 100))

PerformancePoint 2010 - connecting Filter to Analytic Chart from different Data Source

I am working on a PerformancePoint 2010 Dashboard which contains filters and Analytic Charts from different Data Sources. I wanted to share one of the challenges and my solution to it, and if possible to discuss any other alternative solutions.

My challenge was to connect an Analytic Chart from one Data Source (OLAP Database A) to a "Period" filter from another (OLAP Database B). I could not use Time Intelligence Filter to solve this, because the calendar control and Time Intelligence functions (Current Month, Previous Month) were not giving us what was needed - we needed just to display a list of Period names in the filter.

To implement this funtionality, Period dimension members had to be translated into Month hierarchy members of a Time dimension when connecting to a Chart by using string manipulation in the MDX query of the Analytic Chart.

Here are the steps to make it work:

1. Drop Period filter and Analytic Chart to the Dashboard

2. In Analytic Chart, create Parameter <<Period>>

3. Create Connection between Filter and Chart with Member Unique Name as a Source Value:





4. Specify <<UniqueName>> in Connection Formula



5. In Analytic Chart MDX Query create a Calculated Member FilterDate

WITH MEMBER FilterDate
AS "[Time].[Date].[Month]." + vba!Right("<<Period>>", vba!Len("<<Period>>") - vba!InStr("<<Period>>",".&["))

this will remove the dimension and hierarchy name sent from Period filter and append the rest to the dimension and hierarchy name of the Time dimension of the cube that is used in the chart

5. in the WHERE condition, put WHERE StrToMember(FilterDate)

This solution will work only for single-selection filter because it replaces the dimension name only once. I only wish REPLACE funciton was available in MDX, then we would be able to implement multiple-selection solution as well.

There is another condition in the cube design to make this work - Dimension members of Periods and Month hierarchies must have same keys, ex: March 2010 has a key "201003", and it must mean the same thing in both dimensions - Period and Time.

My special thanks to Chris Webb for the link to VBA functions in MDX - http://cwebbbi.wordpress.com/2007/03/03/vba-functions-in-mdx

.

PARTITION BY clause works incorrectly when in nested select over large volumes of data

I have been witnessing a very strange behaviour of ROW_NUMBER() PARTITION BY clause in complex join and nested select or CTE statements over large volumes of data with Max Degree of Parallelism <> 1. The ROW_NUMBER() or RANK() were incorrectly marking records within a partiton, and that behaviour was nondeterministic.

Let's say I have three tables girls, shoppingmall and times

girls and shoppingmall have from and to dates, and times table has a row for every date. shoppingmall has shopid which can appear in different mallid.

ex:


Now, I want to get first mallid where the girl was shopping for every day.

I join girls table with times by from and to. Then I create a nested select from shoppingmall also joined to times by from and to, which would use ROW_NUMBER () PARTITION BY in order to give me first mallid for a given date and given shop.


select * into #resultset from girls
left join times ON times.timeid >= girls.datefrom and times.timeid < girls.dateto
left join
(select mallid, shopid as shopidx, times.timeid as timeidx, ROW_NUMBER() OVER (PARTITION BY shopid, times.timeid ORDER BY ordering ASC) Lenin
FROM shoppingmall
INNER JOIN times ON times.timeid between shoppingmall.datefrom and shoppingmall.dateto
) AS trip ON girls.shopid = trip.shopidx AND times.timeid = trip.timeidx and Lenin = 1

Now, what I should get is only one row per date, per shop from shoppingmall

However, if I run this query on a very large dataset ROW_NUMBER() occasionally marks as 1 two rows for same shopid and timeid!

As soon as I add a very restrictive WHERE condition which limits the dataset, ex: WHERE girls.girlid = "Kathy", the query starts behaving correctly (whereas without strict WHERE and on the same record it was marking two rows PARTITIONED BY shopid and timeid as 1)

Also, if I fist insert result of inner select in the temporary table, and then use it in the join - results are correct.


I am running on SQL Server 2008 10.0.2531.0

I will rewrite all my inner selects to use temporary tables instead of nested selects and CTE, but still I do not like the unexpected and unexplainable behaviour of PARTITION BY. I guess that this behaviour is due to parallelism because when I changed the Max Degree of Paralellism to 1 this behaviour disappeared and PARTITION BY started working correctly?

MDX Performance - burdens of IIF

I used to think naively that IIF is an innocent little function that can only do good and help me make a calculated measure evn faster! Wrong! Wrong! Wrong!

I found this very helpful article by Mosha Pasumansky

http://sqlblog.com/blogs/mosha/archive/2007/01/29/performance-of-iif-function-in-mdx.aspx

where he urges MDX developers to avoid using IIF function and showing how bad it could be for the performance.

I have rewritten my calcuilated measures which widely used IIF functions and results in performance gain were very impressive: IIF worsened performance almost in geometric progression:

	with IIF	without IIF
Query 1	5 s	2 s
Query 2	18 s	8 s
Query 3	87 s	25 s

Lesson learned: before writing a calculated member - try to think more about performance...

SSRS axe work: How to use fields from different Datasets in the same field Expression and Grouping in Report Builder 2.0?

Last week I was puzzled by one of my colleagues - he needed help in creating a Report Builder 2.0 SSRS report based on several OLAP cubes utilizing measures from different cubes in the same field in the expression and grouped on the same logically common dimension - Period.

At first I though - impossible. You need a Linked cube for that! But then I got curious and decided to see if we could hash and slash through the report and make it work on scotch tape and pins.

This article helped me get an idea on how to do it:
http://blogs.msdn.com/b/robertbruckner/archive/2008/07/20/using-group-variables-in-reporting-services-2008-for-custom-aggregation.aspx

To achieve this we must make sure that all datasets from these different cubes return same number of rows and grouped by same logical dimension, e.g.: month-year or Period as it was in my case, and are sorted same way - so that we could line them up side-by-side and pretend they are all grouped on same dimension.

Using Custom Code in SSRS create a Collection and push measures from cube A into it. Next - use those values with cube B measures by accessing the Collection with RowNumber("Tablix") as an index

And that is all there is to it.

Now, in detail:

1. Create your first DataSet DataSet1 from cube A with Period at Rows and Actual amt measure, and Tablix Tablix1 with Period as Row Group and Actual amt as a total. Add sorting by Period





2. Create the second dataset - DataSet2 from another cube: cube B with Period at the Rows and Budget_amt measure
3. Make a copy of Tablix1 and paste it as anew tablix Tablix2 . Connect it to the dataset DataSet2
4. Right-click on the blue background outside of the report body, go to Properties->Code
Add following code into Custom Code window:

Dim denominators As System.Collections.Generic.List(Of Decimal) = New System.Collections.Generic.List(Of Decimal)

Function AddDenominator(ByVal newVal As Decimal)
denominators.Add(newVal)
Return newVal
End Function

Function GetDenominator(ByVal row As Integer) As Decimal
Return denominators(row)
End Function

5. Add following expression to the data field of the Tablix2: =Code.AddDenominator(Fields!Budget_amt.Value)




6. Delete the first column in Tablix2 but leave the associated group:





7. glue Tablix2 to Tablix1 by dragging it to the right side of Tablix1



Now, make sure you see and align by these blue lines - both tablixes have to be aligned with these lines.

8. Make a copy of Tablix2 and paste it - you have Tablix3 now. Attach it to the right of Tablix2



9. Go to properties of Tablix3 and change the biding to DataSet1
10. Change Expression in the data field to =Fields!Actual_amt.Value + Code.GetDenominator(RowNumber("Tablix3") - 1)
11. Change the title of the Tablix3 column to Result
12. Change the title of the Tablix2 column to Budget






And here is the result - you have a Result column which uses totals from two different cubes in the expression

Limitations: the column that pushes the totals from cue=be into the stack cannot be set to invisible but it could be as thin as you can get. Just squish it between Actual amt and result if you don't want to show it. Also, the order is important - pushing the value into the stack has to be before getting it from there, i.e. Budget column cannot be placed after Result column.

ShellRunas

ShellRunas - http://technet.microsoft.com/en-us/sysinternals/cc300361.aspx

Discovered a great tool that is very helpful for testing security. It lets you open any program under a different user account just like "Run as... " in Windows.

Ex: I want to test if my security works correctly in Excel for users in specific roles - I would copy my Shellrunas.exe into C:\Program Files (x86)\Microsoft Office\Office12

Then go to command prompt and run

cd C:\Program Files (x86)\Microsoft Office\Office12
C:\Program Files (x86)\Microsoft Office\Office12>shellrunas excel.exe

This tool has helped me a lot, because the native Windows "Run as..." functionality that could be achieved by holding Shift down and then right click didn't work on my Windows Vista for Excel and "Run as..." is not available for SSMS and for IE as well.

This tool helps work around this limitation:


(c) http://technet.microsoft.com/en-us/sysinternals/cc300361.aspx