Posts tagged ‘SSAS’

BIDS Helper 1.5 Is Available!

The new version of BIDS Helper has now been released. If you haven’t used it before, now’s a great time to get started with it. BIDS Helper has been adding much needed functionality functionality to the BIDS environment for over 4 years, with the first release happening back in May, 2007.

I posted last month about the beta – thanks to everyone that downloaded it and provided feedback. The major new features are:

Duplicate Roles

This feature allows you to copy a role with all of the associated settings and permissions. This is implemented as a new menu item on the right click menu for a role.

Biml Package Generator

The Biml Package Generator provides the ability to create packages from Business Intelligence Markup Language (Biml). You can find some tutorials on using this functionality here, and a short introduction video here.

There were also several issues resolved, and other improvements. The Enable/Disable features dialog has been improved to provide additional information and help links. The new version of the SSIS Expression Editor is also included, and there are improvements to the variable move feature, the Expression list, and the Printer Friendly Dimension Usage feature.

I’m pretty excited about this release, since I’ve been working on the Biml language and technology since joining Varigence. I think it’s great that we’re able to provide some of this functionality for free to the community through BIDS Helper. I’m particularly interested in seeing what interesting uses the community comes up with for it. As I mentioned here, I think it will provide a nice way to package up complete, working samples that someone can just copy and paste from a web page, and use locally. On top of that, it will help automate a lot of rote tasks in SSIS, as I’ll continue to highlight in my series on using Biml in BIDS Helper.

We’ve already gotten some great feedback on the new version, along with some suggestions for continued improvements. If there’s functionality you’d like to see added to BIDS Helper, please suggest it at the Issue Tracker page – you can also use that to track new feature suggestions.

Tuning SSAS Processing Performance

Thanks to all those that attended either the webcast of this that I did for the PASS BI virtual chapter, or the presentation at SQLSaturday #74 in Jacksonville this weekend. I really appreciate all the great feedback that I’ve received from the attendees at both events.

I’ve attached the slides from the presentation. I’ve also attached a few definition files, one for a Performance Monitor data collector set, and two trace templates, one for SSAS and one for SQL Server. Feel free to customize these files as necessary for your own tuning efforts. Also, while these trace templates can be used in Profiler, the best thing to do is to generate the scripts from them to run the trace directly on the server – lower overhead and less impact on performance.

The file is located on my SkyDrive.

Developer Gaps

I’ve been meaning to post about this ever since I saw Jamie’s post on “The SQL Developer Gap”. I couldn’t agree more with what Jamie expressed. Prior to getting into serious BI development, I was primarily an application developer. This was at a time when agile development was becoming very popular. As part of that, refactoring, test driven development, continuous integration, and automated unit testing were becoming accepted as good software engineering practices, and tool support was coming along very quickly.

Then I switched over to BI development in the SQL Server 7 time frame. In a lot of ways, it was like going back to the dark ages. No refactoring support, no automated testing, no concept of builds. Nothing significant changed until SQL Server 2005, when tools like SSIS and SSAS took their first steps toward becoming more “developer friendly” by leveraging Visual Studio to easily integrate into source control and the beginnings of multi-developer support. However, there haven’t really been any improvements in this since 2005. Refactoring, automated testing, automated builds, etc., can all be done, but they are painful and time consuming to set up, and require a fair amount of specialized knowledge to do correctly. In addition, these are all skills that the average BI developer usually doesn’t posses.

To join in Jamie’s rant, this is something that has aggravated me increasingly over the last few years. In many ways, BI is ideally suited to an agile approach and developer tools that increase productivity – requirements shift on the whim of the business, you need to deliver quickly and often, and you need easy mechanisms to confirm that what you are delivering provides the correct results. There are many tasks in developing BI solutions that are repetitive and could be easily automated, if only the tools provided better support for it. And developer productivity using the SQL Server BI tools hasn’t seen a significant increase since 2005.

