on 03-24-2011 9:22 AM
Dear All,
I would like to create common objects for 3 tables which has same columns.
for example: we have 3 transaction tables, which has the data archived yearly basis.
1. transaction-2008
2. transaction-2009
3. transaction-2010
All the columns in the above tables are same. I need to create the objects using these three tables.
Is there any way to build the common objects using all the three tables, based on some criteria the tables in query will change automatically.
If I create object set for each table then objects will be repeated. I dont want to create like that.
Regards,
Sadiq.
Hi,
Use Aggregate Awareness function in order to use it.
As all the tables are same what you could do is, create 3 year objects such as 2008, 2009 and 2010.
Define compatability using Aggregate navigation.
So you could use one set of objects depneds on the year object you pulls in the report.
This solution works wonderful for you. Try this out.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Dear Gowtham,
Thanks for your solution.
I already applied the Agg.Aware function. We have a Transaction ID column, which has different data type (Char, number). I used this object for Agg.Aware. As you suggested, I created three classes 2008,2009,2010, in each class I placed this object with respective table defination.
Ex:
Class Select in Object
2008 2008.Transaction_ID
2009 2009.Transaction_ID
2010 2010.Transaction_ID
Created the objects using agg_aware function for all the rest of the objects except the Transaction_ID.
This works fine for me.
Sorry, I did not update the thread. I was a bit busy in my work.
Thanks for your valuble suggestions.
Regards,
Sadiq.
You can create Drived Table in the universe for objects you want from these 3 tables. You might be knowing the relation between these 3 table.
For creating the drived table please go through the Universe Desginer Guide. you can download this guide from //www.help.sap.com
Second option is create view/Materialized view in the database poiting to these 3 tables and use this view/materialized view in your universe as Table.
Hope this is what you are looking out for.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Rachna,
I will explain why we devided one table into three.
We hava a base table called "Transaction" which has all the customer transactions data for the past 5 years. Whenever query hits this table, either WebI hangs or Timout happens.
To resolve this, we devided this table into three tables.
All the three tables have same columns, I want to use these three tables in the universe and dont want to repeat the objects.
Creating view, will lead to the same situation.
Could you please guide me if there is any possibility?
Regards,
Sadiq.
I agree that View will not impact anything on your Performance. I would suggest to go with Materialized view. This is used to improve the performence.
Are you extracting the data from OLTP or from Datawarehouse.
Rather than creating 3 table by any chance can you do the partioning of the table.
I
am not sure your database structure and the relation between the table. You can talk to your database Admin regarding to this.
Also, try to change some parameter in the universe like Array fatch size etc and check if your webi still hang up.
As I could Understand that You must be having same dimention value in all the table but fact/Measure value are keep getting changed as per the transcation. You can use @aggregate Awareness Funtion on measure.
Edited by: srrachna on Mar 24, 2011 1:37 PM
It depends upon your reporting and how you plan to implement it. How would you know, when the query should go to the different tables? What if it should go to 2010 and 2009?
One simple option would be a derived table to union all three of your transaction tables and then create your objects against that, treating it as one large fact table.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
93 | |
10 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.