nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Designing Reports Based on Single Data Model

xplorerdev

Hi All,
 
I am converting a set of 70+ reports, already developed in other BI tools, into BIRT 4.4.2.
 
I have attached a screenshot of my data model. The data model has close to 60 dimension tables, 10 snowflake tables and 1 fact table. (Screenshot has fewer components as it just gives a picture of how the model looks like)
 
I have tried creating a sample report with 4 measures and 8 dimensions and it works fine. The reports comes within 5 secs. But now, I am unable to expand it. (Report design attached)
 
I need to now add the remaining dimensions. All these dimension will be available to the user as parameter inputs. I have tried creating separate datasets, tried writing custom sql query, tried creating joint data sets etc., but all effort goes for a toss as the report is not running at all.
 
Any thoughts/ideas aound the designing part will be highly appreciated.
 
Best Regards

Find more posts tagged with

Comments

micajblock

Is this OS BIRT or commercial BIRT?

xplorerdev

I intend to use Open Source BIRT.

micajblock

If all 60 dimensions are possible inputs, I would recommend to dynamically build the query based on parameters. Since I know about your other posts, you might need to dynamically build the cubes and measures.

xplorerdev

Thanks for the reply Mica.
 
I created multiple datasets: 1 for FACTquery, 3 for snowflake tables and 29 for dimension tables.
 
Then I created a joined dataset, MASTER, in which I joined all the above datasets. Then I have created a cube_MASTER with 2 Groups for the Dynamic Time Period and Dimension and thats it.
 
Well, initially, the reports were coming fine, but after the second snowflake was added to MASTER, due to the huge data size (i.e. 10+ lacs records in FACT), the MASTER just won't execute.
 
The FACT dataset query took 30 secs to preview. But the MASTER takes 9+ minutes for 5 lac records and more than 15 mins if, in FACT dataset, I am Selecting All Rows from Datasource.
 
I am trying to improve on the query. I just wanted to share my design so that you could, IN YOUR FREE TIME

, take a look and see if its the right way to approach or I need to rethink.
 
Best Regards

micajblock

You are taking a wrong approach. OS BIRT does not have a semantic layer (commercial BIRT does, but you want a free product).
 
I always discourage joined data sets if both data sets are from the same database as the join is done in Java. BIRT will almost never perform as well as the source database.
 
Every report should have ONLY the data it needs. See my previous note in this thread. The idea would be to dynamically build the query based on user inputs. One query.

xplorerdev

Hi Mica,
 
<div>Made modification to the report (attached). Kept one dataset, FACT, with a small query. Now, to make the query dynamic (i.e. give different result based on user inputs), we need to add some code in the beforeOpen window for the FACT dataset and I think it should be something like the following:</div>
<div> </div>
<div>SELECT </div>
<div>PERIOD, -- Computed column for Crosstab Row.</div>
<div>DIMENSION, -- Computed column for Crosstab Column.</div>
<div>SUM(FACT.ACCN_IN_FORC) AS "NO OF ACCOUNTS" -- Single measure.</div>
<div>FROM FACT -- Fact table.</div>
<div>LEFT OUTER JOIN TIME ON (FACT.MIS_DAT_FK = TIME.DAY_PK) --Time dimension table</div>
<div>LEFT JOIN DIM ON (FACT.NATN_FK = DIM.NATN_PK) --Other dimension table</div>
<div>WHERE PERIOD = params["pPeriod"].value --Crosstab Row value = pPeriod paramater user input value.</div>
<div>AND DIMENSION = params["pDimension"].value --Crosstab Column value = pDimension paramater user input value.</div>
<div>GROUP BY PERIOD, DIMENSION</div>
<div> </div>
<div>Performed few steps but, not able to put together a working solution.

</div>
<div> </div>
<div>Best Regards</div>

xplorerdev

<blockquote class="ipsBlockquote" data-author="mblock" data-cid="145907" data-time="1478784314">
<div>
You are taking a wrong approach. OS BIRT does not have a semantic layer (commercial BIRT does, but you want a free product).
 
I always discourage joined data sets if both data sets are from the same database as the join is done in Java. BIRT will almost never perform as well as the source database.
 
Every report should have ONLY the data it needs. See my previous note in this thread. The idea would be to dynamically build the query based on user inputs. One query.
</div>
</blockquote>
 
Hi Mica,
 
I have been able to put something together. Report design attached. Main thing to see in the design would be the beforeOpen script. The report seems to be running fine and timings are also good.
 
Is this the correct approach?
 
Best Regards

micajblock

You are going in the right direction, but I would change the from clause as well. I should be able to build an example using Classic Models today.
P.S. I like your use of the Case statement for the calculation of the No. Accounts, that is what I was planning on recommending to you.

micajblock

See attached example. All the code is in the beforeOpen event.

xplorerdev

<blockquote class="ipsBlockquote" data-author="mblock" data-cid="145932" data-time="1479125253">
<div>
You are going in the right direction, but I would change the from clause as well. I should be able to build an example using Classic Models today.
P.S. I like your use of the Case statement for the calculation of the No. Accounts, that is what I was planning on recommending to you. 
</div>
</blockquote>
 
Ahh. Finally. Thanks.

<blockquote class="ipsBlockquote" data-author="mblock" data-cid="145941" data-time="1479159673">
<div>
See attached example. All the code is in the beforeOpen event.
</div>
</blockquote>
 
This new code seems simpler. Thanks. Its working for Year/Quarter/Month. But it does not seem to work when I am adding a second DIMENSION.
 
My 1st default dimension is Nationality. I am taking Status Code as my second dimension and have altered the beforeOpen script in the foll manner:
 
<div>if (params["pDimension"].value == "Status Code") {</div>
<div>//for the select portion</div>
<div>this.queryText=this.queryText.replace("D2.NATN","D3.STAT_COD");</div>
<div>//for the group by portion</div>
<div>this.queryText=this.queryText.replace("D2.NATN","D3.STAT_COD");</div>
<div>// replace the from clause</div>
<div>this.queryText=this.queryText.replace("LEFT JOIN ARAWSH_MRT.T_DIM_NATN_CC D2 ON F1.NATN_FK=D2.NATN_PK","LEFT JOIN ARAWSH_MRT.T_DIM_STC_ACCN_STAT_CC D3 ON F1.STAT_COD_FK=D3.STAT_COD_PK");</div>
<div>}</div>
 
When I select Status Code from pDimension dropdown, I get the following error:
 
<div>org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.</div>
<div>SQL error #1:[ActuateDD][SQLServer JDBC Driver][SQLServer]The multi-part identifier "D3.STAT_COD_PK" could not be bound.</div>
<div>SQL error #2:[ActuateDD][SQLServer JDBC Driver][SQLServer]The multi-part identifier "D3.STAT_COD" could not be bound.</div>
<div>SQL error #3:[ActuateDD][SQLServer JDBC Driver][SQLServer]Statement(s) could not be prepared.</div>
 
Seems to be some problem with the join but I think I have named it in the right manner. The sql query works fine on the database though. :huh:
 
Report design attached.
 
Best Regards

micajblock

that is because you have spaces in your original query before an after the equal sign. What you have in your script is this:
<pre class="_prettyXprint">
this.queryText=this.queryText.replace("LEFT JOIN ARAWSH_MRT.T_DIM_NATN_CC D2 ON F1.NATN_FK=D2.NATN_PK","LEFT JOIN ARAWSH_MRT.T_DIM_STC_ACCN_STAT_CC D3 ON F1.STAT_COD_FK=D3.STAT_COD_PK");</pre>
What you need is this:
<div>
<pre class="_prettyXprint">
this.queryText=this.queryText.replace("LEFT JOIN ARAWSH_MRT.T_DIM_NATN_CC D2 ON F1.NATN_FK = D2.NATN_PK","LEFT JOIN ARAWSH_MRT.T_DIM_STC_ACCN_STAT_CC D3 ON F1.STAT_COD_FK=D3.STAT_COD_PK");</pre>
</div>

xplorerdev

<blockquote class="ipsBlockquote" data-author="mblock" data-cid="145956" data-time="1479225186">
<div>
 
