SSAS with SSRS filtering and sorting MDX results

In SSRS MDX Query builder I cannot find a way to filter results based on Measures. Here's what I mean:
As you can see, you can only select dimensions from the filtering section. Also there is no option for sorting. Heres how you can do it:

FILTERING:
There are two options. One from the query editor. Other from the tablix properties. I prefer the tablix properties (since sorting MUST be defined there and I found no other way to do it). I explain the first option here and the second option along with sorting.

• Filtering using query properties:
  

This screenshot pretty much explains everything.

As it is ... if you press okay it will give the error : "Value is not an integer number". That is easily solved by setting the Expression for Value field as =.3 as shown.


All set. The procedure for setting the filter in tablix (your table) properties is presented next along with sorting :)

SORTING :
Select tablix properties:

Now you can add your sorts and your filters.


I would prefer the query to do this for me (to save SSRS resources and utilize SSAS resources) but I did not find that option (as of SSRS2008).

Enjoy!

BIRT style Document Outline in SSRS

Truth be told....... I love BIRT! It is simply put: AWESOME. But SSRS is wicked cool as well. Anyways... I loved the document outline in BIRT. It allows you to copy charts around from table to table. It was also highly detailed.

The document outline window is hidden in SSRS BI Dev studio by default. But you can enable it via:
View->other windows -> Document outline.


Enjoy!

Login failed for user 'NT AUTHORITY\\NETWORK SERVICE'

I got this error when deploying my WCF + linq to SQL service to IIS. This happens because IIS runs under NT AUTHORITY\\NETWORK SERVICE. There are a couple of solutions here:

• change IIS service to run under another user.
• setup impersonation (complicated but ideal)
  
• grant Network service access to the database ( quick but insecure)
  

Just to get it running on your test server you can use option three as explained below:

• Just open up SSMS
• log into database server
• expand Security -> logins.
• Right click logins and select new login
  
• search login name
  
• type in "network service"
• in server roles check "public" and "sysadmin"
• in user mapping select your database and give it "owner" and "public" rights
• Click OK and your are all set

Enjoy!

Deploying a WCF service to IIS7

If you installed IIS after .net framwork 3.5 WCF HTTP activation will not be turned on. Without that your deployed svc's will not work. You can turn it on from windows components.

Also, I noticed that the publish option for ASP.net websites is not in the properties of the project. It is however present in the right click menu for the project :)

Enjoy!

Vista file search options

I do not like to index my files. It just takes more memory than I like to spare (yes I love harddisk space a LOT).

So here is how I setup my windows vista file search options:

This more closely resembles windows XP but that's the way I like it.

WCF error Catastrophic Failure

My wcf service was constantly timing out. Note that I had a session based service i.e:

• on the contract:

[ServiceContract(SessionMode=SessionMode.Required)]

• on the implementation:

[ServiceBehavior(InstanceContextMode=InstanceContextMode.PerSession)]

Now what was happening was that I put a break point on a function and called it MANY times. It wasn't being triggered after the first couple of times. Figured it out (after mike's tutorial on sessions and instancing). Some got timed out. And because of that the queue got messed up and resulted in the catastrophic failure. Since I dont do anything thread unsafe in general I changed the behavior on the implementation to :

[ServiceBehavior(InstanceContextMode=InstanceContextMode.PerSession,ConcurrencyMode = ConcurrencyMode.Multiple)]


Got the hint from here (read the sessions and streaming section).


Enjoy!

Using a winform (.net) as a fixed size dialog

Heres how just select the dialog and set the following properties:

• MinimizeBox = false
• MaximizeBox =false
• FormBorderStyle = FixedDialog

Enjoy!

Passing LINQ to SQL objects over WCF

I came accross a cool artile that explains just that :

http://blog.lab49.com/archives/1949

I just had to bookmark it.

