I’m confused about something really basic about BOBJ.
I don’t know what a ‘row’ is.
For example, when I read that my limit on rows retrieved for a query is 1,000,000 rows (or whatever) – what is that statement assuming about how my data is organized? Same question about “row level” and “column level” security.
All I see are reports I create in webi (i.e. I don’t have designer access…)- I can organize those reports to put what I want on a column versus a row.
For example, lets say I have a report with 100,000 products, 50 states, and a sales measure. Now if I put the products on the rows and nest states within them, I wind up with 5,000,000 rows on my report. But if I drag the states to the columns I have 100,000 rows.
Surely when when I have a 1,000,000 row limit that figure isn’t dependent on how I structure my report. A row must have some other meaning in the universe, or in the source database. Maybe all dimensions are considered rows, and measures columns? What about details?
As I said - there is something really basic I’m missing in the terminology / jargon here.
Thank you Marek, but let me try and phrase my question a different way. Say I run two Webi reports. (see below example)
In the first, I get 8 rows of data. Now I drag the year dimension to the column, like in the 2nd example. Now I have only 4 rows.
Same data, half the rows. Now my common sense tells me that it can’t work this way – I’m bringing back a fixed number of rows no matter how I decide to look at it.
When people refer to the number of rows a query brings back, there is some implicit assumption on what a row is. My guess would be it always looks something like the first example, or more generically:
Dimension 1, Dimension 2, Dimension 3, Measure 1, Measure 2
I don’t know whether what that row structure looks like is universal, or whether some other factors(s) influence it like the syntax of the SQL generated, the structure of the universe, the structure of the underlying database, etc. Hope maybe my question is more clear now.
Any help in much appreciated!
p.s. the reason I’m asking is so I can estimate the rows that will be returned for a query. If I know the number of items in each dimension, and the filters used, I should be able to estimate the number of rows I’ll get back for a given query.
What is expressed in your reports are not rows of data returned by queries.
Your reports are a collection of objects, calculations, variables, etc. formatted as desired and populated with data returned by the query.
You may have 8 lines in the report that it has taken 1.2 million rows of data to populate. If you are bringing back so many rows, though, it is likely that no aggregate functions have been built into your universe.
What you did is turn a reguar table into a crosstab/pivot table, thereby reducung the perceived vertical length of the table.
Still in the background Webi is operating on a flat table (not a crosstab), which consists of 8 rows.
Imagine you were to transform your data into a chart, you would have no individual table lines visible, still your chart is based on 8 rows in your example.
Your query editor and thereby the generated SQL decides on how many rows are returned, the presentation of data as: table, crosstab, pie chart or line chart e.g. is just the icing on top of it.
In short:
When you create a query in a WebI report (or a data provider in a DeskI report) then an SQL or MDX statement is generated and sent to a database. The database returns requested data in a form of a raw data - a table with rows and columns. The number of rows in the raw data is what you are asking about.
What you then later do with the raw data in the WebI/DeskI report is up to you. You can aggregate it, filter it, turn horizontal table to a vertical table, change it to a cross-tab, to a chart, etc. etc. All of this is done based on the raw data that the WebI/DeskI report keeps internally in a structure called a microcube. The data in a microcube does not change unless you refresh a report. However, you can do whatever tricks you can/need with the raw data on the report level to display required information.
(BOB member since 2003-11-27)
(BOB member since 2002-06-20)