InvoiceNumber as a degenerated dimension in AS 2000?

Hello,

We have a couple of cubes in AS 2000 where the users would like to be able to search/view by #Invoicenumber and #POnumber. Instead of creating a separate Invoicedimension I would like to implement it as a 'degerated dimension' - just as a textfield in the facttable.

Some questions: Does AS2000 support this? Are there any special demands on the client tool to be able to support degenerated dimensions? At the moment InvoiceNumber is defined as varchar(16), is that OK?

Looking forward to your responses and advices ....

As far as I can recall this shouldnt be any problem.

Degenerate dimensions are handled just like any other dimension by ssas so there are no problems with client tools using that dimension.

BUT (there is a big but): Unless you are developing your own custom front end for this, the functionality you are looking for will be hard to implement in the front-ends i know of (proclarity, pivottables in excel) because there will typically be a humongous number of invoices. Im guessing that you want users to be able to punch in an invoice number and display invoice lines for that invoice, not show every invoice in a dropdown. My advice would be to look at reporting services to create this kind of report.

|||

I agree with Peter K that Reporting Services is a good platform for detailed low level analysis. On the other hand, if you are required to build this is in AS2000 you simply build a dimension from the invoice number in the fact table. The way you do it is by building fake levels in this dimension like the two first positions for the invoice number, followed by three and four and so on. You can use the TSQL Left-function to do this in the dimension editor for the key and name column.

Regards

Thomas

|||

Thanks Peter and Thomas,

Since we need to stick to AS2000 for the moment I have created a Invoicedimension. This dimension will be huge an flat but hopefully we could migrate to AS2005 within a year!

sql