Its pretty simple. Click the whitespace in your dbml file. Select its properties and set the serialization mode from "None" (the default) to "Unidirectional". Tada... now you can send the objects returned from linq over the wire with complete intellisense and debugging awesomeness of visual studio!

However you cannot serialize DataContext :( There is a workaround though. It involves setting up a timestamp column. So without further ado here it is.

To learn more about serialization I recommend the mike taulty video: Type Serialization

Enjoy!

WCF Discover service option in Add service reference tool

The WCF Discover service option in Add service reference tool only works when you have a web based wcf service (svc extension :) ) in the current solution.

I am refering to visual studio 2008. Googling this with this sentence would have saved me some time.

Enjoy!

WCF First time woes

I remember the first time I used WCF i came across a weird error "The type name ServiceReference1 does not exist in the type Namespace.ClassName". I solved it ... but now someone has posted it. However I would like to mention that this will occur if ANY class in your project has the same name as ANY namespace in your project. Inshort ... keep your class names different from your namespace names.

Anyways. I was googling it due to other reasons. Async callbacks.

In case you cannot generate Async callback from the add service reference tool you need to set your project to output .NET 3.5.

Enjoy!

WCF file transfer

Here a nice article that explains all you need to do :
http://www.codeproject.com/KB/WCF/wcffileserver.aspx

Enjoy!

WCF Video Tutorials

There is a lot of power in WCF. But then again there is nothing TRULY awesome and UNIQUE about the technology that you don't already have with java service achitecture. What you do have is an AMAZING IDE that does most of the work for you and in the most well integrated way that you can imagine. To get up to speed on what I mean reading about it won't help. But looking at it will. Here's a compilation of video tutorials for you (in order of recommendation ..... personal recommendation):

1. http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2006/09/13/8875.aspx
2. http://www.dasblonde.net/2007/06/24/WCFWebcastSeries.aspx|

BTW for some videos by mtaulty you can bing them since the links are expired. They will either be nuggets or screencast. (google did not have those pages indexed.)

Enjoy!

Embedding Google Earth in your .NET application

It used to be that the best integration that you could get with google earth was either:

• make a static KML file: this gave you the opportunity to search your data from the kml)
• make a dynamic KML. This made it possible to get the latest information always.

Well now there's a better way. Embed the google earth plugin. Its been around for sometime but I just saw it now.

Anyways. There is an amazing control that allows you to embed this (browser) plugin into your winform application writter by Fraser .

All about the winform component on google code .

Although for anything advanced I would still go for this control :)

Enjoy!

Winforms keeping a dialog open but hidden

You can keen a windows form open but hidden bringing it into focus on demand as follows:

• Declare it in your main class

RFCManager rfcmanager;

• create it in on form load:

rfcmanager = new RFCManager();
rfcmanager.Hide();

• When you want to show:
  

rfcmanager.Show();

• On the FormClosing Event handler :

this.Hide();
e.Cancel = true;


Enjoy!

Testing Linq

There is an amazing software called linqpad. It can help you test out your linq queries.

Other than that you might still feel the need to show the data returned from your query quickly.

Here's a code snippet that does just that:

public static void ShowInGrid(Object items)
{
Form form = new Form();
DataGrid dg = new DataGrid();
form.Controls.Add(dg);
dg.Dock = DockStyle.Fill;
dg.DataSource = items;
form.ShowDialog();
form.Dispose();

}

use at will :)

Setting up Devexpress ribbon

Heres what I do:

1. After adding the ribbon change the Form to inherit from: DevExpress.XtraBars.Ribbon.RibbonForm
2. Drag and drop defaultlookandfeel component onto the form.
   
3. Set the theme by selecting the defaultlookandfeel control.

Themes are all set. Now setup the icon:

1. Select the form and point icon to an ico file.
2. Select the ribbon and set applicationicon property to a PNG file (preferably 24x24 version of the ico file :) )

Logging parameter changes in a table using triggers in SQL Server similar to Oracle

Here is some code I wrote to make my oracle to SQL Server migration relatively painless.

