An Avanade Blogging Community

That's a common scenario and was a pain in the neck to get results using Analysis Services 2000.

In SSAS 2005 we have.... SEMI ADDITIVE MEASURES: by using this measures we use SUM for all the dimensions but not for the time dimensions (watch out you need to use proper time dimensions!), pretty cool.

Snippet from Projecr Real, technical overview:
(http://www.microsoft.com/technet/prodtechnol/sql/2005/projreal.mspx)

The use of semi-additive measures, as exemplified by the inventory data.  Semi-additive measures were not simple to implement in prior releases and did not always perform well.  Now the system directly supports them, and their use in Project REAL illustrates this.  (For readers not familiar with the term: Semi-additive measures do not sum up over time.  Sales for the three months in a quarter can be added to get the total sales for the quarter; this is an additive measure.  Inventories for the three months cannot be added to get the “total” inventory—it makes no sense.  Inventories at a particular time at different locations can however be added to get a total.  Hence, inventories are the classic example of a semi-additive measure.)

Links:

http://blogs.conchango.com/christianwade/archive/2005/04/07/Semi_2D00_Additive-Measures-in-Analysis-Services-2005.aspx

http://www.datadefractor.com/blog/

Quite common request...

  If you use Excel, you have defined methods to do this, the most elegant is to use XMLSS format, transform an XMLSS file to an XSL and combine it with a DataSet, pretty cool.

  but on Ms Word... mmm.. if you only have to support Office 2007, then you are saved, it has a native XML format, but if you "poor soul" have to support older formats, you can play generating RTF files and stream them to the client (you can convert them to an XSL template and combine them with a DataSet as well), about streaming RTF, here you have an interesting link:

  http://support.microsoft.com/kb/270906/

   HTH

Sometimes when we have to migrate our knowledge from 1.1 to 2.0... we just try to make things as it was before, just because it worked out fine, or we don't had the time to go that inner details, this post belongs to that kind of stuff....

Are you still using hidden columns or width 0 to keep you internal ID's on your grid ? If you are doing it that way, don't do it anymore !!!, just use the 2.0 GridView DataKeys field, you will only have to add this declaration on you GridView (on DataKeyNames enter the name of your ID field in the DB/DataSource):

<asp:GridView ID="grdMyGrid" runat="server"

(...) OnRowCommand="grdMyGrid_RowCommand"

datakeynames="Id"

>

And to access in the rowcommand:

protected void grdDotnetGrid_RowCommand(object sender, GridViewCommandEventArgs e)

Remember you can use composite keys as well !!

More info:

http://weblogs.asp.net/despos/archive/2005/06/30/416783.aspx

Tweaking ASP .net 2.0 Gridview with custom paging and sorting to speed up our application, it's a good idea, but apart from the manual work that you have to do (exciting anyway to grasp full control :-)), take care with sort expression, you may be tempted to pass in this field the column of the SQL table that you want to use... is it a good idea?... NO !, two bullets here:

  - If you work with an n-tier system, suddenly you are breaking up the isolation between the UI Later and the DataLayer by using physical column names of tables.

  - A hacker could insert an injection attack you SQL, because finally you are just passing that string as a concat for the you dynamic SQL stored proc, the evil !!.

  A better approach is to use logical names for the sorting, and map this in your datalayer, never trust user input. A very cool link about this issue:

http://www.dotnetjunkies.com/WebLog/joshuagough/archive/2006/08/29/145416.aspx

HTH

In the last projects that I have been working I need to rely on an SMTP server with some "security policies"... sometimes it was a problems to check if sending e-mails was a problem of my application, or just that the SMTP server sucks...

I have found a cool link that let's you make a quick test (Send an e-mail using a telnet), so you can call the Admin telling that for sure their servcer is not working (EHLO !):

http://msexchangeteam.com/archive/2006/07/14/428324.aspx

  HTH

      Braulio

Hello,

  When you use Reporting Services Dataset designer,you can only go adding dimension members to a given report, that's a risk because adding fields there means that the cube will try to slice and dice by every single column added, What happens if you want to show a member property (an attribute that is only information, e.g. you have Project ID, Project Name would be that kind of attribute), if you try to drag and drop a member property, the designer won't let you do so :-(...

   What to do? Here you have the solution:

1.  There is a well known bug in SQL Server 2005, "912702", you need to install the Service Pack 1 of SQL Server 2005.
2. To install the service pack correctly stop the AS service (dono why but I had to make that in order to install the patch correctly).
3. In reporting, forget to use the nice design mode, switch to query mode, and where it is used the dimension that you want to get the attribute, add your attribute name (just after the DIMENSION PROPERTIES declaration), e.g.:

 SELECT

NON EMPTY { [Measures].[MyMeasure] } ON COLUMNS,

NON EMPTY { ([MyDimension].[MyHierarchy].[MyLevel].ALLMEMBERS) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME,[MyDimension].[MyHierarchy].[MyLevel].[MYMEMBERPROPERTY]
ON ROWS

(...)

   4. Run the query (it should work fine), now go to the report and you have two options:

•  Directly drag and drop the member property into the reports (it's available as a dataset column).
•  Or just select the field (dimension, hierarchy level..) that should contain the attribute and insert the following text into the cell:.

   =Fields!MyDataSetField.Properties("MYMEMBERPROPERTY")

   Run the report,and everything should work fine !!!

   HTH
     Braulio

   On thing to take into consideration MDX editor is not very stable, you will get from time to time a crash on Visual Studio :-(.

Sometimes when you try to create a database diagram on SQL Server 2005, you get a nasty message:

"Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects. "

Well, there are some magic statements to remove this error message:

Select your database in the Management console

EXEC sp_changedbowner 'sa'

EXEC sp_dbcmptlevel 'YOUR_DATABASE_NAME', '90';

go

In the forums they say that this happens when you try to use a SQL Server 2000 database in 2005, you have to change your compatibility level to SQL 90... well not sure if it's true, but at least it works :)

With SSIS is easy to get a recordset in a variable, then you can play with it.. assign it to a loop, iterate through all the rows... BUT if you need to use that recordset in a vb .net script task your nightmare just begins:

• You cannot cast an Recordset into a Dataset, recordset, recordset is old VB6 COM stuff.
• You cannot easily use the Recordset object in the vb script, you have to make special configurations (install a COM DLL, register it.. :-(), not good stuff.

  Solution... take a dataset and fill it with the recordset data (object) using an oldDbAdapter !!

     http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx

   And if you need to do it the other way around, Jack Pham pointed out a good link to convert from DataSet to ADO Recordset:

    http://support.microsoft.com/kb/316337/en-us

   HTH