More than 4000 Character in OBIEE

Recently I have come across throught the business requirement where the backe end databae SQL server is having some dimensional columns in a table which is having around 15000 character. As per the business need I need to show the same value in BI Dashboard.

But as we know OBIEE repository is having 3 types of Character types and max size of the datatype is 4000

1. VARCHAR
2. BINARY VARCHAR
3. LONGVARCHAR

If values exceed more than 4000 character in a columns It  does not show the value in the OBIEE answers/Dashboards but the interesting fact is OBIEE VARCHAR column support to the max size of 8000

So here is the workaround:

Step1:
Right click on the particular column in the physical layer of the repository and select 'Properties' and set LONGVARCHAR as data type and set '32768' as size

Step2:
Use the substring function the split the value in the column. So here my columns is having around 15000 character so I am splitting first 8000 character into one column and another 7000 character to another column.

Here keep two things in Minds.

First we need to cast the column to change LONGVARCHAR to VARCHAR and then use SUBSTRING to split the values

For e.g:

Original Column:
BAW."Dim - Product"."Sales Comment"
Sales Comment1 : Which will be holding the value of first 8000 character 

CAST ( SUBSTRING(BAW."Dim - Product"."Sales Comment" FROM 0 FOR 8000) AS VARCHAR ( 8000 ))

Sales Comment2: which will be holding the value of another 8000 character
 CAST ( SUBSTRING(BAW."Dim - Product"."Sales Comment" FROM 8000 FOR 16000) AS VARCHAR ( 8000 )) 

Step3: 
Drag the newly created columns into the corresponding presentation tables in presentation layer.

Step4: 
Reload the metadata repositoy in OBIEE answers side and check with the newly imported columns in results.

Note:
If you need both values in a single column result, try to use the narrative view methods.

Cool... isn't it???!!!

Cheers!
Bose

Important OBIEE Interview Questions

Q1. Tell me about you and your technical expertise.

      Ans: your personal details, with studies job experience and highlighting the projects 

      and your technical skills

Q2. Which versions of OBI/ Siebel Analytics you have worked in?

Q3.Have you worked in Informatica? Which version

Q4. Explain me about your recent Project?

      Ans: Explain the Domain of the Project, Company from which you have worked in             that project, who is the Client, Technologies used in this application like OS,             

      Database,  OBI, Source Application (Mention as Siebel CRM)

Q5. Have you worked on OBIEE or OBIA?

Ans: OBIA

Q6.What is OBIA?

Q7.What is OBAW?

Q8.What is the Components of OBIEE and OBIA?

Q9.What is the Difference in OBIEE and OBIA?

Q10. Explain the Architecture of OBIA?

Q11. Explain the architecture of OBIEE?

Q12. What have you done in RPD?

Q13. What have you done in Reports what kind of reports / views you have worked in

Q14. Explain about one of the complex situation you have faced in your project and how          did you solve that. (While answering Provide Example)

Q15. What is Connection Pool?(While answering Provide Example)

Q16. What do you mean by Shared Log On option in the Connection Pool?(While answering         Provide Example)

Q17. How do you create a DSN?

Q18. RPD UserID and Password and Connection Pool user ID Password are usually same or            different? Why? (While answering Provide Example)

Q19. What are the Physical Layer Objects?

Q20. Why do you need to create an Alias? (While answering Provide Example)

Q30. How do you create a view in the Physical Layer of RPD? (While answering Provide            Example)

Q31. How many Layers are there in the RPD?

Q32. What is the Use of BMM Layer? (While answering Provide Example)

Q33. What is the Use of RPD? (While answering Provide Example)

Q34. What is the use of Presentation Catalogue? (While answering Provide Example)

Q35. RPD is Holds Data or Metadata?

Q36. What is the use of Config.INI File (While answering Provide Example?)

Q37. Where is this file located? (While answering Provide Example)

Q38. Where is the Presentation Catalogue located (While answering Provide Example?)

Q39. How do you enable Cache (While answering Provide Example?)

Q40. What are the other properties that you can set in the Config File (While answering Provide Example)

Q41. Where is Instance Config.xml located and what is the use of this File(While answering Provide Example)

Q42. What is Cache Persistence? Where do you set that? (While answering Provide Example)

Q43. How do you make a table "Cacheable" (While answering Provide Example?)

Q44. Suppose In the Config file I have enabled the Cache still cache is Not generating what could be the possible reason?(While answering Provide Example)

Q45. Based on What criteria or situation a report / query will hit the cache? (While answering Provide Example)

Q46. What do you understand by Cache? (While answering Provide Example)

Q47. What is a Fact Table? (While answering Provide Example)

Q48. What is a dimension table? (While answering Provide Example)

Q49. What is Measure? (While answering Provide Example)

Q50. What is Attribute? (While answering Provide Example)

Q51. What is star schema? (While answering Provide Example)

Q52. What is snowflake Schema? (While answering Provide Example)

Q53. What are the Difference BW OLTP and OLAP? (While answering Provide Example)

Q54. What is Normalized and what is De-normalized? (While answering Provide Example)

Q55. Why the Fact is on the many side and Dimension is at the One Side of a star schema (While answering Provide Example)

Q56. How many LTS can a logical table have? (While answering Provide Example)

Q57. How many Physical Sources a LTS can have? (While answering Provide Example)

Q58. In which scenario you will have more than one logical table source under one logical table? (While answering Provide Example)

Q59. Under which scenario you will have one LTS and More than one Physical Sources inside that? (While answering Provide Example)

Q60. Why do we need to create a complex/logical join in the BMM layer? (While answering Provide Example)

Q61. What is the difference b/w Logical join and Physical Join?(While answering Provide Example)

Q62. One Logical Column can be mapped to how many Physical Columns? (While answering Provide Example)

Q63. One Logical Column can be mapped to how many logical columns? (While answering Provide Example)

Q64. If you create a Logical column based on an existing logical Column, and a similar column based on two physical columns what would be the difference? (While answering Provide Example)

Q65. How do you create a calculated measure? (While answering Provide Example)

Q66. How do you create Dimension Hierarchy? Tell all the steps(While answering Provide Example)

Q67. Why Dim Hierarchy is required? (While answering Provide Example)

Q68. What do you understand about content level of a Fact Table? (While answering Provide Example)

Q69. What do you understand about Granularity of a fact and Dimension table? (While answering Provide Example)

Q70. What is the Significance of providing the content specification in the Fact Table? (While answering Provide Example)

Q71. How do you implement Aggregate Fact Table? (While answering Provide Example) provide all the steps

Q72. Why do we need to use Aggregate table? (While answering Provide Example)

Q73. While implementing aggregate table how many LTS will be there in the logical table? (While answering Provide Example)

Q74. One presentation catalogue can be mapped to how many Business Model? (While answering Provide Example)

Q75. One How many Presentation catalogue can be mapped to one Business Model.(While answering Provide Example)

Q76. How many Types of variables are present ? (While answering Provide Example)

Q77. What is the use of Each Type of Variable? (While answering Provide Example)

Q78. What is a Initialization Block? (While answering Provide Example)

Q79. What is a Row Wise Initialization block? (While answering Provide Example)

Q80. What is a Level Based Measure? (While answering Provide Example)

Q81. What is the use of a Level Based Measure? (While answering Provide Example)

Q82. How do you create Level Based Measure? (While answering Provide Example)

Q83. I want to create the Following Measure Explain how you will do that.

     # of SR closed in last 14 Days. (While answering Provide Example)

Q84. Want to create a measure.

     # Of SR Closed Till Date (While answering Provide Example)

Q85. What is the Time Series Function? How to use Time Series Function. (While answering Provide Example)

Q86. How to create following measure?

     # of SR Closed Monthly / No of SR Closed Total

Q87. What is Fragmentation Content? (While answering Provide Example)

Q88. What is Where Clause Filter? Where do you apply such filter? Why do you apply such filter? (While answering Provide Example)

Q89. What is Authentication? What is Authorization? (While answering Provide Example)

Q90. How many type of Authentication are present? (While answering Provide Example)

Q91. What do you mean by Database authentication? (While answering Provide Example)

Q92. What do you mean by External Table authentication? (While answering Provide Example)

Q93. How do you authenticate the users using the variables? What kind of variable you use for this purpose (While answering Provide Example)

Q94. What is the Difference between Non system Session Variable and Dynamic Repository Variable?(While answering Provide Example)

Q95. How do you provide access to different Subject Areas? (While answering Provide Example)

Q96. Can a Group be member of another Group? (While answering Provide Example)

Q97. Suppose a User U1 is member of a Group1. Group1 has access to Subject Area A1. Group1 Does not have access to Subject Area A2, Group1 is member of Group2. Group2 has no Access for Subject Area A2 , but has Access for Subject Area Sa3.

User U1 will have access to which subject Areas? Question from Group Inheritance (While answering Provide Example)

Q98. Want to replace the Null Values as 0 for the Measure Sales Value? How would you do that?(While answering Provide Example)

Q99.What is the difference between Pivot and Tabular report?(While answering Provide Example)

Q100. Why do we need exclude column option in Pivot report? (While answering Provide Example)

Q101. What is section in a pivot table report? (While answering Provide Example)

Q.102 what is Pages in a pivot table report? (While answering Provide Example)

Q103. What is calculated item in a pivot table report (While answering Provide Example?)

Q104. What is conditional formatting (While answering Provide Example)

Q105.  I want to compare if actual is greater than the target then we need to highlight the sales rep column how to do this (While answering Provide Example)

Q106. What do you mean by Scope of a Dashboard prompt (While answering Provide Example?)

Q 107.  What is Dashboard prompt how to create dashboard prompt (While answering Provide Example)

Q108. Difference between Column selector and View Selector (While answering Provide Example)

Q109. What do you understand by Report Defaults in Dashboard prompt (While answering Provide Example?)

Q110. What is the difference between Dashboard prompt and Global Filter (While answering Provide Example?)

Q111. What is the Difference between dashboard Prompt and Report Prompt (While answering Provide Example)

Q112. How many Type of Graphs are supported in OBI (While answering Provide Example)

Q113. Can we navigate from graph how? (While answering Provide Example)

Q114. How do you navigate from report? (While answering Provide Example)

Q115. I have a parent report with Region and sales. Once i click on a particular Region                Value it should drill down to other report with Region and Pending order, for that               particular region I clicked on. How to achieve this? (While answering Provide Example)

Q116. What is the difference in Report prompt and dashboard prompt?  (While answering Provide Example)

Q117. What is guided navigation (While answering Provide Example?)

Q118. How to attach document in the Dashboard (While answering Provide Example)

Q119. How to Create Combined Request (While answering Provide Example)

Q120. How to use Filter based on another request (While answering Provide Example)

Q121. What is narrative view?

Q122. I want to remove the download to excel option in the dashboard Reports - how (While answering Provide Example)

 Edit Dashboard -> select Property of Report -> Select Report link -> uncheck the download option.

Q123. How to create dashboard?

 Settings -> Administration -> Manage Interactive Dashboard -> Create Dashboard -> Give the dashboard name and builder to create.

Q124. How to give access to the different dashboards to different groups (While answering Provide Example).

 Settings -> Administration -> Manage Interactive Dashboard -> Select Lock Symbol for the specific Dashboard and give access to the users.

Q125. How to deny Permission to use answers to a particular group. (While answering Provide Example)

 Settings -> Administration -> Managed Privileges -> Access -> Access to Answers

Cheers!

Bose