how to design universe on multiple/identical excel files

[Former Member]

hi guys

I have 4 identical excel files meaning they have same measures and dimensions in them. now when i use UDT to design my universe it brings in each file as a seperate table and i also have to define 4 sets of dimensions and measures one for each ffile/table. i dont want 4 sets of same dim and measures i only want to have one set of dim and measures representing data from all 4 files/tables. how can i do that.

secondly is my approach right is this how i should design my universe. the only reason all the data is spread in to 4 files is because the volume of data is too high and excel has a limit of some 60000 rows.

if this works i will be brininging in 15 years of data for our analysis.

Regards

Adnan

[Former Member · Accepted solution]

Hi Adnan,

It seems like the way IDT is behaving is correct.

You have divided single excel sheet into 4 in order to handle large data.

4 spreadsheets are coming as seperate tables in IDT.

In order to get complete data, you will have to UNION all tables like:

Select * from table_1

UNION

Select * from table_2

and so on

for this, you need equal number of columns in SELECT clause for all tables i.e. equal number of dimensions from all tables.

So, objects from all tables will be required.

Also, in addition to Henry's comment, new format i.e. XLSX can support more than million rows as far as i know.

Hope this will help in some way.

Regards,

Yuvraj

[Former Member · Accepted solution]

Thanks yuvraj. your idea helped. I built a derived table and used Select with "uniion all" statement to merge all the data.

another thing i had to do was use aggregate aware function for the measures in the derived table otherwise i was getting multivalue error.

[Former Member]

If you have SQL Server available, I'd strongly recommending importing the data into a combined table.

[Henry_Banks]

Hi,

just FYI - the old .xls format has the 64K rows limitation, but the new .xlsx format (Office 2010 etc) can store much more.

regards,

H