Re: [sqlite] multidimensional representation

[Kit]

2011/7/6 e-mail mgbg25171 :
>                                1990        1991  year   <= dimension
>
> north         sales        10            8
>                cogs         (5)           (4)
> south        sales         6             5
>                cogs         (2)           (1)
>
> region        line item   <==  dimensions

create table stat(region, item, year, value);
insert into stat values("north","sales",1990,10);
insert into stat values("north","sales",1991,8);
:
insert into stat values("south","cogs",1991,-1);

> I just want to be able to specify different dimensional values and get back
> rectangles of data that I can manipulate. I've already done the manipulation
> stuff so it's just how to layout the data.
> Query examples would be...
>
> total = north + south                             returning 9, 8
select region,sum(value) as total from stat group by region;

> total cogs = north.cogs + south.cogs     returning -7, -5
select year,sum(value) as total from stat where item="cogs" group by year;

> 1990                                                   returning 10,-5,6,-2
select region,item,value as total from stat where year=1990;

> north:1991                                           returning 8,-4
select item,value as total from stat where region="north" and year=1991;

> north:1990..1991                                  returning 10,8
select year,sum(value) as total from stat where region="north" and
item="sales" group by year;

> Once you've created region I don't think you can delete it and all it's
> entries cos that would delete everything i.e. I believe you have to leave at
> least one value in the column whether it be null or north.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multidimensional representation

[e-mail mgbg25171]

Luuk, Roger
Yes it is like a pivot table.
I was stuck on how you delete dimensions you decide you don't want anymore.
After a day thinking about it I think I've cracked it so...
Thanks very much for your assistance.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multidimensional representation

[Luuk]

On 06-07-2011 17:30, e-mail mgbg25171 wrote:
> 19901991  year   <= dimension
> 
> north sales108
> cogs (5)   (4)
> southsales 6 5
> cogs (2)   (1)
> 
> regionline item   <==  dimensions
> 
> I just want to be able to specify different dimensional values and get back
> rectangles of data that I can manipulate. I've already done the manipulation
> stuff so it's just how to layout the data.
> Query examples would be...
> 
> total = north + south returning 9, 8

select data from  where region in ('north','south')

> total cogs = north.cogs + south.cogs returning -7, -5

select data from  where category = 'cogs'
( 'sales', and 'cogs' are in the dimension 'category')

> 1990   returning 10,-5,6,-2

select data from  where year=1990

> north:1991   returning 8,-4

select data from  where region='north' and year=1991

> north:1990..1991  returning 10,8

select data from  where region='north' and year between 1990 an d
1991

> 
> Once you've created region I don't think you can delete it and all it's
> entries cos that would delete everything i.e. I believe you have to leave at
> least one value in the column whether it be null or north.
> 


I looks like pivot table, like Roger calls them
but that is layout, and has nothing much to do with SQL


-- 
Luuk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multidimensional representation

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/06/2011 08:30 AM, e-mail mgbg25171 wrote:
> I just want to be able to specify different dimensional values and get back
> rectangles of data that I can manipulate.

Is what you are trying to do the same thing as pivot tables in spreadsheets?

If so you can Google SQL based solutions, or just use a spreadsheet.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk4UpccACgkQmOOfHg372QThKQCfdRSgXY65rqbbXN7WfUKO05ci
ApUAoJ3Gq7ifuvTk90fs+MndqcWnIp7s
=zmHd
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multidimensional representation

[e-mail mgbg25171]

19901991  year   <= dimension

north sales108
cogs (5)   (4)
southsales 6 5
cogs (2)   (1)

regionline item   <==  dimensions

I just want to be able to specify different dimensional values and get back
rectangles of data that I can manipulate. I've already done the manipulation
stuff so it's just how to layout the data.
Query examples would be...

total = north + south returning 9, 8
total cogs = north.cogs + south.cogs returning -7, -5
1990   returning 10,-5,6,-2
north:1991   returning 8,-4
north:1990..1991  returning 10,8

Once you've created region I don't think you can delete it and all it's
entries cos that would delete everything i.e. I believe you have to leave at
least one value in the column whether it be null or north.



>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multidimensional representation

[e-mail mgbg25171]

Simon only just saw your post so hope my image didn't come through.
It didn't on mine but makes my post pretty meaningless.
I'll try to do it in text
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multidimensional representation

[e-mail mgbg25171]

On 6 July 2011 15:39, e-mail mgbg25171  wrote:

> I've done it in the email body
>



eg total income = north.total + south.total

where north and south are from dimension "region" and
sales, cogs are from dimension "line item"
ie all I need the queries to do is return "rectangles" data cells as a
result of various means of specifying them such as

1 the method above or
2 north.income.sales:1990 --->returns 1
3 north.total > returns 5000,4000

The dimensions will be dynamic so I want to be able to return the data to
it's form before the extra dimensions eg in this case region was added i.e.

Edit...nce you've created an extra dimension it doesn't look like you can
dimension it seems to me that you can't just delete it cos everything would
disappear.
It seems the best you can do is just reduce it down to one value or as you
say null but even then you need one entry.

Hope this helps.
BTW "income" is not a dimension value but just a convenience to
automatically select sales and cogs. I haven't quite worked out how to
represent this yet.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multidimensional representation

[Simon Slavin]

On 6 Jul 2011, at 3:39pm, e-mail mgbg25171 wrote:

> A picture paints 1000 words
> Would it be ok to attach .png files of what I'm trying to do?

Please don't do that to a message on the mailing list.  A picture takes a 
million bits, too.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multidimensional representation

[e-mail mgbg25171]

Thank you for your response
I didn't quite catch what you mean't but would be happy to give examples of
the app.
A picture paints 1000 words
Would it be ok to attach .png files of what I'm trying to do?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multidimensional representation

[Jay A. Kreibich]

On Wed, Jul 06, 2011 at 01:41:13PM +0100, e-mail mgbg25171 scratched on the 
wall:
> The layout where x and y are dimensions eg

> might be represented the following fact table
> xid   yid  data

> If I added another dimension eg yy

> might be represented by this extended fact table
> yy   xid   yid  data

> That's all well and good until you decide you want to DROP the yy dimension.
> How do you best organise table to represent a star schema type arrangement
> so that you can easily INSERT/DELETE dimensions and the new data that
> accompanies them.
> Hope I've made myself clear.

  If you have a reasonable, bound number of dimensions, I would just
  create a column for each dimension.  You can insert NULL for any
  values that don't have a value in a specific dimension.  This also
  gives somewhat fine grain control over your queries, as you can
  search for values with specific values, or with specifically no
  value (e.g. "x IS NULL").

  If you have a more dynamic set of dimensions, then a "star" schema is
  exactly right.  You want to setup attribute tables for the data rows.
  You could have a different table for each dimension, or a single
  attribute table with a (dimension, d_value, data_id) type
  configuration.  In that case, you would have one row per dimension
  per data value.  Regardless, each dimension has a reference back to
  the data row it represents.

  The issue with attribute designs is that they can be very tricky to
  query.  You often find yourself needing to do a "relational divide",
  which is kind of the opposite of a JOIN.  It is one of the core
  Relational operations, but it isn't supported natively by SQL.  There
  are ways of doing it in SQL, but the query syntax can get quite messy.

  I would put together a few example cases of what you're trying to do
  with your application.  In addition to the data layout, pay specific
  attention to the types of queries you need to run and how you're
  going to set those up.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] multidimensional representation

[Luuk]

On 06-07-2011 14:41, e-mail mgbg25171 wrote:
> The layout where x and y are dimensions eg
> 
>  x
>  12
> 
> y  1  | 3   4
> 2  | 5  6
> 
> might be represented the following fact table
> 
> xid   yid  data
> 1  1 3
> 2  1 4
> 1  2 5
> 2  2 6
> 
> If I added another dimension eg yy
> then the following layout
>  x
>  1   2
> yy   y
> 11  3   4
>   2  5   6
> 21  7   8
>   2  9  10
> 
> might be represented by this extended fact table
> yy   xid   yid  data
>  1  1  1 3
>  1  2  1 4
>  1  1  2 5
>  1  2  2 6
> +
>  2  1  1 7
>  2  2  1 8
>  2  1  2 9
>  2  2  2 10
> 
> That's all well and good until you decide you want to DROP the yy dimension.
> How do you best organise table to represent a star schema type arrangement
> so that you can easily INSERT/DELETE dimensions and the new data that
> accompanies them.
> Hope I've made myself clear.

which value of data do you want to keep for different values of yy
dimension?

> yy   xid   yid  data
>  1  1  1 3
and
>  2  1  1 7

when yy is dropped you can choose 3, or 7, or ?

-- 
Luuk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users