Here is a sample dashboard that I built about a year back as proof of concept.If you hover your house over the segments and each customers then the next level of info changes. It did not took me much time to build it but as I don't have much time today I will put how to build drilling function in dashboard in another post.Enjoy..
Tuesday, December 18, 2012
Monday, December 10, 2012
Calculation Context in Business Objects :
In SAP Business Objects the WebI tool is designed to put some intelligence based on selected dimension for calculation of any measures. For example if you select a couple of objects like Month and Sales.Then the BO will group by all the sales records by month however if you drag Year with sales its will automatically group by the measure by Year within its reporting engine and show it by year. This works great for most of the simple scenario.
However there is couple of use case where this approach breaks or does not work all great. We will shed some light on it here
Case 1 : There are some scenario where the measures that are calculated are not fully additive. For example if the measure is based of a Average or percentage then the default syntax approach would not work right. Some times these measure needed to be calculated by a database query rather then the reporting engine and here comes the need for smart measure. That is a topic of discussion for another post however.
Case 2 : What happens if there is a case I want to see the sales figure by month also I want the maximum sales for any given month for the year in the same record. Now here come the need for calculation syntax. Calculation syntax is nothing but overriding the default syntax of the section or table and then replacing it with a new set of dimension to calculate.
Now we will go a little deeper into the calculation syntax.Now as we have understood the problem there is actually more to it. In the specific syntax we are needed to change the group by which is the basis on which we calculate the number also we need to aggregate the results themselves to be printed into specific areas of the reports like the report header/footer , breaks , sections footers or some other places cross tab reports. So there is actually two problems still, one ask the reporting engine to tell the group by clause like in SQL (Input Context ) or we want to tell where we want it to print the formula like report footer/header or a block with a break (Output Context )
Input Context :
Input context is quite simple to understand , basically it is asking the formula to be calculated based on specific context like group by clause in SQL.
For example if I have a revenue measure and its just defined as SUM([Revenue]) and we put it with dimension like year. So the results will be like the query below.
Select Sum(Revenue) , Month
from Sales
Group by Month
Similarly the group be clause would change it you put it along with Year.However if you want to show the max monthly sales in entire year along with sales for each month you have to use the input context for the max monthly sales in the year. The syntax is as below.Which you can also get easily from internet. Now remember the default context you have in your section is Month.
Max ([Revenue] In ([Year]))
Remember the input context should be in parenthesis and all the dimensions needs to be separated by semicolons. Even if you use one dimension you need the parenthesis.
So what it actually does , it basically adds a measure which is calculated based on a different group by clause and merges the results into your report using your common dimensions. Simple ??
If you are well versed with Oracle SQL and analytic function you can still get the results in the query below.
Select Month , Sum(Revenue) ,
Max(Revenue) OVER ( partitioned by Year )
from Sales
group by Month
So whats is so great about them , honestly ; NOTHING. Its just the way you need to do complex calculation in SAP Business Objects.
Now lets see here we just defined all the dimensions based on which the calculation needs to be done.So what happens when I have 20-30 or even more dimension which are being used in the report. Do you need to explicitly write all of them into the formula if you need to use the calculation syntax.
The answer is Yes but there are some easier ways to do it..
So here comes the need for addition or deletion of some dimension from the default calculation context and they are " FOREACH" and " FORALL". The objective is very simple "ForEach" adds a new dimension to the default context and "ForAll" removes a dimension to the default context.
Here is a example :
Here the default syntax is YEAR and Quarter. So if I want to calculate the Year total as shown above , I can just remove the Quarter from default syntax using "FORALL". Now the formula would be like below.
Sum([Revenue] FORALL ([Quarter]))
Similiarly if you need to produce the monthly max sales within querter you can use ForEach.
MAX([REVENUE] FOREACH [MONTH])
As this post has already become so large I will spare the talk about the output context to another post..
In SAP Business Objects the WebI tool is designed to put some intelligence based on selected dimension for calculation of any measures. For example if you select a couple of objects like Month and Sales.Then the BO will group by all the sales records by month however if you drag Year with sales its will automatically group by the measure by Year within its reporting engine and show it by year. This works great for most of the simple scenario.
However there is couple of use case where this approach breaks or does not work all great. We will shed some light on it here
Case 1 : There are some scenario where the measures that are calculated are not fully additive. For example if the measure is based of a Average or percentage then the default syntax approach would not work right. Some times these measure needed to be calculated by a database query rather then the reporting engine and here comes the need for smart measure. That is a topic of discussion for another post however.
Case 2 : What happens if there is a case I want to see the sales figure by month also I want the maximum sales for any given month for the year in the same record. Now here come the need for calculation syntax. Calculation syntax is nothing but overriding the default syntax of the section or table and then replacing it with a new set of dimension to calculate.
Now we will go a little deeper into the calculation syntax.Now as we have understood the problem there is actually more to it. In the specific syntax we are needed to change the group by which is the basis on which we calculate the number also we need to aggregate the results themselves to be printed into specific areas of the reports like the report header/footer , breaks , sections footers or some other places cross tab reports. So there is actually two problems still, one ask the reporting engine to tell the group by clause like in SQL (Input Context ) or we want to tell where we want it to print the formula like report footer/header or a block with a break (Output Context )
Input Context :
Input context is quite simple to understand , basically it is asking the formula to be calculated based on specific context like group by clause in SQL.
For example if I have a revenue measure and its just defined as SUM([Revenue]) and we put it with dimension like year. So the results will be like the query below.
Select Sum(Revenue) , Month
from Sales
Group by Month
Similarly the group be clause would change it you put it along with Year.However if you want to show the max monthly sales in entire year along with sales for each month you have to use the input context for the max monthly sales in the year. The syntax is as below.Which you can also get easily from internet. Now remember the default context you have in your section is Month.
Max ([Revenue] In ([Year]))
Remember the input context should be in parenthesis and all the dimensions needs to be separated by semicolons. Even if you use one dimension you need the parenthesis.
So what it actually does , it basically adds a measure which is calculated based on a different group by clause and merges the results into your report using your common dimensions. Simple ??
If you are well versed with Oracle SQL and analytic function you can still get the results in the query below.
Select Month , Sum(Revenue) ,
Max(Revenue) OVER ( partitioned by Year )
from Sales
group by Month
So whats is so great about them , honestly ; NOTHING. Its just the way you need to do complex calculation in SAP Business Objects.
Now lets see here we just defined all the dimensions based on which the calculation needs to be done.So what happens when I have 20-30 or even more dimension which are being used in the report. Do you need to explicitly write all of them into the formula if you need to use the calculation syntax.
The answer is Yes but there are some easier ways to do it..
So here comes the need for addition or deletion of some dimension from the default calculation context and they are " FOREACH" and " FORALL". The objective is very simple "ForEach" adds a new dimension to the default context and "ForAll" removes a dimension to the default context.
Here is a example :
| YEAR | QUARTER | SALES | YEAR TOTAL | ||
| 2012 | 1 | 100 | 1000 | ||
| 2012 | 2 | 200 | 1000 | ||
| 2012 | 3 | 300 | 1000 | ||
| 2012 | 4 | 400 | 1000 | ||
| 2011 | 1 | 100 | 1100 | ||
| 2011 | 2 | 200 | 1100 | ||
| 2011 | 3 | 300 | 1100 | ||
| 2011 | 4 | 500 | 1100 |
Here the default syntax is YEAR and Quarter. So if I want to calculate the Year total as shown above , I can just remove the Quarter from default syntax using "FORALL". Now the formula would be like below.
Sum([Revenue] FORALL ([Quarter]))
Similiarly if you need to produce the monthly max sales within querter you can use ForEach.
MAX([REVENUE] FOREACH [MONTH])
As this post has already become so large I will spare the talk about the output context to another post..
Few more things that I wanted to write about quite a long ago however never got a chance or motivation to do so. I wanted to list out those first so that at least I will not forget about them.
1. Custom bursting using Business Objects' own publication
2. Calculation context and few things to remember using the calculation contexts.
3. Custom event based triggers for BO reports.
4. Business Objects visual intelligence and first impression
5. Synchronization of query and merged dimensions
6. Relational connection to ECC using JCo enterprise adapter
7. BO data steward and data Insight (some thoughts)
8. Smart measures and what is smart about them
9. Tag Cloud and how powerful it can be in Business Objects
10. The future of BI from querying to reporting to predictive analytic..some thoughts
This list is just for my reference and I intend to finish in a couple of weeks .. I am just publishing these to put forth my commitment to share knowledge..
1. Custom bursting using Business Objects' own publication
2. Calculation context and few things to remember using the calculation contexts.
3. Custom event based triggers for BO reports.
4. Business Objects visual intelligence and first impression
5. Synchronization of query and merged dimensions
6. Relational connection to ECC using JCo enterprise adapter
7. BO data steward and data Insight (some thoughts)
8. Smart measures and what is smart about them
9. Tag Cloud and how powerful it can be in Business Objects
10. The future of BI from querying to reporting to predictive analytic..some thoughts
This list is just for my reference and I intend to finish in a couple of weeks .. I am just publishing these to put forth my commitment to share knowledge..
Wednesday, December 5, 2012
Issues : Migration with LCM in Business Objects BI 4
I will start up with a new category today.. there are many blogs out there on BO any most of them are excellent source of information. So I thought why not start something very unique and personal to me and something that might save time for someone.. I will take the effort as a success if it save more time of someone than the time it took me to write it. Many of these issues took days , weeks to resolve involving several developers. Also I will include some keywords as its very difficult to search and get to the issue other wise.Please comment if you needs any details of these I will be glad to reply.
1. LCM ( Lifecycle Manager ) in BI 4 does not promote all the dependent objects of reports mainly Crystal seamlessly in BI 4 FP1 . Some time the LOVs does not move through some times the Business view does not move, some time Universe or connected even they are selected in the objects to be promoted does not go through. We faced issues for months while migrating from BOXI 3.1 to 4.0. We created bunch of Service Tickets to SAP and and did a lot of cofig changes howeverthe problem hardly resolved.
Resolution :
You have to be very very meticulous while promotion through LCM (although its the only tool available now with BI4 if you discount Upgrade Management Tool UMT ).
1. Create a clean Validation or Pre Dev environment with UMT if there is a migration from BOXI 3.1 to BI 4 is the ultimate goal.
2.Preferable handle Connection separately promote them it the before starting up with any content.
3.Handle the promotion of the Universe and business views separately through LCM and verify the universe or Business View in the target environment before any report content migration.
4. In case of Business view or Universe with bigger size and many hundred objects you would find in many cased of partial success and if its a partial success it means there are few object never made through. You will find ghost objects in the target environment. In these case carry on the migration with LCM as many times (may be 3-4 times) until the result shows fully success.
5. If the reporting has a lot of dynamic LOVs using Business View in crystal reports handle the LOVs independently. As the reports that are having a large number of dynamic LOV will drop of the LOV from dynamic to static. And you might get a error as Item 1 not found in collection . If you see this error then its a indicator that the migration has some issue.
6.Now once the BVs are promoted there is quite possibility that the data access security is screwed up and it will result in blank LOV , blank crystal reports even report producing blank SQL queries.
7.In case of BV make sure all the objects in the data foundation and business element has correct data access rights. We had to enable the data access right to everyone for most of the LOV and BV. Also there would be cased where the object right will not be correctly inherited to the child objects and you have to setup rights for all child objects. This is most likely a bug for now.I found this after lot of experiment with Business View manager
8. Now once all these done and in place start the migration of reports and contents. Again there would be cases where there will be partial success. Remember to try multiple times until they are succeed.
9. Now you are ready to go but you might face quite a few weird issue inbetween in CMC and in the report itself. Like issue with cache sever and many reporting anomalies. Now thats a topic for later post , till then CYA...
1. LCM ( Lifecycle Manager ) in BI 4 does not promote all the dependent objects of reports mainly Crystal seamlessly in BI 4 FP1 . Some time the LOVs does not move through some times the Business view does not move, some time Universe or connected even they are selected in the objects to be promoted does not go through. We faced issues for months while migrating from BOXI 3.1 to 4.0. We created bunch of Service Tickets to SAP and and did a lot of cofig changes howeverthe problem hardly resolved.
Resolution :
You have to be very very meticulous while promotion through LCM (although its the only tool available now with BI4 if you discount Upgrade Management Tool UMT ).
1. Create a clean Validation or Pre Dev environment with UMT if there is a migration from BOXI 3.1 to BI 4 is the ultimate goal.
2.Preferable handle Connection separately promote them it the before starting up with any content.
3.Handle the promotion of the Universe and business views separately through LCM and verify the universe or Business View in the target environment before any report content migration.
4. In case of Business view or Universe with bigger size and many hundred objects you would find in many cased of partial success and if its a partial success it means there are few object never made through. You will find ghost objects in the target environment. In these case carry on the migration with LCM as many times (may be 3-4 times) until the result shows fully success.
5. If the reporting has a lot of dynamic LOVs using Business View in crystal reports handle the LOVs independently. As the reports that are having a large number of dynamic LOV will drop of the LOV from dynamic to static. And you might get a error as Item 1 not found in collection . If you see this error then its a indicator that the migration has some issue.
6.Now once the BVs are promoted there is quite possibility that the data access security is screwed up and it will result in blank LOV , blank crystal reports even report producing blank SQL queries.
7.In case of BV make sure all the objects in the data foundation and business element has correct data access rights. We had to enable the data access right to everyone for most of the LOV and BV. Also there would be cased where the object right will not be correctly inherited to the child objects and you have to setup rights for all child objects. This is most likely a bug for now.I found this after lot of experiment with Business View manager
8. Now once all these done and in place start the migration of reports and contents. Again there would be cases where there will be partial success. Remember to try multiple times until they are succeed.
9. Now you are ready to go but you might face quite a few weird issue inbetween in CMC and in the report itself. Like issue with cache sever and many reporting anomalies. Now thats a topic for later post , till then CYA...
Ok..I am back to blogging again and this time I have made a pledge. I will continue to blog my experience no matter what the work pressure may be.
Meanwhile after the data profiling and BI Architect assignment I am back to a new role with new assignment with multiple source universes in BI 4 and mean while involved integrally with all the BI 4 migration hiccups and Life Cycle manager issues , and all I can tell you its one of the tool that can make a migration project very very difficult to execute if the reporting contents are crystal reports and Business Views and LOVs. One advise take the security structure very seriously in BI 4 its quite different than BOXI 3.1. You have to be very careful and allow the data layer access in Business View manager in the data foundation and in the business element else reports will produce blank SQL and LOVs will never populate data. I will describe the hiccups of LCM and migration in detail later.
Meanwhile there are so much to know and share in BO I can not ever think of where to start so no haphazard way of publishing one thought or one finding , rather I will categorize them in sections like IDT , WebI , Launchpad , Explorer, DashBoard Manager , CMC , HANA and the newest BO suite product Visual Intelligence (I have started evaluation of it recently ) also would include data modelling and SDK as a separate section ( yes I got to work in the Java and .NET SDK recently ).
So lets see how its goes and can I keep my promise to myself..
Meanwhile after the data profiling and BI Architect assignment I am back to a new role with new assignment with multiple source universes in BI 4 and mean while involved integrally with all the BI 4 migration hiccups and Life Cycle manager issues , and all I can tell you its one of the tool that can make a migration project very very difficult to execute if the reporting contents are crystal reports and Business Views and LOVs. One advise take the security structure very seriously in BI 4 its quite different than BOXI 3.1. You have to be very careful and allow the data layer access in Business View manager in the data foundation and in the business element else reports will produce blank SQL and LOVs will never populate data. I will describe the hiccups of LCM and migration in detail later.
Meanwhile there are so much to know and share in BO I can not ever think of where to start so no haphazard way of publishing one thought or one finding , rather I will categorize them in sections like IDT , WebI , Launchpad , Explorer, DashBoard Manager , CMC , HANA and the newest BO suite product Visual Intelligence (I have started evaluation of it recently ) also would include data modelling and SDK as a separate section ( yes I got to work in the Java and .NET SDK recently ).
So lets see how its goes and can I keep my promise to myself..
Subscribe to:
Posts (Atom)