Search results matching tags 'SSRS' and 'SSAS'

Build-time dependency resolving coming to Entity Framework. Now, how about those BI tools too?

jamiet — Tue, 07 Dec 2010 10:04:00 GMT

Three months ago I wrote a blog post entitled Some thoughts on Visual Studio database references and how they should be used for SQL Server BI where I shared some thoughts on a feature available to database developers in Visual Studio 2010 that I would love to see added to SQL Server Integration Services (SSIS), Analysis Services (SSAS) and Reporting Services (SSRS). In there I said:

Over the past few weeks I have been making heavy use of the Database tools in Visual Studio 2010 and one of the features that has most impressed me has been database references.

Database references allow you to have stored procedures in your database project that refer to objects (tables, views, stored procedures etc…) that exist in other database projects and hence when you build your database project it is able to resolve those references.

It occurred to me that similar functionality would be incredibly useful for SQL Server Integration Services(SSIS), Analysis Services (SSAS) & Reporting Services (SSRS) projects. After all reports, packages and data source views are rife with references to database objects – why shouldn’t we be able to have design-time dependency checking in our BI projects the same way that database and .Net developers do?

In that blog post I shared links to three Connect submissions where I requested this feature be added to SSIS, SSAS & SSRS. In addition I also submitted a request that the feature be extended to .Net projects so that any reference to a database object in a .Net assembly can be resolved at build time. That Connect submission is at [Entity FX] Use database references to constrain the EDM and overnight it received this comment from Microsoft:

We have been working on this feature for a while and and will be available soon

This is really good news - it improves the Microsoft developer ecosystem by ensuring invalid references to database references get caught at build time (ideally as part of a Continuous integration build) rather than run time. [Hopefully it might nip this code-first nonsense in the bud too (Ooo...way to incite flame comments :) ) ]. If you want to see this feature in action then check out a video from Teched Europe last month entitled SQL Server Developer Tools Code-named "Juneau" where it is demo'd by Lance Delano and Tim Laverty.

The point of this blog post though is not just to draw attention to this forthcoming feature for .Net developers, it is to ask you to petition Microsoft to get this feature added to SSIS/SSAS/SSRS too. After all, we already know (from the video above) that the feature is coming to this new code-name Juneau development environment plus we also know that Juneau will be the development environment for SSIS/SSAS/SSRS as well - is it really much of a stretch to expect the BI tools to have access to this great feature too? I don't think so and if you agree with me then I urge you to vote and add a comment to the Connection submissions that are requesting this feature. They are at:

[SSAS] Declare Object Dependancies
[SSRS] Declare Object Dependancies
[SSIS] Declare Object Dependancies (Update, Apparently someone at Microsoft has deemed it necassary to set this to private and I am not able to change it back even though I submitted it. You can still vote on the other two though.)

Let's close that SQL Developer Gap!

@Jamiet

SQL Server v.Next (Denali) : Launch point for BI resources

AaronBertrand — Wed, 01 Dec 2010 01:23:00 GMT

Microsoft's new data platform vision for BI is called BISM - Business Intelligence Semantic Model. While I focus primarily on manageability, availability and database engine features, I'd like to keep tabs on who can help you out with their input on the other aspects I rarely touch - Analysis Services (SSAS), Integration Services (SSIS), and Reporting Services (SSRS). Since I'm not covering these areas in my "What's New in Denali" presentations, I thought it would only be fair to provide a decent launch point So here are some blogs, individual posts and other resources with great information - if you have any other resources, please let me know! Here is what I have come up with so far, in no particular order:

Jen Stirrup has some thoughts on BISM that are definitely worth a read:

http://www.jenstirrup.com/search/label/denali

Chris Webb also talks about BISM and its unification with SSAS:

http://cwebbbi.wordpress.com/2011/05/17/good-news-on-the-future-of-analysis-services/

The AS & PowerPivot team blog also has some information about BISM and SSAS:

http://blogs.msdn.com/b/powerpivot/archive/2011/05/16/analysis-services-vision-amp-roadmap-update.aspx

Rafael Salas talks about a lot of SSIS topics as they relate to Denali:

http://www.rafael-salas.com/search/label/Denali

Robert Bruckner of the Reporting Services team answers many questions about Project "Crescent":

http://blogs.msdn.com/b/sqlrsteamblog/archive/2010/11/09/a-glimpse-at-project-crescent.aspx

