Categories
- All Categories
- 161 Oracle Analytics News
- 28 Oracle Analytics Videos
- 14.8K Oracle Analytics Forums
- 5.7K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 56 Oracle Analytics Trainings
- 12 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 2 Oracle Analytics Industry
- Find Partners
- For Partners
Pivot results
Hello all,
When I try Pivoting the result of one of my queries, OBIEE gives me an error saying there are too many variables. Is there a way to pivot the data i'm querying so it looks something like the Table#2.
The result of this query brings more than 30,000 rows as a result of more than 500 sales resources / 30 different products / 3 MDC... the revenue results are by week.
I tried using SQL but I couldn't make it work.
Table#1
| Sales Resource | Product | MDC | Revenue |
|---|---|---|---|
| SR 1 | Product 1 | LY | 120 |
| SR 1 | Product 1 | TY | 122 |
| SR 1 | Product 1 | PLAN | 130 |
| SR 1 | Product 2 | LY | 150 |
| SR 1 | Product 2 | TY | 145 |
| SR 1 | Product 2 | PLAN | 200 |
| SR 2 | Product 1 | LY | 300 |
| SR 2 | Product 1 | TY | 400 |
| SR 2 | Product 1 | PLAN | 500 |
Table#2
| Sales Resource | Product | LY | TY | PLAN |
|---|---|---|---|---|
| SR 1 | Product 1 | 120 | 122 | 130 |
| SR 1 | Product 2 | 150 | 145 | 200 |
| SR 2 | Product 1 | 300 | 400 | 500 |
Answers
-
-
Oracle Business Intelligence 12.2.1.4.0
Error
View Display Error
Exceeded configured maximum number of allowed output prompts, sections, rows, or columns.
Error Details
0 -
There are bunch of settings in instanceconfig.xml defining how many rows/columns/cells etc. to display for a query result. It's a OBIPS setting.
The doc and various MOS documents cover them, there isn't a magic setting and those who say to set everything to "millions" are those who wants to use OBIEE for datapumping.
For example in your case it's a pivot, in the config file (if not wrong) there is a <Pivot> section with subentries like <MaxCells> etc. There is where you can review current limits and increase them if needed.
0 -
Hi @Guillermo Bueno,
As @Gianni Ceresa has mentioned, there should be similar discussions here for this type of error - I have asked this in the past .
If you have access to My Oracle Support, there are also many notes on this. Try these for starters:
OBIEE : Error: "Exceeded configured maximum number of allowed input records. Error Codes: EKMT3FK5:OI2DL65" when Exporting or Opening a Large Report (Doc ID 1487839.1)
OBIEE 11g - Not Able To Export All Rows From a Report "<div>" Message in the .CSV File And "Maximum total number of cells exceeded" Error in Sawlog.log (Doc ID 1999106.1)
OBIEE 11g|12c: Download | Export To Excel Via A Load Balancer Fails With "There was an error processing your download. Please Check with your administrator" (Doc ID 2167506.1) -> Look at the bottom, under the References section for more links.
Regards,
Charles
0 -
@Gianni Ceresa @Charles M thank you for your responses. I talked to the support team before posting and those limits can not by changed.
I'll keep looking (SQL) and let you know if I find a work around.
0 -
Guillermo Bueno wrote: I talked to the support team before posting and those limits can not by changed.
^--- Wait, what? Why? That's the weirdest answer.
0 -
I mean they can be changed but they will not be changed... same with 10,00 row agent limits and...
0 -
Well ... even by SQL you can't really bypass a limit set by the presentation service.
So the question is more if you have the proper aggregations set in the RPD so that the query would return you a smaller number of results.
As alternatives add a prompt and request your users to select something to see a subset of the data only...
0 -
I have to agree with Gianni on this one. You will have to reduce amount of raw data flowing into the analysis and the view itself. There is no way of "cheating you way around" the bottleneck but rather you have to redesign the request, the data model, the analysis - one, all or a combo.
0 -
@Gianni Ceresa Let's say i do not have that limit. If I use the section SQL Issued on the advance tab, can i pivot data using sql?
I tried different variations of the following with no luck:
SELECT * FROM
(
SELECT
"Time"."yyyymm",
"HR Profile"."Country",
"HR Profile"."Alias",
"HR Profile"."Name",
" HR Profile"."Position",
"Rev"."Type of Rev",
"Rev"."Type of Rev Cd",
"Rev"."Name office",
"Name Rev"."Net Rev",
'Group1' AS Region,
'Bikes' AS BU
FROM "XYZ" WHERE
("Time"."yyyymm" IN (201901, 201908))
AND ("HR Profile"."Country" = 'NI'))
)
PIVOT
(
SUM ("Name Rev"."Net Rev") AS sum_all FOR ("Rev"."Type of Rev") IN ('TY', 'Budget', 'LY')
)
0