I’m spending a fair amount of my time these days working in Visual Studio, where I have the luxury of a built in unit testing tool, the capability to switch between visual editing and text editing depending on which makes the most sense, the ability to easily do a diff between two versions in source control, a full undo-redo stack, etc. And I get to use add-ins like ReSharper (a fantastic tool that I can’t recommend enough). It really highlights the difference between developing traditional applications and BI applications these days.

That’s part of the reason I joined Varigence, where I have the opportunity to actually help developers deliver BI solutions faster and better. Our approach makes it much easier to support the same features that you see in traditional application development tools. I’ve been pretty pleased to see how easy it is for us to add productivity features to our tools – honestly, it makes me wonder why BI developers had to wait this long for these features to be available in the tools we use on a daily basis.

Presenting at Midlands SQL PASS Chapter

I’ll be presenting at the Columbia, SC PASS user group on July 7. I’ll be speaking on “Getting Started with Analysis Services 2008”, which is a demo heavy introduction to using SQL Server Analysis Services 2008. If you want to use SSAS, but aren’t sure quite where to start, this session should help.

If you are in the Columbia area on July 7, please drop by. You can find more information here:

Formatting Numbers in SSRS (and SSAS)

This is a little off of my usual SSIS posts, but I ran across an interesting item. It’s probably well-known to people who work with format strings on a regular basis. However, I’ve used them for a while, and just ran across this the other day, so I thought I’d share it.

I’ve been working with SSRS recently, using an SSAS cube as a data source. The cube had some large numbers in it, in the 10s of millions (10,000,000). It’s not uncommon to translate these large numbers to display as thousands or millions, by moving the decimal place to the left. This saves space on the report, and can make it easier to interpret when dealing with large numbers.

In the past, I’ve usually divided the number by 1,000 to move the decimal place. However, I found out that you can use format strings to accomplish the same thing a little more gracefully. For example, you might have a format string that looks like $#,### to format the number and an expression on the value of the text box that divides the number by 1,000 (=FIelds!MyColumn.Value / 1000). When setting the format string for the textboxes in Reporting Services, you can put two commas together to tell SSRS to divide the by 1,000, instead of using an expression, like this: $#,,

The same technique can be used in format strings for measures in SSAS. Again, this is probably common knowledge for a lot of people, but it was new for me, so I thought I’d share it here.

BIDSHelper 1.2 Is Out

BIDSHelper 1.2 is now available at CodePlex. The new version has a number of SSIS specific features, like expression and configuration highlighting, a tool window that lists all the expressions in the package, and a Package Smart Diff tool. Darren (one of the other team members) posted about it on his blog. Keep watching, as we are targeting shorter release cycles with more incremental functionality. If you have suggestions for new features, please post them here.

I’m balanced, except when I’m not

Jamie posted a question on ragged hierarchies to his blog today:  Are you balanced or unbalanced? I started thinking about it this evening, and decided to post my thoughts here. I think that, for the most part, business requirements drive should drive the decision between a balanced and unbalanced hierarchy. But there is a gray area. You can often force an unbalanced hierarchy into a balanced hierarchy by filling in the missing levels between the top and bottom. This would typically be implemented by either leaving the intervening levels blank, or copying the parent value down through each intervening level.

For example, say I have the following unbalanced hierarchy, where the DC branch rolls up directly to the North region, and the California (CA) territory has no branches:

Corporate Region Territory Branch
  North   DC
  North MA Boston
  South NC Charlotte
  West CA  

I can implement a balanced hierarchy by filling in the blanks, like this:

Corporate Region Territory Branch
  North North DC
  North MA Boston
  South NC Charlotte
  West CA CA

Sometimes this is perfectly acceptable, but it may not accurately represent the business information. That is a call that has to be made on a case by case basis. However, there are some reasons that balancing the hierarchy may be desirable:

  • Parent-child hierarchies, when used in Analysis Services, only have aggregates created for the key attribute and the All level (or top attribute, if you disable the All level). That has a significant performance impact on large dimensions.
  • If you using Analysis Services, you can still simulate an unbalanced hierarchy by using the Hide Member If property, which allows you to hide a level if it is blank, or contains the same value as its parent.
  • Parent-child table relationships, while being simple to set up at the relational database level, are difficult to query. If you are developing a data mart that end users will be creating queries against, parent-child relationships are likely to cause problems.