For the uninitiated on Oracle here's a short description of how an update trigger in oracle looks like:

CREATE OR REPLACE TRIGGER "RFSITE"."CNA_CALLTRIG"
after update on rfsite.cna_cell
for each row
begin
if :old.CBCHD<>:new.CBCHD then
insert into cna_hist
values
(:old.cell_id,'CBCHD',:old.CBCHD,:new.CBCHD,NULL,NULL,NULL,sysdate,NULL,:OLD.BSC);
end if;
end

Notice:

• The main code of the trigger will be looped for each row. (due to portion highlighted in bold)
• for each row :old is a bound variable for all columns of old value.
  
• for each row :new is a bound variable for all columns of new value.
  

See how simple it is to store the history. Well not that easy in SQL Server. Here's how its different:

1. In SQL server trigger is called ONCE will all the old values in deleted and new values in inserted .
2. No option like 'for each row'

So in case you want to loop through variable the same as oracle you need to write a LOT of boiler plate (to my knowledge..... I would LOVE to be proven wrong)

Heres a short description of how to do this:

1. Declare column members as variables.
2. Declare a cursor for select * from inserted inner join deleted on ()
3. loop through cursor pushing the values into your declared variables.
4. Now that you have the variables do what you want with them.

Now that you have the theory down heres a short sample of how to do it.

1. DECLARE
   @RFCIDOLD int,
   @RFCIDNEW int,
   @ProjectOLD varchar(255),
   @ProjectNEW varchar(255) ........etc
   
2. DECLARE Item CURSOR FOR
   
   select
   deleted.RFCID as RFCIDOLD,
   inserted.RFCID as RFCIDNEW,
   deleted.Project as ProjectOLD,
   inserted.Project as ProjectNEW ...... etc
   from
   deleted inner join inserted on
   (
   inserted.RFCID=deleted.RFCID
   )
   
3. Open Item
   fetch Item into
   @RFCIDOLD ,
   @RFCIDNEW ,
   @ProjectOLD ,
   @ProjectNEW .....etc
4. While @@Fetch_Status = 0
   begin
   --You now have the required data in the new and old values same as oracle. Do as you like.
   if @RequestedByOLD <> @RequestedByNEW
   begin
   Print @RequestedByOLD +' Changed to ' +@RequestedByNEW
   end
   
   fetch Item into
   @RFCIDOLD ,
   @RFCIDNEW ,
   @ProjectOLD ,
   @ProjectNEW .... etc

Of course you will need to end the loop close the cursor, deallocate it and end the trigger (more boiler plate).

Anyways. This post is not about explaining the process as much as it is about automating it.
Enter: Trigger maker

Heres how it works:
(sample RFC table ddl is provided in the download)

• Put a file ".txt" in the input folder containing your table DDL (generated from SSMS option to script to a new window)
  
• Put the file ".k" in the input folder continaing your keys seperated by newlines.
• Put the file ".i" containing columns you do not want to audit changes for.
• Put the file ".a" containing the audit code. e.g: 
• Insert into CNAH values (@BSCNEW,@CELL_IDNEW,'%(param)s',@%(param)sOLD,@%(param)sNEW,null,null,@NCELL_IDNEW,null,null,GetDate())
  
• Run process.py
• Your trigger will be present in the output folder as ".txt>"

Note trigger maker does one more cool thing for you. Here's how I like my auditing structure:

• Say you have table
• I like my trigger name to be (tablename)AUDIT

Enjoy!

Creating a shared analysis services datasource in SSRS

Here are the steps:

• Open report manager
• Click on New datasource
• Type in the name
• Type in the Description
• Set Data source type to SQL Server Analysis Services
• Paste in the connection string
• In connect using: Credentials stored securely in the report server. Type in the username with access to SSAS (no need to give fully qualified username if user is on localhost).
• Be SURE to check use windows credentials when connecting to the datasource.

All set. Enjoy!

