Infobright.org Forums |

converting from row to columns

pauliyang

Posted: 02 November 2009 06:11 PM

[ Ignore ]

Newbie

Total Posts: 2

Joined 2009-11-02

I downloaded the community edition and wanted to try it out on a scenario I have.

I have a Fact table of financial data items with the following fields. there are about 5000 dataitemids in the table. we run statistical analysis on these dataitems.

Datadate,CompanyId, DataItemId, DataItemValue
1/1/2000, 123, 1, 120.1
1/2/2000, 123, 2, 120.2
1/3/2000, 123, 3, 120.3

The problems is that the fact table is in a row format, and what we really want is a column format like below
DataDate, CompanyId, DateItem1,DateItem2,DateItem3,....... DateItem5000
1/1/2000, 123, 120.1, 120.2, 120.3 ..................

If I wanted to stored this table in InfoBright, I would create a table with all the columns like above, and then loaded the data? Do I pivot the data in the fact table and then loaded the data?

thanks

Paul

Profile

Janusz Borkowski

Posted: 03 November 2009 03:56 AM

[ Ignore ] [ # 1 ]

Sr. Member

Total Posts: 648

Joined 2008-08-18

Hi!

Infobright uses mysql as communication front-end and unfortunately mysql does not let table rows to have more than 65535 bytes summarized width and more than ~3300 columns. So, technically the new approach is not feasible.

Also IB dos not have alter table, so adding new columns (= features) would be not possible.

I remember other users on this forum having similar problems, they ended up with the standard fact table structure, which proved to be quite efficient, possibly after partial sorting on the itemId.

Profile

pauliyang

Posted: 03 November 2009 10:27 AM

[ Ignore ] [ # 2 ]

Newbie

Total Posts: 2

Joined 2009-11-02

thanks

So if I kept the row based structure in InfoBright, and I wanted to retrieve several dataitems from the table and transform it to the column format. I would have to use some sort of pivot function to do that in the front end?

Profile

Jakub Wroblewski

Posted: 03 November 2009 11:10 AM

[ Ignore ] [ # 3 ]

Sr. Member

Total Posts: 578

Joined 2008-08-18

Hi,

You may use pivot function in a front end, if it is available. It is also possible to create an SQL query which selects rows as columns (although not all of them, as Janusz wrote), something like:

SELECT tab_col5.value, tab_col9.value, (...) tab_col531.value
FROM
      (SELECT value FROM tab WHERE row_id = 1 AND col_id = 5 FROM tab) tab_col5,
      (SELECT value FROM tab WHERE row_id = 1 AND col_id = 9 FROM tab) tab_col9,
(...)
      (SELECT value FROM tab WHERE row_id = 1 AND col_id = 531 FROM tab) tab_col531; 

But it is not too feasible. Actually, it is something like mixing two different paradigms: a spreadsheet and a relational database one. Try to avoid such operations completely, and just to do what you want to finally do within SQL.

Regards,

Signature