In general, I prefer balanced hierarchies, but mostly for technical reasons. There are certainly business cases where they just don’t apply.

BIDS Helper 0.9 Beta is Released

The 0.9 beta of BIDS Helper has been released on CodePlex. It’s got a lot of great features for working with Analysis Services, and some future enhancements will include SSIS.

Retrieving Information from Active Directory with SSIS

I was recently asked to help someone retrieve information from Active Directory for use in SSIS. My initial thought was “This shouldn’t be too difficult. I know there is an OLE DB driver for Active Directory, so it should just be a matter of hooking up a few components.” As it turns out, it took a few hours of painful trial and error to get something working. Also, at the end of it all, I found a different approach that was as simple as I had originally envisioned it. Hopefully, this will save some time for the next person needing to do this.
I’ll start with a summary of the results, in case you don’t feel like wading through the rest. It does work, but there are a few caveats. You can use either an OLE DB connection manager, or an ADO.NET connection manager with the .NET OLE DB wrapper. I recommend the ADO.NET connection manager, as it will allow you to use access Active Directory as a source in your data flows. If you are using the OLE DB connection manager, you will be limited to using Execute SQL tasks and passing the recordset into the data flow. There is a sample package attached that shows both approaches.
I started out using an OLE DB connection manager and choose the OLE DB Provider for Microsoft Directory Services. I put in the server name, left the authentication as Windows Integrated, and clicked Test Connection. The test was successful, so I moved on to the next step.

I attempted to set up an OLE DB data source in my data flow, using my OLE DB connection manager. However, nothing worked. So I backed up, and tried an Execute SQL task. Still nothing. My first thought was that I didn’t have the syntax right for the query, but after trying it successfully outside of SSIS, I was looking at the connection again. Eventually I found a reference in MSDN to setting the ADSI Flag to 1 on the connection properties. After updating that, my Execute SQL task started working.

 The Execute SQL was set up to return the full resultset to an object variable. The query used was “SELECT cn FROM ‘LDAP://DC=DOMAINCONTROLLER,DC=local’ WHERE objectClass=’User’”. This retrieves all the users from Active Directory.

I then went back to using an OLE DB Source in the data flow, but it still wouldn’t work. The OLE DB Source didn’t seem like it was able to execute the query or get any metadata from the provider. So I took the approach of using a script source to process the recordset in the data flow.  (See this post for more information on handling recordsets in scripts.) That worked fine, but I was wasn’t completely satisfied.
I went back and tried creating a new connection manager. This time I used the ADO.NET connection manager, but still used the OLE DB Provider for Microsoft Directory Services through the .NET Provider for OLE DB. Just for grins, I didn’t bother setting the ADSI Flag on this connection.

I then created an ADO.NET Reader data source in my data flow, set the connection to the connection manager I just created and put the same LDAP query in it.

When I ran the updated package, it worked first time through (without the ADSI Flag=1). Since it’s been my understanding that it is always preferable to use the OLE DB connection manager when working with OLE DB providers, this behavior was a little frustrating. It seems like you should get the same behavior whether you are using the OLE DB connection manager or the ADO.NET connection manager with the OLE DB wrapper.
Anyway, that was my journey through SSIS with Active Directory. I’d definitely recommend using the ADO.NET connection manager, as it make the process much easier. Please check out the sample package attached if you have further questions.

Open Source SQL Server Stuff (OSSSS)

I was on the CodePlex ( site today looking for the Analysis Services Stored Procedure project, and found that they have a number of SQL Server related projects available. First, all of the SQL Server samples have been uploaded to the site, which is nice if you only want to grab one or two of them.

There is also the aforementioned AS Stored Procedure project ( , which has some great examples of extending AS functionality with .NET based stored procedures.

Then there is the SQL Inserter project, which can generate INSERT statements from database tables. It’s handy for quickly moving small amounts of data, if you don’t want to use the Import / Export Wizard.

There are also a couple of test data generators ( and Data Generator is designed to be used in .NET based development, and TdGen is for databases.