All columns in the table are being designated as Dimensions

#1

I am trying to create a Data Cube from an Excel sheet table which was derived from SQL Server database. Some records have NULL values. When I drop the excel on the 'Create New Data Cube' page, all the columns get designated as Dimensions and there are no Measures!


Why is it so? And how do I correct it?

1 Like
#2

Excel files have a bad habit of having data that looks correct on first glance, but are stored by Excel as a text string and not a number with the appropriate format which is usually how the type is determined. (In excel dates are stored as numbers but entered looking more like a text string and are frequent offenders). You can correct this 2 ways. The first you would have to do every time but is a more general solution for dealing with incorrect types from any data source, the second you will need to do only once, but only works for tabular data sources (Excel, CSV etc).


  1. Use a Data Conversion transform. After the input transform insert a Data Conversion transform and configure it to convert the data types of the fields. If you need to convert a string to a datetime value, then a text field will be shown to input the format your dates are in. Format strings look something like 'dd-MM-yyyy' or 'yyyy-MM-ddTHH:mm:ss.fff'. This page can give you more details on the exact placeholders.
  2. Configure the Structure in the connector. Open/Edit the connector. Click on Data Structures. This will show you a list of structures that were discovered in the Spreadsheet. You'll see one or more tables defined in the list (when autodiscovered, one worksheet equals one table). Click on one of the tables and scroll down to see the columns in this table. Click on one of the offending columns, and you'll see the ability to set the type (with datatime, you can set the format as well). You can also select columns to be primary keys or skipped as well.


However, you said the data came from a Query extracted from your data source. It seems to me that you should just grab that data directly, unless you are prototyping, since you can use a Manual query in the data cube to extract the data (you'll need the Unsafe Transform application privledge) . Since it is coming from the SQL server directly, it will still have all the type information intact. After you've finished with adding any required data prep transforms, you can warehouse the cube to avoid loading down the source database.