Getting Yesterday date properly formated in SSRS expressions ready for MDX

Suppose I want yesterdays value for use in MDX from SSRS. It will be something like:

[DPTime].[THEDATE].&[2009-08-22T00:00:00]

Here you can see that I have a Time heirarchy DPTime->THEDATE and in that the member for the time.

Heres how to do it in SSRS expressions. The following expression with take you 7 days back:

="[DPTime].[THEDATE].&["
&
Year(DateAdd("D",-7,DateTime.Now()))
&"-"&
IIf(Month(DateAdd("D",-7,DateTime.Now()))< 10,"0","")
&
Month(DateAdd("D",-7,DateTime.Now()))
&"-"&
IIf(Day( DateAdd("D",-7,DateTime.Now()) )< 10,"0","")
&
Day(DateAdd("D",-7,DateTime.Now()))
&"T00:00:00]"

The above expansion of the expression makes it pretty much self explanatory. But still note the following:

• -7 i.e the number of days to go back occurs 5 times.
• "[DPTime].[THEDATE].&[" is your heirarchy (bound to be different in your case).

Enjoy!

Getting yesterday date properly formated using SSIS expressions

Well In case you want today in the following format:
2009-08-23

Notice the trailing zero before the 8. Heres how:
(DT_WSTR, 4) DATEPART( "yyyy", DATEADD( "dd" , -@DateCounter, GETDATE() ) )
+ "-" +
Right("0"+(DT_WSTR, 2)DATEPART( "mm", DATEADD( "dd" , -@DateCounter, GETDATE() ) ),2)
+"-"+
Right("0"+(DT_WSTR, 2)DATEPART( "dd", DATEADD( "dd" , -@DateCounter, GETDATE() ) ) ,2)
+" 00:00:00"

the hours mins and seconds are non dynamic in this case but I hope you get the drift.

The variable DateCounter will take you back as many days as you like.

A little more one how adding the zero worked:

• DT_WSTR,2 made the "mm" section return X8 where X is null.
  
• "0" + "X8" = "08" . But had it been say "11" then "0"+"11" = "011".
  
• Right (,2) made "08" = "08 but had it been "011" as explained above it would be "11"
  

Enjoy!

SSRS forcing chart column colors

Amazing article:
http://msdn.microsoft.com/en-us/library/aa964128%28SQL.90%29.aspx

setting up chart colors is easy. There are two options.

• For items that exceed a certain threshold kindly review the article.
• Second a custom color series. Read on!
  

Steps:

• Select the chart.
• Goto Properties.
• Set Palette to Custom
• Drop down the property : CustomPaletteColors

You are all set.

Enjoy!

SSRS forcing labels orientation to be vertical and showing all values all

The question is longer than the solution :). Simple. Right click the horizontal axis and select "Axis Properties"

• In "Axis options" set the interval to 1.
  
• In "Labels" select disable autofit and then set rotation to 90.

Enjoy!

Best google maps ASP.net control

I have previously reviewed google maps controls for winforms.

But now I Just reviewed the options for googlemaps in asp.net. There are MANY so I will not go into all of them and just point to what I found to most FREQUENTLY updated and yet feature loaded. (The frequency of update is very important for commercial use of a control. Since two days without your users being able to see their favorite map could be disastrous!)

Here it is : http://googlemap.codeplex.com/

LINQ with WCF

There is something very important I want to say. I LOVE Microsoft. It has made my life as a programmer and DBA so FANTASTIC in so many ways that I just had to say it.

Latest discovery ... LINQ! Known about it since it came out but started using it now :) due to work on other ...cough ... java ... cough ... projects).

Anyways anyone wondering how LINQ can fit into your WCF client / server application.

Well for one:

• Create a statefull service
• Create a DataContext for each session. There is no theoratical limit on the number of DataContexts you can use. They are not the same as your traditional ORM. Infact to cancel a DB transaction you should set your DataContext to null or just forget about it :). The only time a trascation takes place is in the SubmitChanges() type functions which start ... carry out the changes ... and finish the transaction. (of course selects also do transactions but you get the idea)