Chris Webb lists some great talking points about SSAS, with some excellent comments as well:

http://cwebbbi.wordpress.com/2010/11/11/pass-summit-day-2 /
http://cwebbbi.wordpress.com/2010/11/14/pass-summit-day-2-the-aftermath /

TechNet Wiki has some great stuff on SSIS enhancements in Denali on the new SSIS Catalog ("SSISDB") and usability enhancements:

http://social.technet.microsoft.com/wiki/contents/articles/sql-server-denali-ctp1-integration-services-ssis-catalog-overview.aspx
http://social.technet.microsoft.com/wiki/contents/articles/sql-server-denali-ctp1-integration-services-ssis-usability-enhancements.aspx

Jacob Sebastien has created a "What's New in Denali" resource page that has plenty of BI-related information:

http://beyondrelational.com/whatisnew/sqlserver/denali/

Watching the Denali tag on this site could be useful, even though most of the posts so far do not relate to BI:

http://sqlblog.com/search/SearchResults.aspx?q=denali&o=DateDescending

While during CTP periods, Books Online is rarely in sync with the actual build that is out there, it can still contain quite a bit of information. Here is the starting point for "What's New" in Denali:

http://msdn.microsoft.com/en-us/library/bb500435%28v=SQL.110%29.aspx

And you can download the latest "offline" version of Books Online here:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=a94fd771-9e65-4afa-9c5f-03344c48133f

The official SQL Server "Denali" site - more marketing than tech, but still useful:

http://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspx

And here is MSDN's Denali Resource Center:

http://msdn.microsoft.com/en-us/sqlserver/denali_resource_center.aspx

If you're looking for help, the SQL Server community is a vast and generous resource. Post your question under the appropriate twitter hashtags:

#SQLHelp
#SQLDenali
#SQLServer
#SSAS
#SSIS
#SSRS

And don't forget the TechNet forums:

http://social.technet.microsoft.com/Forums/en-US/category/sqlserverprerelease

Finally, if you'd like to provide feedback, submit suggestions, or file bugs, please use Connect (you'll need a LiveID):

http://connect.microsoft.com/sql

PASS Summit 2010: Epilogue

smisner — Mon, 15 Nov 2010 21:16:00 GMT

Another PASS Summit has come and gone, and a good time was had by all. The song at the opening keynote sums up the experience quite nicely, "Simply the Best." My favorite quote of the week comes from Andy Leonard (blog| twitter) who tweeted on November 9, "#sqlpass is a family reunion. :{>" Oddly enough, Andy is one of the few people that I DIDN'T get to see last week, but not for lack of trying. Sorry, Andy!

As an independent consultant, I don't get to interact regularly with my peers, so I love the reunion aspect of PASS. And now that I've started following people on Twitter, my universe of colleagues has expanded even more, and I was delighted to meet the people behind the avatars.

The joy of PASS is not limited to those of us who have attended for many years. As I was sitting in the airport last Friday, I recognized an attendee who was a first-timer and asked about his experience. He replied that it exceeded expectations. He was so anxious to get back to work to put what he learned into practice. He felt that the value of the knowledge that he is bringing back to the office from PASS was so much greater than the price of the conference. Now that's a ringing endorsement if I ever heard one.

There is so much more that could be said about PASS, and many in the community have already posted their thoughts on Twitter and on their blogs. There has been quite a stir in the business intelligence community this past week about the future of Analysis Services, about which I will comment in a future post. Today, however, I will focus on a few follow-up comments and links to resources related to activities in which I participated last week.

Getting Started In Blogging And Technical Speaking

(Photo courtesy of Brent Ozar)

Who had control of this very interactive session? You'll have to buy the PASS Summit DVD to find out!

