select
substring(account, 0 , 6), substring(account, 6, 3), substring(account, 8,
3)
from lc_dw_accounting_csv

How do i do this in OLAP

I have Account number (11111900200) need split into 3 different columns
account_number = 11111
Item = 900
sub_item = 200

Please help me

Assuming that you're using AS 2005, you can create a named Query in the
Data Source View which returns the 3 fields:

select account,
substring(account, 1, 5) as AccountNumber,
substring(account, 6, 3) as Item,
substring(account, 9, 3) as SubItem
from lc_dw_accounting_csv

Then you can define an Account dimension, with account as the key
attribute, and AccountNumber, Item and SubItem as other attributes. Each
attribute can have its own hierarchy.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***