where to keep sql server data type text

Hi

I have several fields in a table that are text data type in sql server. Should I keep them in Fact table and build degenerate dimension using unique key from fact table or should I build junk dimension?

The worry with junk dimension I have is performance and I'm not sure if I will be able to do comparisions with text data type. I suspect I could also consider chaning it to varchar(8000) and accept certain loss in data.

Any thoughts?

Regards
Emil

Do not place text or varchar columns in a fact table whenever possible.

The worry with junk dimension I have is performance

Performance of what? Performance of queries that use the dimension or performance of ALL queries? If you bloat the fact table with extraneous big character fields, you degrade the performance of all queries.

itcouple wrote:Hi

... I suspect I could also consider chaning it to varchar(8000) and accept certain loss in data.

Any thoughts?

Regards
Emil

Don't do that.

Hi

Thanks for the comments.

I have question regarnding degenarete dimension. When would you use it? Althought I have read Kimball tips about degenerate, junk and reference dimension. I don't have clear picture of them (when to use degenerate dim).

Coming back to my original question. The amount of data and incremental updates is very small and for non-cube queries I could have a several indexes to satisfy non-text queries, especially that there are few fields in fact table.

The reason why I think about nvarchar is coplications I can get with text. Obviously it is not up to me to decide about it but most data in these fields something like 99.9% is < 8000 characters so having two fields x 8000 does not sound to me like such a bad idea comparing to potential issues with text data type.

Regards
Emil

Degenerate dimensions are usually used to hold business identifiers that do not have their own dimension. For example, an order ID when you don't have an order dimension. They are not used to hold dimensional attributes.

So how should large text "comment" fields be stored in the Star? I assume in the dimension but I have hard time imagining the dimension key being assigned to the fact by joining on the comment field?

Hi Jeff,

It can be joined using surrogateKey using the standard way. Junk dimension would also use surrogatekey. Third option is uniquekey from fact table which is kind of one-to-one but the second table would be used as dimension. I believe that is called reference dimension.

I decided to go with both junk dimension for certain fields and reference dimension for very long attributes.

The data type text is being deprecated on SQL Server, so you shouldn't use it.

What is text data type replaced with? As far as I know varchar max has limit of 8000 characters? My text is above 8000 characters in some instances.

I thought varchar(max) was 8000 limit but it seems it is not. I will use that instead.

FIO

nvarchar(max) seems to be problematic with SSAS. There is a bug and I had to apply workaround (XML code change) to make it work and after using the same workaround on other attributes I reached another limitation "Not enough storage is available to process this command"

Hi itcouple,

As well as the cube engine issues you have bumped into, presentation and use of large text strings is problematic in many OLAP client tools. I normally pass such data through a SQL view where I truncate it (e.g. to 255), and finish it with '...' where necessary.

If you really need to blend it with OLAP data, perhaps SSRS Lookup functions would do the job. Or a Report Action defined for your cube could carry OLAP users to a SSRS Report (e.g. Customer-on-a-page) to show the full text.

Good luck!
Mike

I think Mike's suggestion for an SSAS action to drill to a report is an excellent one.

Hi Thanks for the suggestions, all of them would work. Initially I decided to build dimensions DW (Junk + ref) but also I decided to keep Ref dimension outside of SSAS cube (I'll be removing it due to issues) and use SQL approach to generate those non-analytical reports which require all details.

Thanks for all your suggestion. That is greatly appreciated.