Enjoy!

How to process SSAS Cubes from SSIS

In case you do not know I LOVE SSAS. It makes sooo many things sooo easy (with great performance J) that it should be a sin for anyone to charge implementing it. But shhhhh don’t tell anyone.

 

Anyways you can refresh Cubes from SSIS using analysis services processing task. Its in the main SSIS panel.

 

One error that you may encounter is that it will fail with some weird error like “x cube failed because primary key y not found” or something. That is because the fact table will contain data not found in the dimension.

 

Here’s a tip about how SSAS works. Dimensions are processed separately (dimension processing) and fact tables are processed separately (cube -> fact table -> partition processing) with only liking to the dimensions.  The error occurs when data present in the fact tables against a dimension is not in the last processed dimension. To avoid this process the cubes AND dimensions. Dimensions are pretty cheap to process since they are only select distincts. Sure you might have data for say day x in Cell level, BSC level, Whatever other level but its still one day x. Get the drift?  

 

So to process the partition you need to process the Dimensions first. But since the “Analysis Services Processing Task” does not order your processing sequence as you would like (no feature to move steps up and down) you will need two Analsysis Services tasks. In sequence refresh dimensions in first and refresh cubes (fact tables / partitions) in second.

 

Enjoy!

Great SSAS for Excel find

I love using Excel 2007 for analysis. But I find it limiting in terms of features. Also I like to know a little about what is going on under the hood.

So, If you want to view MDX that is generated from you modifying various pivot field there is a software that can do just that. Its called PTPower.

I also allows you to add your own calculated members :)

SQL Server profiler simplest settings

Got this off the LINQ videos on ASP site. (BTW cool stuff LINQ!)

• start the sql profiler
• click new trace
• connect to your database
• click on event selection and uncheck all
• still in event selection check show all events and also check show all columns
• Now check SP:StmtCompleted in events.
• Next check the TextData column for this event.
• Click Run

Enjoy!

SSAS Tools shortlisting

I am in the process of shortlisting the best of tools (on basis of features / price) from:

http://ssas-info.com/analysis-services-client-tools-frontend

INTEGRATION CASE

For integration into your own applications I found an AMAZING tool at :

http://www.datadynamics.com/Products/DDA/Features.aspx

They offer .NET components for your use in both ASP and WinForm applications.

A close second (and one I recommend due to price) is XtraPivotGrid. If you get their suite editions there is no beating the price.

http://www.devexpress.com/Products/NET/Controls/WinForms/Pivot_Grid/index.xml

STANDALONE DESKTOP CASE

http://www.softpro.hr/

Their Cube Player is the best MDX editor you can find. For that alone it is worth it!

SQL Server Clustered and Non clustered indicies

Indicies are the best performance gain you can get from a table in any database.

For SQL Server indicies can be classified into clustered and non-clustered. There is a facinating article that explains the difference to be found at:
http://www.sql-server-performance.com/articles/per/index_data_structures_p1.aspx

Here's the crux:
For speeding up single records and minimizing index space use non-clustered.
If select performance (rather than insert performance) is paramount... use clustered idices.

Note: A table can have only one clustered index. It is recommended to keep that index same as the primary key :)

So when adding indicies you will generally be adding non clustered indices. Enjoy !

Disabling USB drives in VISTA

You can do this via a group policy. First copy the data presented in this article from microsoft into a .adm. The data you need to paste is also presented below:

CLASS MACHINE
CATEGORY !!category
CATEGORY !!categoryname
POLICY !!policynameusb
KEYNAME "SYSTEM\CurrentControlSet\Services\USBSTOR"
EXPLAIN !!explaintextusb
PART !!labeltextusb DROPDOWNLIST REQUIRED

