where to keep sql server data type text

View previous topic View next topic Go down

where to keep sql server data type text

Post  itcouple on Thu Jan 26, 2012 5:52 am

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

itcouple

Posts : 45
Join date : 2010-10-13

View user profile

Back to top Go down

Re: where to keep sql server data type text

Post  ngalemmo on Thu Jan 26, 2012 10:11 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: where to keep sql server data type text

Post  BoxesAndLines on Thu Jan 26, 2012 10:33 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

reply

Post  itcouple on Thu Jan 26, 2012 1:00 pm

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

itcouple

Posts : 45
Join date : 2010-10-13

View user profile

Back to top Go down

Re: where to keep sql server data type text

Post  ngalemmo on Thu Jan 26, 2012 3:04 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: where to keep sql server data type text

Post  Jeff Smith on Thu Jan 26, 2012 3:16 pm

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?

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

reply

Post  itcouple on Thu Jan 26, 2012 4:08 pm

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.

itcouple

Posts : 45
Join date : 2010-10-13

View user profile

Back to top Go down

final solution

Post  itcouple on Wed Feb 01, 2012 7:17 am

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

itcouple

Posts : 45
Join date : 2010-10-13

View user profile

Back to top Go down

Re: where to keep sql server data type text

Post  John Simon on Thu Feb 02, 2012 5:41 pm

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


John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

text deprecated

Post  itcouple on Fri Feb 03, 2012 5:17 am

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.

itcouple

Posts : 45
Join date : 2010-10-13

View user profile

Back to top Go down

text deprecated

Post  itcouple on Fri Feb 03, 2012 6:10 am

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

itcouple

Posts : 45
Join date : 2010-10-13

View user profile

Back to top Go down

ssas issue

Post  itcouple on Fri Feb 03, 2012 8:29 am

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"

itcouple

Posts : 45
Join date : 2010-10-13

View user profile

Back to top Go down

Re: where to keep sql server data type text

Post  Mike Honey on Sun Feb 05, 2012 6:37 pm

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
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: where to keep sql server data type text

Post  John Simon on Sun Feb 05, 2012 10:07 pm

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

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

suggestions

Post  itcouple on Mon Feb 06, 2012 9:58 am

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.

itcouple

Posts : 45
Join date : 2010-10-13

View user profile

Back to top Go down

Re: where to keep sql server data type text

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum