Named calculations are basically customized columns (or attributes) that you create in data source views in Analysis Services.  The beauty of the named calculation is that you don't have to make changes to your source tables or views.  Instead you create a SQL statement, such as a case statement, to build an attribute locally within your data source view. 

I've used named calculations to concatenate values in two separate columns and I've also used them to create groupings for some dimensions. 

Here is an example: Let's say that you need to create names for products where the id for the product exists in a dimension table, but the product names do not exist.  For instance, your underlying table or view has a ProductID column (with values 1,2,3) and you want to use a product name instead of an id in your dimension.  What we want to do is create a named calculation called ProductName. 

You simply open your data source view in the 'Business Intelligence Development Studio'; right click on the supporting table or view, then click on 'Add Named Calculation'.  A window opens with two textboxes, one is for the name of your named calculation (Product Name), and the other is where you add your SQL Expression.  You can create the named calculation with the following expression:

CASE ProductID
WHEN 1 THEN 'Product A'
WHEN 2 THEN 'Product B'
WHEN 3 THEN 'Product C'
ELSE 'Unknown'
END

You can now add the ProductName named calculation as an attribute to your dimension in the 'Dimension Designer' (on the 'Dimension Structure' tab).  Click on the name of the named calculation (ProductName) in 'Data Source View' pane and drag it to the 'Attributes' pane.

That's it.  You've now created a customized attribute.  Obviously, you could have added the name of the product to the dimension table or view itself, but this example illustrates what is possible with named calculations.

 

I have to say one of the best tools that I've worked with thus far in Analysis Services 2005 is SQL Server Profiler. 

Although Profiler is not new to SQL Server, it is new to Analysis Services and provides developers and DBAs with tons of useful information.  It is very simple to set up a trace and see which users are connected to AS and what MDX queries they are sending to the instance of AS.

Yesterday, a developer and I were trying to resolve an issue we were having involving Panorama NovaView passing incorrect slicer parameters to AS.  We were able to create a trace and show the MDX that was being passed by Panorama to AS and discovered that AS was indeed getting the incorrect MDX due to an error in a Panorama class library.  This saved a ton of time tracking down where the problem was occurring.

Today I started working with Analysis Management Objects (AMO) which is a library of classes that users work with to create and manage Analysis Services objects.  Although DSO 9.0 (Decision Support Objects) is available in Analysis Services 2005, it is being deprecated and developers are encouraged to use AMO instead of DSO in the future.  So far, I've been highly impressed with AMO.  Hopefully I can get a few scripts out in the next week or so.

I've been developing reporting solutions with Micosoft Analysis Services (originally called MS OLAP Services) since it first shipped with SQL Server 7.0.  For the last two months I've been working on a contract at Microsoft using SQL Server Analysis Services 2005 and must admit I'm highly impressed with the product.

There are many improvements to the newest version of Analysis Services.  From an application interface point of view, I'd have to say that the integration with the Visual Studio 2005 environment was a great move.  A standardized interface for all of the SQL Server components (Database Engine, Analysis Services, Integration Services, Notification Services, and Reporting Services) is pretty nice.  I like being able to run queries against multiple databases as well as cubes within the same window. 

Business Intelligence (BI) is a term used to describe software technologies and the management of data to help decision makers make better decisions.  When people ask me what BI is, I tell them its the process of turning raw data into usable information. Through the use of statistical analysis, forecasting, data-mining, and simple reporting, this usable information translates into knowledge for end users.  BI applications are sometimes referred to as Decision Support or OLAP (On-line Analytical Processing) systems.

I've been designing and developing BI software applications through my own company, dymetrics, inc., for the last six years.  My experience includes the development of data warehouses/marts, multi-dimensional databases, and user interfaces for displaying reports and querying data.

I use both Oracle and SQL Server for data warehouse/mart development, Microsoft SQL Server Analysis Services for OLAP development, and ASP.Net for front-end development.  If you are in need of a consultant to help you with your Business Intelligence intiative, feel free to email me using the link in the navigation bar to the left.