that is because you have spaces in your original query before an after the equal sign. What you have in your script is this:
<pre class="_prettyXprint">
this.queryText=this.queryText.replace("LEFT JOIN ARAWSH_MRT.T_DIM_NATN_CC D2 ON F1.NATN_FK=D2.NATN_PK","LEFT JOIN ARAWSH_MRT.T_DIM_STC_ACCN_STAT_CC D3 ON F1.STAT_COD_FK=D3.STAT_COD_PK");</pre>
What you need is this:
<div>
<pre class="_prettyXprint">
this.queryText=this.queryText.replace("LEFT JOIN ARAWSH_MRT.T_DIM_NATN_CC D2 ON F1.NATN_FK = D2.NATN_PK","LEFT JOIN ARAWSH_MRT.T_DIM_STC_ACCN_STAT_CC D3 ON F1.STAT_COD_FK=D3.STAT_COD_PK");</pre>
</div>
 
</div>
</blockquote>
 
I did what you suggested but still the same 'The multi-part identifier' error.
 
I also checked in your report design script: If I put space before & after the = sign for Country, I am getting the foll error:
<div>SQL error #1:Column 'C.COUNTRY' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'C.COUNTRY' is not a column in the target table.;</div>
 
It seems, there is some other join issue in my report script, which is not happening when I run the query in the database but is there via the script.

xplorerdev

huh!
 
My brain is 100% fried now. Absolutely no clue about this 'The multi-part identifier' error. I have tried with diff dimensions. I have used tables from .dbo schema. I have checked all combinations of adding/removing a space. I have checked table-names/column-names/alias etc, all seems to be fine. I created another report using Classicmodels and that works fine. But my report will not work. As soon as I add a second dimension, I get the same error:
 
Caused by: java.sql.SQLException: [ActuateDD][SQLServer JDBC Driver][SQLServer]The multi-part identifier "s.stat_cod_pk" could not be bound.
 
New report design attached.
 
Best Regards

micajblock

Are all the dimensions of the same data type?

xplorerdev

<blockquote class="ipsBlockquote" data-author="mblock" data-cid="145979" data-time="1479309813" data-date="Today, 08:53 PM">Are all the dimensions of the same data type?</blockquote>
 
Yes. Datatypes follow a similar definition. STAT_COD_PK and NATN_PK are Integer. STAT_COD and NATN are Varchars. 
Best Regards.

micajblock

so they are not on the same data type.Integer and varchar are not the same.

xplorerdev

<blockquote class="ipsBlockquote" data-author="mblock" data-cid="145981" data-time="1479312112" data-date="Today, 09:31 PM">so they are not on the same data type.Integer and varchar are not the same.</blockquote> 
Yes. The joining keys are Integer and the name/description is varchar.

micajblock

can you attach a text file with the different queries? Also what are the data types of each fields.

xplorerdev

<blockquote class="ipsBlockquote" data-author="mblock" data-cid="145984" data-time="1479315244" data-date="Today, 10:24 PM">can you attach a text file with the different queries? Also what are the data types of each fields.</blockquote> 
Sure. I just reached home. Will send queries tomm mrng.

micajblock

Please verify that the queries you send work before you attach them, thanks.

xplorerdev

<blockquote class="ipsBlockquote" data-author="mblock" data-cid="145986" data-time="1479317929">
<div>
Please verify that the queries you send work before you attach them, thanks.
</div>
</blockquote>
 
Thanks for the support Mica. I have attached a sample report design, Dynamic Crosstab_wip_3.rptdesign. Queries, datatypes and other design details are in the attached text file, Multipart Identifier error investigation.txt.
 
<div>Error on selecting Status Code from dropdown:</div>
<div>java.sql.SQLException: [ActuateDD][SQLServer JDBC Driver][SQLServer]The multi-part identifier "s.stat_cod_pk" could not be bound.</div>
<div> </div>
<div>Error on selecting Nationality from dropdown:</div>
<div>java.sql.SQLException: [ActuateDD][SQLServer JDBC Driver][SQLServer]The multi-part identifier "n.natn_pk" could not be bound.</div>
 
Best Regards

micajblock

No idea, as everything looks good. As I cannot run your report I am somewhat limited. Here is what I would do.
<ol><li>Copy your report.</li>
<li>Copy your code form the beforeOpen script</li>
<li>Delete the cube and the data set.</li>
<li>Add a label with the text being your initial query</li>
<li>In the onCreate script of the label paste the code from the beforeOpen</li>
<li>replace all this.queryText to this.text</li>
<li>run the report with the different parameters and then see what is wrong with the queries.</li>
</ol>

xplorerdev

<blockquote class="ipsBlockquote" data-author="mblock" data-cid="146010" data-time="1479425966" data-date="Today, 05:09 AM">No idea, as everything looks good. As I cannot run your report I am somewhat limited. Here is what I would do. <ul class="bbcol decimal"><li>Copy your report.</li><li>Copy your code form the beforeOpen script</li><li>Delete the cube and the data set.</li><li>Add a label with the text being your initial query</li><li>In the onCreate script of the label paste the code from the beforeOpen</li><li>replace all this.queryText to this.text</li><li>run the report with the different parameters and then see what is wrong with the queries.</li></ul></blockquote> 
Seems like a lot of work. I am thinking what I could possibly do to debug this further. Hmmmm.

micajblock

Not a lot at all. I did it in 5 minutes (attached). Basically what I found out is it appears there some hidden characters or something. I copied the string directly from the original query and it works.

xplorerdev

Ohh. Great. I just stepped into office. Will try out the attached and give a report later. I guess its too late for you now. Good night and thanks.:)

xplorerdev

Finally..its working !
 
Here is what I thought and what I did: Multipart Identifier error was suggesting that the dimension in the select clause was not able to reference the table/alias. It seemed to me that the beforeOpen script was executing sequentially. Thus I needed to define the table/alias first so that the select clause dimension could point to it. So, I put the From clause this.queryText.replace at the top, followed by the Select clause and finally the Group By clause.It works now.

What I had all this while, which was not working:
<pre class="_prettyXprint _lang-auto _linenums:0">
if (params["Dimension"].value == "Status Code") {
//for the select portion
this.queryText=this.queryText.replace("d.deln","s.stat_cod");
//for the group by portion
this.queryText=this.queryText.replace("d.deln","s.stat_cod");
// replace the from clause. AT THE BOTTOM
this.queryText=this.queryText.replace("left outer join delinquency d on f.deln_fk=d.deln_pk","left outer join statuscode s on f.stat_cod_fk=s.stat_cod_pk");
}</pre>
Then I put the //replace the from clause code at the top and it works now!
<pre class="_prettyXprint _lang-auto _linenums:0">
if (params["Dimension"].value == "Status Code") {
// replace the from clause. AT THE TOP.
this.queryText=this.queryText.replace("left outer join delinquency d on f.deln_fk=d.deln_pk","left outer join statuscode s on f.stat_cod_fk=s.stat_cod_pk");
//for the select portion
this.queryText=this.queryText.replace("d.deln","s.stat_cod");
//for the group by portion
this.queryText=this.queryText.replace("d.deln","s.stat_cod");
}</pre>
Best Regards

micajblock

That is not the reason as the query does not get executed until the end of all the changes. Tell me if the attached works for you. All I did here is copy the below from the query in the text file to the beforeOpen script.
<pre class="_prettyXprint">
left outer join delinquency d on f.deln_fk=d.deln_pk</pre>
In any case I am glad it is finally working for you.

xplorerdev

Sure. Thanks. But I just stepped out. I will try this on Monday morning and report. 
PS: My timezone is IST. 
Best Regards.

xplorerdev

<blockquote class="ipsBlockquote" data-author="mblock" data-cid="146024" data-time="1479472560">
<div>
That is not the reason as the query does not get executed until the end of all the changes. Tell me if the attached works for you. All I did here is copy the below from the query in the text file to the beforeOpen script.
<pre class="_prettyXprint">
left outer join delinquency d on f.deln_fk=d.deln_pk</pre>
In any case I am glad it is finally working for you.
</div>
</blockquote>
 
I tried this Mica. But it ain't working. Same Multipart Identifier error.

What can possibly be wrong here? 
 
But I am relieved that its working with the change I did.

micajblock

I am glad you got something working. I am still convinced there is something different in the characters otherwise the 2 elements in the attached design would be the same. If I am not mistaken there are multiple characters for "_". In any case I would use the attached design as a testing bed to test the code for text replacement. Good luck with the rest of your project.