Ask Sippy

Are you migrating your reports and dashboards from Oracle to SAP HANA?  Below are some ready to use date conversion SQL formulas which would save your significant amount of time. Please note that I have considered Financial year from 1-April to 31 March for year calculations. Also, number of days Calculations are considered till previous day. 1)   Current Financial year start e.g. 1-Apr-2017  ADD_MONTHS(ADD_DAYS(CURRENT_DATE, DAYOFYEAR(CURRENT_DATE) * -1 + 1), 3) 2)  Previous Financial Year start  ADD_YEARS(ADD_MONTHS(ADD_DAYS(CURRENT_DATE, DAYOFYEAR(CURRENT_DATE) * -1 + 1), 3), -1)  3) First date of current Month   ADD_DAYS(LAST_DAY(ADD_MONTHS(CURRENT_DATE, -1)), 1)   4)First date of current Month Previous year   Add_months(ADD_DAYS(LAST_DAY(ADD_MONTHS(CURRENT_DATE, -1)), 1), -12) 5)Number of days till date This Financial year  DAYS_BETWEEN(ADD_MONTHS(ADD_DAYS(CURRENT_DATE, DAYOFYEA...

If you know all the features of webi and universe, you may think that you generally don't feel any difficulty as you know all the features. But this question is not about features but real reporting issues that arise with reports. This is a interview question panel ask to understand your overall experience. If you have written particular years of experience, your answer to this question should give overall idea to them about the complexity of the work you have done. Below are top two issues people generally get while working on report 1) Report is taking too much time to run The report which you have developed on Universe may sometime take more time than expected. You need to explain here the performance tunning techniques you apply to solve the performance issues of report on webi , universe and database level. 2) Traps in the Universe You may be get inflated results due to traps i.e. fan trap or chasm trap. This can be avoided if you have done proper universe des...

Below are best practice guidelines for development of webi reports: • Variable names should start with “V_” or “Var_”. • Avoid creation of variables/formulas (in tool bar) without naming them. • Name the report tabs and queries with meaningful names and tab / query name should start with Capital letters. Example, Have DLY (daily), MTH (Monthly) and QTR (Quarterly) in end of report name to identify the report frequency easily. Example, “DWBI SALSE REPORT MTH”. • Follow the same hierarchy for Condition (objects) as classes/objects. It helps user locate the conditions quicker and makes condition objects more meaningful as well. • In query panel, pulling the Dimension objects followed by detail objects before selecting Measures help user locate the objects easily (it avoids scrolling down in search of any objects. This helps more in case of queries built with more than 25 objects). • Purging the data before saving it to the repository saves a lot of space on servers; It...

Below are best practice guidelines for Universe development • Universe Name & Universe Description should be defined with proper Business Name and description in the Universe properties. • Class name should be started in INIT cap. • Objects name should be started in INIT cap. • Object should be placed in alphabetical order in the class. • Measure Objects in any class should be placed after the dimensions/detail objects. • Always define every object with meaningful description so that it helps user easily understand the purpose of it which avoids going through heavy documentation. • Measure objects should be created with aggregate functions like sum, max, min etc. This will ensure that only required aggregated data is returned to reports instead of whole set of raw data. • Create the context with proper business logic names. It will help you to find out what context is used in a particular report. • Always place any derived tables or isolated tables down ...

Below are performance tuning guidelines for webi report development. These guidelines needs to be followed with Universe performance tuning guidelines given separately on this blog to get good performance reports. •  Avoid using larger number of reports in same document 10 reports per document is a reasonable number Do not exceed 20 reports per document • Don’t try to account for all scenarios in one document but rather focus on specific scenarios that add value to the consumer. 50,000 rows per document is a reasonable number Do not exceed 500,000 rows per document • Limit the number of Data Providers used Best Practice from the field is to use no more than 15 Data Providers per document Data providers run serially so run times add up Refresh time and merging of dimensions can cause some big delays on the Processing Server side • Create smaller, reusable documents   By creating smaller, reusable documents you: Reduce the time it takes to load the doc...

Below are Universe performance tuning guidelines : These guidelines need to followed with Webi performance tuning guidelines given separately on this blog to get good performance reports. • Always use aggregate functions i.e.  Sum, max, min etc with measure objects. • Set the proper query limits at the universe, especially “Limit size of result set to” & “Limit execution time to”. Setting the right parameters will result in a good universe by performance wise • Create aggregated tables for reports taking too much time to execute due to huge data set in underline tables. • List of values: Create LOV’s on small lookup/master tables instead of actual fact table. Avoid LOV on measure and date objects. Remove LOV from non required objects i.e. keep LOV on objects which are going to be used in filters. • Use conditional objects/filters on Universe level to filter out non required data from universe level instead of reports • Push complex calculation of object...