I waited patiently for my turn to speak. With a room full of speakers, getting your own turn can be a challenge! I heard a few attendees express concern that they didn't know what to write about, or that someone else has covered the topic. My response to this concern is that everyone has a unique take on a topic, and that's why T-SQL Tuesday is such a great way both to learn and to contribute. T-SQL Tuesday was started by Adam Mechanic (blog | twitter) and gives everyone with an opinion (and who doesn't have one?) or a specific experience to add to the topic. You can see the first invitation to T-SQL Tuesday here to see how it all started and the most recent T-SQL Tuesday posts hosted by Paul Randal (blog | twitter). The best way to keep tabs on who's hosting the next round is to follow the #TSQL2sDay hash tag on Twitter.

Unfortunately, I didn't get to hang out for the entire presentation, because I had to move on to the…

Women in Technology Panel

I was honored to participate as a panelist for this year's Women in Technology luncheon. It was well attended, and I heard so many positive comments after the event from both men and women. I drew inspiration from my fellow panelists as well as the stories shared with me by other women attending PASS this year. You can view a recording of the event here if you're a registered member of PASS (which is free to join).

It's so difficult to say what the right answer is for increasing the numbers of women in technology. The numbers are diminishing at a deplorable rate (as I discussed in a previous post). It seems to me that to foster change we need to start laying the groundwork with our children. By "our children", I mean society in general, not me specifically, although I have tried do my part! On the one hand, I don't recommend forcing children into a career path that they can't embrace enthusiastically. On the other hand, I believe that one reason that girls don't pursue technology as an option is lack of exposure to the possibilities. Lynn Langit (blog | twitter) is a role model for showing kids (not just girls!) how to explore these possibilities through www.teachingkidsprogramming.org. Check it out!

Along these lines, I proposed that maybe - as great a community as PASS is - we should collectively think about what we can do for our kids. Someone tweeted that I suggested we should bring our daughters to SQLSaturday, but actually I wondered aloud if we could do something similar to SQLSaturday that focused on the kids (and not just girls). Maybe we could get some sponsors to help, too?

Demystifying MDX in Reporting Services

In this session, I explained some of the nuances of working with MDX in Reporting Services. I have posted my demo reports here.

In addition, you might want to refer back to some of my recent posts about using dynamic MDX in Reporting Services: Part 1 and Part 2.

If you're not familiar with MDX, I presented Session 07: Intro to MDX for 24 Hours of Pass: Summit Preview which you can view if you have a free PASS membership. MDX is not going away any time soon, contrary to recent rumors, so invest some time learning it if you plan to work with real Analysis Services cubes, which will continue to have their place in the BI stack for several years to come.

Real World Analysis Services Stored Procedures

This topic drew a larger audience than I expected as it's a fairly specialized topic. For years, I never needed to use Analysis Services Stored Procedures (ASSP), avoiding it because folklore said so due to performance hits. However, some things just can't be done any other way and I ran into such things this past year. To date, I haven't found much written about ASSP other than BOL, but you can find some excellent examples to download at CodePlex. Plus I've uploaded the very simple (non-production-ready) C# example that I used in my session demonstration for you to peruse.

What's Next?

So now that I've completed all the training and Webcasts and conference sessions that I've been focused on the last couple of months (with one exception - Delivering Information with Reporting Services, a free Webcast at 12 pm Pacific on Wednesday, November 17), I plan to get back to a more regular blogging schedule. There are certainly plenty of topics on my "to do" list!

Using Dynamic MDX in Reporting Services: Part 2

smisner — Sat, 09 Oct 2010 01:31:00 GMT

In this post, I continue my exploration of approaches to working with dynamic MDX in a Reporting Services dataset when you are using Analysis Services as a data source. I began this series with a look at string conversion functions in Part 1. In Part 1, the dynamic MDX relied on parameters that work as a filter on the query results.

But what if you want to change the structure of the query itself? A parameter won't help with that. Instead, you need to create the query string at run-time. However, the Analysis Services data source in Reporting Services doesn't allow you to use an expression to define the query string. That's okay- I'll just do an end run around that problem. I'll use an OLE DB provider to connect to my cube and then I can build up the query string by using an expression. In this post, I walk you through the process.

The context for this demonstration is a report that allows the user to specify the sets that appear on rows and columns of a matrix and to select one measure. It's a very simple example that focuses on the dataset construction, and doesn't spend as much time on the beautification of the report. Hopefully, it will give you some ideas to leverage for your own reports.

Create a data source

First, I need to create an OLE DB source. In the Type drop-down list, I select "OLE DB" and then I provide a connection string like this:

Provider=MSOLAP.4;Data Source=.;Initial Catalog="Adventure Works DW 2008R2"

The Edit button allows you to use a UI to generate the string if you don't want to remember how to construct it manually.

Parameters

This set of steps is necessary to create the lists from which the user makes the selection. In my very simple example, I have created two parameters - Rows and Columns - and hard-coded possible lists. You can do more interesting things here, of course. Just make sure that the user can't make the same selection for both parameters - whether you enforce that by manually providing the values or by doing something clever with a dynamically generated list based on a query.

I created the Measure parameter with the following values:

Label	Value
Sales Amount	="[Measures].[Sales Amount]"
Order Quantity	="[Measures].[Order Quantity]"
Gross Profit Margin	="[Measures].[Gross Profit Margin]"

I created the Columns parameters like this:

Label	Value
Calendar Year	="[Date].[Calendar Year].[Calendar Year].Members"
Reseller Business Type	="[Reseller].[Business Type].[Business Type].Members"

And the Rows parameters like this:

Label	Value
Product Category	="[Product].[Category].[Category].Members"
Sales Territory Country	="[Sales Territory].[Sales Territory Country].[Sales Territory Country].Members"

Dataset

Although the ultimate goal is to produce a dynamic MDX query, it's actually easier to start the design of the dataset using a static query and then to switch it out later. By using a static query, the fields for the dataset are autogenerated. The less work I have to do, the better.

Now one thing about dynamic MDX in Reporting Services is the need to make sure the number of fields in the dataset are the same each and every time. Therefore I need to structure the query differently than I would if I were to write it for a "normal" Analysis Services client. That is, I need to rewrite a query like this:

select

non empty [Date].[Calendar Year].[Calendar Year].Members on columns,

non empty [Product].[Category].[Category].Members on rows

from [Adventure Works]

where [Measures].[Sales Amount]

which produces a result like this:

to a query that looks like this:

select
[Measures].[Sales Amount] on columns,
non empty
([Product].[Category].[Category].Members,
[Date].[Calendar Year].[Calendar Year].Members)
on rows
from [Adventure Works]

and produces a result like this:

However, while structurally the result set is what I need, the fields generated for the dimensions above will change each time that I run the dynamic query with different specifications for rows and columns. So I need to modify the query one more time like this:

with
member [Measures].[Measure] as [Measures].[Sales Amount]
member [Measures].[RowValue] as [Product].[Category].CurrentMember.Name
member [Measures].[ColumnValue] as [Date].[Calendar Year].CurrentMember.Name
select {[Measures].[Measure], [Measures].[RowValue], [Measures].[ColumnValue]} on columns,
non empty ([Product].[Category].[Category].Members, [Date].[Calendar Year].[Calendar Year].Members) on rows
from [Adventure Works]

To get a result like this:

Now I can reference the generic Measure, RowValue, and ColumnValue in the report layout.

Matrix

I like to test things out before I start introducing more complexity. So my next step is to add a matrix layout to the report design, and put fields into the layout and apply a little formatting as shown below.

One extra step related to formatting is required in my example. I have three possible measures, each of which uses a different format string. If I were displaying detail records, I could use the formatted_value cell property as an extended property in the textbox expression, replacing Fields!Measure.Value with Fields!Measure.FormattedValue. However, the use of a matrix here doesn't work with that approach, so... I need to create a conditional expression to set the Format property correctly:

=Switch(Parameters!Measure.Label = "Sales Amount", "C2", Parameters!Measure.Label = "Order Quantity", "N0",

Parameters!Measure.Label = "Gross Profit Margin", "P2")

Then I preview the report to make sure all is well, which it is.

Query expression - step 1

Now it's time to do the deed - convert the query string to an expression. To do this, I open Dataset Properties and click the expression button (fx) next to the Query box.

The first step is just to enclose the query in double-quotes and prefix with an equal sign and to eliminate all the line feeds in the query. The expression needs to be one long string. If you really must add line feeds to make it easier to read, you can set up the expression like this:

="with"

+ " member [Measures].[Measure] as [Measures].[Sales Amount]"

+ " member [Measures].[RowValue] as [Product].[Category].CurrentMember.Name"

+ " member [Measures].[ColumnValue] as [Date].[Calendar Year].CurrentMember.Name"

+ " select {[Measures].[Measure], [Measures].[RowValue], [Measures].[ColumnValue]} on columns,"

+ " non  empty ([Product].[Category].[Category].Members, [Date].[Calendar Year].[Calendar Year].Members) on rows"

+ " from [Adventure Works]"

Just make sure to allow for a space between words on separate lines. I put it at the beginning of each new line so that I can see it easily. I then preview the report again to make sure that the expression works before I add in the next layer of complexity.

Query expression - step 2

Next I plug in parameter values in the appropriate sections of the query, like this:

="with"
+ " member [Measures].[Measure] as " + Parameters!Measure.Value
+ " member [Measures].[RowValue] as"
+ " " + Split(Parameters!Rows.Value,"]")(0) + "]" + Split(Parameters!Rows.Value,"]")(1)+ "].CurrentMember.Name"
+ " member [Measures].[ColumnValue] as"
+ " " + Split(Parameters!Columns.Value,"]")(0) + "]"
+ Split(Parameters!Columns.Value,"]")(1)+ "].CurrentMember.Name"
+ " select {[Measures].[Measure], [Measures].[RowValue], [Measures].[ColumnValue]} on columns,"
+ " non  empty (" + Parameters!Rows.Value + ", " + Parameters!Columns.Value + ") on rows"
+ " from [Adventure Works]"

Then I preview again. Here's the report with the default parameter settings: products on rows, dates on columns, and sales amount as the measure.

And here's the report with sales territory on rows, business type on columns, and gross profit margin as the measure.

Mission accomplished!

Using Dynamic MDX in Reporting Services: Part 1

smisner — Thu, 07 Oct 2010 16:40:00 GMT

If you're using Analysis Services as a data source for Reporting Services reports, you can build a simple dataset using the graphical query designer, but you'll want to switch to the generic query designer to create the MDX query string manually when you have more advanced requirements. Using the generic query designer, you can:

· Impose greater control over the sets that you want to add to the rows axis by using set functions.

· Add query-scoped named sets to the query in addition to calculated members. (Calculated members can also be added in the graphical query designer, but not named sets.)

· Build dynamic MDX queries.

When would you need a dynamic MDX query? Whenever you want to modify the query based on a condition known only at run-time, typically based on a parameter value. If you're using the graphical query designer, you can auto-generate the report parameter's query for available values by selecting the Parameter checkbox. When the user selects a value during report execution, Reporting Services passes the unique name for the selection to the query and all is well. However, there might be situations when the user selection doesn't come from the cube, so you must find a way to convert the parameter value into a value that will work with the query.

In a series of posts, I will explore the available options for working with dynamic MDX queries in Reporting Services. By dynamic MDX, I mean that the query can be different each time it executes. In this post, I cover the use of StrToMember() and StrToSet() functions in parameters.

A very common scenario is the requirement to pass dates into a query. If you have a date filter for the report, do you really want users to navigate through a list of dates from the cube as shown below?

This list of dates - even if it's arranged hierarchically by month, quarter, and year - is what you get when you build the parameter directly from the date hierarchy in the query designer as shown below.

Wouldn't a more user-friendly experience allow the user to select a data from a calendar control? I can do this by changing the auto-generated report parameter's data type to a Date/Time data type and clear the "Allow multiple values" check box. I must also change the Available Values setting for the parameter to None. I can set the default value to "No default value" to force the user to make a selection, or do something nice like define an expression to set a date, like =Today().

So far, so good. But the problem now is that the date data type returned by the calendar control cannot be used by the MDX query without some intervention. I need to change the Parameter Value mapped to the query parameter in the Dataset Properties to an expression, like this:

="[Date].[Calendar].[Date].[" + Format(CDate(Parameters!DateCalendar.Value), "MMMM d, yyyy") + "]"

The expression that you use to convert a date like 2/1/2008 to a valid unique name in your Date dimension might look different. My example is specific to the Adventure Works 2008 R2 cube, which requires the date member to look like this: [Date].[Calendar].[Date].[February 1, 2008].

That's fine so far, but the result of this expression is a string and the MDX query requires a member or a set. The autogenerated query already makes this change for you fortunately. However, if you're creating your query manually, you should understand what it's doing, especially if you need to make changes to it.

The autogenerated query looks like this before I make changes:

SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS,

NON EMPTY { ([Product].[Category].[Category].ALLMEMBERS ) }

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM ( SELECT ( STRTOSET(@DateCalendar, CONSTRAINED) ) ON COLUMNS

FROM [Adventure Works])

WHERE ( IIF( STRTOSET(@DateCalendar, CONSTRAINED).Count = 1,

STRTOSET(@DateCalendar, CONSTRAINED), [Date].[Calendar].currentmember ) )

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I prefer to simplify the query as shown below - removing the text highlighted in red text above. The function does what it says - changes the string (represented by the parameter @DateCalendar) into a set object. I remove the WHERE clause from the query as the FROM clause adequately restricts the query results to cell values related to the selected date. If I need the dimension properties in the report to display something or if I need the cell properties for report formatting, I'll include only the ones I need, but for this example I have removed them all from the query.

SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS,

NON EMPTY { ([Product].[Category].[Category].ALLMEMBERS ) } ON ROWS

FROM ( SELECT ( STRTOSET(@DateCalendar, CONSTRAINED) ) ON COLUMNS

FROM [Adventure Works])

You could change the StrToSet() function to a StrToMember() function and get the same result. It's not harmful to leave StrToSet() as it is. It just returns a set of one member in this case-the date from the calendar control which is a valid set. The CONSTRAINED flag is used to prevent an injection attack and requires the expression to resolve to a valid member before the query executes.

One challenge that often confounds people working with MDX queries in the generic query designer is the inability to copy and paste the query into Management Studio for testing when parameters are in the query as shown above. The MDX query editor doesn't support parameters. Teo Lachev (blog | twitter) posted some advice for working with parameterized MDX queries in Management Studio which I encourage you to check out.

In my next post, I'll explain how to use the OLE DB for OLAP provider with dynamic MDX to create a dataset.

Some thoughts on Visual Studio database references and how they should be used for SQL Server BI

jamiet — Tue, 14 Sep 2010 12:53:00 GMT

Over the past few weeks I have been making heavy use of the Database tools in Visual Studio 2010 (formerly known as datadude, DBPro, VSTS for Database Professionals or one of a plethora of other names that it has gone by over the past few years) and one of the features that has most impressed me has been database references.

Database references allow you to have stored procedures in your database project that refer to objects (tables, views, stored procedures etc…) that exist in other database projects and hence when you build your database project it is able to resolve those references. Gert Drapers has a useful introduction to them at his blog post Database References; in his words database references allow you to:

represent and resolve 3 and/or 4-part name usage inside a database project. Database references are conceptually the same as assembly references inside a C# or VB.NET projects; they allow you to reference objects from your database project that live inside another namespace (database).

It occurred to me that similar functionality would be incredibly useful for SQL Server Integration Services(SSIS), Analysis Services (SSAS) & Reporting Services (SSRS) projects. After all reports, packages and data source views are rife with references to database objects – why shouldn’t we be able to have design-time dependency checking in our BI projects the same way that database and .Net developers do?

Here are some examples of ways in which this would be useful:

If a SSRS report pulls data from a view, I firstly want to know (at design-time) that that view exists. Secondly, if the view definition changes I want to know that my report will still work.
Similarly for a SSAS cube, if a view referenced in the DSV changes or disappears I want my SSAS project build to fail or succeed accordingly
Similarly for a SSIS Execute SQL Task, if it executes a stored procedure I want to know that the named parameters that I am using actually exist in the stored procedure.

To this end I have submitted three identical suggestions to Connect, one each for SSIS, SSAS & SSRS:

[SSAS] Declare database object dependencies

[SSRS] Declare database object dependencies

[SSIS] Declare database object dependencies

I have said before in my blog post The SQL developer gap that we database and BI developers deserve as much love from Microsoft as our .Net counterparts do and I’m of the opinion that providing design-time dependency checking across the full gamut of BI projects would be a huge step in the right direction. The holy grail is an object dependency graph from database tables all the way up through database views, database functions, stored procedures, DAL, ORM layer & business logic layer all the way through to presentation – I don’t know how long it will take but we will get there one day I am sure, hopefully sooner rather than later!

@JamieT

UPDATE: This feature (i.e. the ability to define dependencies on database objects at build time) is coming to the Entity Framework as confirmed in this Connect submission: http://bit.ly/igmEiS. Let's hope the same comes to SSIS/SSAS/SSRS soon.

It's Official - SQLSaturday is Coming to NYC!

Mike C — Sat, 06 Feb 2010 19:59:00 GMT

New Jersey SQL Server User Group (NJSQL) is bringing SQLSaturday #39 to NYC on April 24, 2010! The free all-day training event will be hosted by Microsoft at their Midtown Manhattan offices. The speaker line-up is growing fast—if you'd like to present, visit the event's open call for speakers. This is a free full-day training event, but registration is required to attend. Seating is limited.

Registration, speaker, and sponsorship details are posted at http://www.sqlsaturday.com/39/eventhome.aspx.

The SQL developer gap (Warning: rant coming up)

jamiet — Tue, 12 Jan 2010 23:43:21 GMT

I recently submitted a bug to Microsoft Connect concerning the behaviour of the expression language in SSRS, if you’re interested you can read about it here: DateAdd() doesn't throw an error when it has invalid parameters.

I got a swift and detailed reply (for which I am grateful) from a fellow on the SSRS team however the content of it wasn’t exactly music to my ears:

While I do agree it would be desirable to have an error here, the SSRS engine makes use of the VB code engine for functions such as this. So, the call to DateAdd is just a passthrough as far as SSRS is concerned. The majority of the functions available in SSRS are actually just VB ones.

In other words the inequities of SSRS’s expression language are down to the frailties of a 14 year old scripting language that should have been thrown out long ago on the coattails of Crystal Reports. Am I the only SQL Server BI developer that feels a little … oh I don’t know … unloved sometimes by the various BI teams in SQL Server land. Let’s have a look at some of the things that we have to put up with:

two different expression languages (one for SSIS, one for SSRS), neither of which support comments as far as I am aware, neither of which have any debugging features and neither of which are extensible
an expression language in SSRS that is based on a dead scripting language and rarely produces meaningful errors
an expression editor in SSIS for which there isn’t even context-sensitive help let alone intellisense
an MDX query editor in SSMS that doesn’t have intellisense
a script task editor in SSIS that shows read-only system variables in the ReadWriteVariables dropdown
I could go on…

None of these issues are particularly prohibitive on their own but when you add them all up (along with many others) I’ve no doubt that we could all be a heck of a lot more productive were they all solved.

I look around at our cousins in .Net land and I see their fancy WPF code editors, extensible languages, Javascript Intellisense, code navigation shortcuts, mockable code, code style checkers, MEF-compliant IDEs, type-inferencing, 3rd-party code editors, keyboard shortcuts, modern type-safe compilers, code generation templates, meaningful error messages, proper debuggers, runtime watches, collapsable code regions, IDEs with built-in refactoring support, built-in code formatting, code documentation tools, whitespace highlighting, code performance analysis tools, LINQ, fluent this-that-and-the-other etc… and I can’t help but feeling a little hard done by. (Anyone that has used Resharper for .Net will know exactly what I mean.) Am I the only SQL guy that feels like a second class citizen in the Microsoft developer ecosystem?

I must be fair and admit that we have it a lot better than other database platform developers (if you have ever used any of the plethora of Oracle developer tools out there you will know what I mean) but nonetheless it would be nice to see a little bit of love go the way of us lowly SQL bods don’t you think?

OK, rant over. My apologies if you came here hoping for some words of wisdom but I’ve been wanting to get this off my chest for ages; normal service will be resumed forthwith!

@Jamiet

P.S. A valid response to this from the SQL Server team may be “submit your ideas to Connect”. I’d like it to be known that the majority of things I have listed above have already been submitted to Connect in some way shape or form and I would be happy to supply a list if one were required!

I should also apologise to the SSRS team for making an example of them – the submission highlighted above is hardly the most annoying of problems listed here but it just happened to be the one that pushed me over the tipping point to writing this!

Start / Stop SQL Server, SSIS, SSAS, SSRS and SQL Server Agent at once with a batch file

jorg — Tue, 06 Jan 2009 21:50:00 GMT

A lot of developers have SQL Server and its different services running on their PC or notebook to develop or test BI solutions. Unfortunately this slows down your system quite a lot. To speed things up when not using SQL Server, I used to stop and start each service manual quite often.

Recently I found out that it's possible to start and stop all services at once with a simple batch file. It now only takes a couple of seconds instead of a few minutes and some annoying steps.

Copy/paste the following in a .txt file and rename it to .bat to make it a batch file, execute it by double clicking the file.

START SCRIPT:

NET START "SQL Server Agent (MsSqlServer)"
NET START "MsSqlServer"
NET START "MsSqlServerOlapService"
NET START "ReportServer"
NET START "SQL Server Integration Services"

STOP SCRIPT:

NET STOP "SQL Server Agent (MsSqlServer)"
NET STOP "MsSqlServer"
NET STOP "MsSqlServerOlapService"
NET STOP "ReportServer"
NET STOP "SQL Server Integration Services"