.NET with BI development tips, tricks and experiences.

The day before yesterday I had my problem with getting my stored proc recognised in the Entity Framework (EF) designer in Visual Studio 2010 (VS2010). Yesterday my next step was to get one of my stored procedures (sprocs) that use Full Text Search (FTS) bound to a complex type. So I could get all the results and aggregations in one round trip to the database. I started with the simplest sproc I could:

CREATE PROCEDURE [dbo].[P_ParentFullTextSearch]
@queryString NVARCHAR(250)
AS
BEGIN
SELECT P.[ID], P.[Name],
(SELECT COUNT(1)
FROM  dbo.[Children] C
WHERE P.[ID] = [C.ParentID]) AS ChildrenCount
FROM   dbo.[Parent] P
WHERE  FREETEXT(P.[Name], @queryString)
AND    P.IsDeleted = 0
END


I granted EXECUTE and CONTROL permissions to the EF connection string SQL Server login on the proc and could get it picked up in the Add Function Import but could not retrieve any column information. Frustrating…

I tried a number of things on the MSDN forums like SET NOCOUNT OFF and adding the VIEW DEFINITION permission. None worked. After running a trace (the first thing I should have done) in the SQL Server Profiler I found clicking Get Column Information was executing:


SET FMTONLY OFF;
SET FMTONLY ON;
EXEC [db_name].[dbo].P_ParentFullTextSearch @queryString=null;
SET FMTONLY OFF;


The problem comes from the fact you CANNOT use nulls in FTS predicates – DOH should have noticed that. This causes the metadata query to fall over. Unfortunately the error you get when running the SQL does not bubble up into the Add Function Import window.

A simple NULL check resolved the issue:

CREATE PROCEDURE [dbo].[P_ParentFullTextSearch]
@queryString NVARCHAR(250)
AS
BEGIN
IF @queryString IS NULL
BEGIN
SELECT    (CAST NULL AS INT) AS [ID],
(CAST NULL AS NVARCHAR(50)) AS [Name],
(CAST NULL AS INT) AS [ChildrenCount]
RETURN
END
SELECT P.[ID], P.[Name],
(SELECT COUNT(1)
FROM dbo.[Children] C
WHERE P.[ID] = [C.ParentID]) AS ChildrenCount
FROM dbo.[Parent] P
WHERE FREETEXT(P.[Name], @queryString)
AND P.IsDeleted = 0
END

Behold! Metadata and a sproc bound to a complex type.

Unfortunately I could not see the stored procedure in the list of items to be added when refreshing the model from the database. I have been implementing a stored procedure/inline-table function to facilitate a Full Text Search (FTS) in SQL Server 2008 using ADO .NET Entity Framework (EF).

The cause was that the EF connection did not have Control/Execute permissions on the function/procedure. Grant these permissions to the user utilised in your EF connection string and it will pop-up in the list of stored procedures to be added when you go back to update the model.

Once that is done you can then map the proedure/function results to your entities or a complex type. This is accomplished by selecting Add function import when right-clicking anywhere in the EDMX Designer View or the stored procedure in the Model View.

A colleague of mine found a handy way to run a single unit test in Visual Studio 2010. Go to Test -> Windows -> Test View, select the test you want to run and right-click. Hit Run Test and away you go.

Always remember to run all your tests though before you commit your changes.

Been busy creating a Silverlight/SSAS mashup recently to show off their potential when used together to both colleagues and clients. While attempting to build the measure selection/filtering interface I thought it would be pleasant if they were grouped by their… measure groups, hierarchically like they are when browsing a cube in SSMS or Visual Studio. Measures are easy enough to find as a collection on the CubeDef object as a MeasureCollection. But where are measure groups hiding?

Naturally I would have thought there would be a MeasureGroup or MeasureGroupCollection off the CubeDef – no such luck. Debugging and taking a closer look at the Measure object, in the PropertyCollection (the Properties member) I spyed the property MEASUREGROUP_NAME. The sweet smell of victory. So to get your measure’s measure group here is the C#:

Property measureGroupProperty = measure.Properties["MEASUREGROUP_NAME"];
string measureGroupName = measureGroupProperty.Value.ToString();


I was recently playing about with hiding the All member for several dimension attributes in my SSAS project. In order to hide the All member we set the IsAggregatable property to False. This immediately had the effect of my queries not returning any results. In hindsight my problem should have been blindingly obvious.

When we no longer aggregate the members on a dimension attribute, SSAS does not know what member/members to use unless you tell it explcitly, or set the DefaultMember attribute. Doing one or the other will stop your measures and calculations from vanishing.

After installing the Office 2010 Beta I continued to develop my SSIS and SSAS projects without interruption. In my spare time (a rare thing) I wanted to keep my web app skills up to date and continue to explore Silverlight and its all-singing, all dancing .NET RIA Web Services. All was well at first. I created a new web application project and started to create my master page and default web form when without cause I was not able to click on the Visual Studio 2008 window. The CPU was idle and I had no other apps open at the time other than Spotify. Tried the ALT+TAB to see if some modal dialog had gone astray but nothing…I was completely locked out.

I scratched my head for a while and tried restarting a few times. The only way I could kill VS2008 was using the Task Manager. The same occured each and every time with a web project. BI projects remained functional. I started to trawl the internet and did not find much but then I stumbled across Martin Hinshelwood’s blog and this article. He hit the nail right on the head.

If you have VS2008 locking you out on web projects after installing Office 2010 your solution awaits you here. Thanks Martin.