SQL Server Performance Forum – Threads Archive
Creating cubes using .csv as data source
Hi, I am interested in creating cubes directly from .csv files, instead of from databases (as I’d need to DTS the data into DBs then create cubes, so I am trying to bypass DTS). I have created a DSN using Microsoft Text Driver; specified a directory as the database, and defined the format for the files (column name header box is checked, and the ‘guess’ function is correctly determining the column names, datatypes and lengths etc). In MS SQL Server 2000 (Developer) Analysis Services, I can set this MS Text Driver as the data source, and the connection is established successfully. When I choose to create a cube, I can (using the wizard) select the data source, and the .csv file as the fact table, but my problem is when I select the fact table, a message pops up saying "Unable to retrieve the list of columns for the ‘file.csv’ table.", and another saying "Cannot get the list columns from the ‘file.csv’ table in the ‘datasource name’ data source." Clicking OK gets rid of these messages, and I can now select ‘Browse Data’. Doing this opens up the data exactly as it should: three columns of data with the column names as specified in my .csv file. Since no columns were derived from the fact table however, I cannot select any from the right-hand pane, and so cannot continue with creating the cube.. Any help with the solution to this problem would be greatly appreciated! jonathancoxFor those who are interested I have discovered the source of my problem: When (in SQL Enterprise Manager) I used DTS to import data from a text file to the DB, the datatype is set automatically to ‘varchar’. This works fine in the database, and the table is completed normally. However, when (in Analysis Services) I attempted to create a cube using this table as the fact table, the error, "The selected table lacks numerical columns" arose; you can’t have non-numeric data in a cube. So, to fix the problem, I had to set the data types in SQL Enterprise Manager (I have three columns of data, of types [datetime, varchar, float]) to the proper types, and the cube can now be created. This shows that when using the MS Text Driver to create cubes directly from a text file source, the cube couldn’t create the columns because the types were non-numeric. So, now my problem is: How do I set the data in the file to a certain type? That is, how do I set the data as e.g. ‘datetime’ instead of having it set to ‘varchar’? FYI here is the format of my flat file: "TIME","NAME","VALUE"
2002-10-25 13:00:00,"UNIT_347",76261.8816104
2002-10-25 10:00:00,"UNIT_24_TEMP",2.6473792595100001
2002-10-25 10:00:00,"T5_PRESSURE",0
2002-10-25 10:00:00,"UNIT_45_PRESSURE",0
2002-10-25 10:00:00,"T4_SPRAY-FLOW",9.5871666666700008
… Thanks! jonathancox
]]>