VALUENAME "Start"
ITEMLIST
NAME !!Disabled VALUE NUMERIC 3 DEFAULT
NAME !!Enabled VALUE NUMERIC 4
END ITEMLIST
END PART
END POLICY
POLICY !!policynamecd
KEYNAME "SYSTEM\CurrentControlSet\Services\Cdrom"
EXPLAIN !!explaintextcd
PART !!labeltextcd DROPDOWNLIST REQUIRED

VALUENAME "Start"
ITEMLIST
NAME !!Disabled VALUE NUMERIC 1 DEFAULT
NAME !!Enabled VALUE NUMERIC 4
END ITEMLIST
END PART
END POLICY
POLICY !!policynameflpy
KEYNAME "SYSTEM\CurrentControlSet\Services\Flpydisk"
EXPLAIN !!explaintextflpy
PART !!labeltextflpy DROPDOWNLIST REQUIRED

VALUENAME "Start"
ITEMLIST
NAME !!Disabled VALUE NUMERIC 3 DEFAULT
NAME !!Enabled VALUE NUMERIC 4
END ITEMLIST
END PART
END POLICY
POLICY !!policynamels120
KEYNAME "SYSTEM\CurrentControlSet\Services\Sfloppy"
EXPLAIN !!explaintextls120
PART !!labeltextls120 DROPDOWNLIST REQUIRED

VALUENAME "Start"
ITEMLIST
NAME !!Disabled VALUE NUMERIC 3 DEFAULT
NAME !!Enabled VALUE NUMERIC 4
END ITEMLIST
END PART
END POLICY
END CATEGORY
END CATEGORY

[strings]
category="Custom Policy Settings"
categoryname="Restrict Drives"
policynameusb="Disable USB"
policynamecd="Disable CD-ROM"
policynameflpy="Disable Floppy"
policynamels120="Disable High Capacity Floppy"
explaintextusb="Disables the computers USB ports by disabling the usbstor.sys driver"
explaintextcd="Disables the computers CD-ROM Drive by disabling the cdrom.sys driver"
explaintextflpy="Disables the computers Floppy Drive by disabling the flpydisk.sys driver"
explaintextls120="Disables the computers High Capacity Floppy Drive by disabling the sfloppy.sys driver"
labeltextusb="Disable USB Ports"
labeltextcd="Disable CD-ROM Drive"
labeltextflpy="Disable Floppy Drive"
labeltextls120="Disable High Capacity Floppy Drive"
Enabled="Enabled"
Disabled="Disabled"

Run the group policy editor: "gpedit.msc"

Afterwards goto "Computer Configuration>Administrative Templates" since you want to disable it for the computer. Right click "Administrative Templates" and select "add / remove template". Click "Add" and browse to the .adm file you made. After adding the file close the dialog.

A new section "Classic Administrative Templates (ADM)" will appear. Go inside that folder and you will see "Custom Policy Settings>Restrict Drives". In case the Restrict Drives folder is empty you need to do the following: goto "View>Filtering" and uncheck "Only show policy settings that can be fully managed".

You will now see the option to "Disable USB Drives" along with others in the "Restrict Drives" folder. Click on it. Select Enabled and also "Disable USB Ports" to Enabled.

Getting the week on sybase

I wanted to get the week number from sybase ASE. The requirement was simple:

• Monday is the first day of a week ( a week is from mon - sun)
• Even if 1st January was a Sunday, the week 26 Dec - 01 Jan would be called week 1 (and not week 53).
  

This is known as ISO standard 8601.

For sybase this was pretty simple (since the server was configured to use ISO 8601). This pretty much explains itself:

select datepart(cwk, convert(datetime,'2009/01/03',111)) as thisyear
, datepart(cwk, convert(datetime,'2008/12/28',111)) as lastyearlastweeklastday
, datepart(cwk, convert(datetime,'2008/12/29',111)) as thisyearfirstweekfirstday

This returns 1,52,1.

Enjoy:)