In-Database Data Mining for Retail Market Basket Analysis Using Oracle Advanced Analytics

https://www.youtube.com/watch?v=umMA4wfJ4ac

Онлайн построитель схем баз данных

Строим онлайн схемы базы данных:

http://dbdesigner.net/

Заходим под учетной записью: cvetikraa@gmail.com
Пароль: Cdtnkfyf1981!
 Google

OBI EE and Oracle Data Mining

Having recently attended one of Oracle’s coming out parties for its new Data Mining software, I thought I’d take the opportunity while its still fresh in my head to talk about how the two work together in Oracle’s vision of BI.

What is Data Mining?

First off, what is Data Mining and how is it different from traditional Business Intelligence?  Without getting into too much of a detailed discussion, Data Mining is the practice of extracting hidden knowledge out of a dataset to aid you in the decision making process.  By hidden knowledge, think of predicting what products a customer might buy based on a profile that has been developed via a review of thousands of other customers.  Or how about which attributes of a customer affects their buying patterns the most?  Think statistics, probabilities, clusters, correlations and predictions instead of trends and summaries.  Sometimes we don’t even know what to look for – Data Mining helps us out there with oftentimes startling revelations.
Typical Query & Reporting, OLAP or other analysis tools tend to be good at getting the details of a type of decision you know you want to make.  We are able to define metrics and dimensions and drill downs and reports and dashboards, as we usually have an idea about what we are after.  We just don’t know the #s and products and regions and customers involved.  A user might know how to define what a good customer is, and the BI system can develop that rule to show who the best customers are.  But what about a relatively new customer – will they be a good one or a bad one?  Can you make them into  a better customer than they already are?
With that being stated as just a few points about the differences (I don’t want to get into the whole topic on this post), can the two get along?  The answer is absolutely, and it is a particular strength of Oracle’s offerings as compared to others.
Keeping in line with Oracle’s philosophy of expanding functionality into the database engine, ODM is included in the Oracle 10 R2 database engine.  The unique architecture of having it in the database, and accessible via PL/SQL or Java APIs allows all of the other capabilities built into the Oracle platform to work – such as parallelism, indexing, materialized views, security, High Availability, etc.  The other great benefit of doing this is in its ability to eliminate data movement out of the database and into a Data Mining tool such as SAS – a time consuming effort for many environments.
The Data Mining process is very different than traditional BI.  With Data Mining, you first goal is to build a model, which the software creates off of a sample dataset that you first have to clean.  This model (think of it as super advanced function you can call on a record) can then be deployed to a single record or a whole dataset.
Use of a single record is useful in front-line applications, where a real time scoring may be done, with a resultant probability or outcome to be predicted.  The most commonly used example here is in a Call Center environment, where based off of the call and customer coming in, a prediction can be made as to which Offers the customer would most likely respond.  (If this sounds a lot like Oracle Real-Time Decision (RTD, acquired via Siebel via Sigma Dynamics), you are correct.  In this class of BI tools, Oracle plans to sell two products which have some solid overlap in functionality and capability.)  This scenario, where records are scored in real time, does not lend itself well to the batch processing that a typical BI system (OBI EE) does.  Although the data mining functions are accessible directly from SQL, presently OBI EE cannot generate these SQL extensions.  Of course one can get around that by using Opaque Views or database views and simply mapping a column.  I would consider this something you wouldn’t want to do save but a few rare cases.
The second manner is the ability to score a whole recordset – to basically execute an UPDATE statement in a nightly or weekly ETL load, setting a column to a value computed by the Data Mining Model.  In this scenario, the result is very simply a column in a table.  As such, any of our normal techniques can be applied to the column – it can be a dimensional attribute useful for filtering or campaign segmenting, or it can be used as a metric, such as an Avg Expect Value.  Sorry for the let down – there really isn’t anything flashy about using it.

So How to they Work Together?

There are exciting  possibilities when integrating the results of a sophisticated predictive model into a regular BI dashboard.  Matched with OBI EE’s desire to share high-quality information to a large community, you will now be able leverage the results of your Data Mining efforts to a much greater extent than with traditional Data Mining systems.

Enabling R and the relevant Analytics functions on OBIEE 12c

Установка по статье:
http://obieeil.blogspot.co.uk/2015/11/enabling-r-and-relevant-analytics.html 

Пошагово:

1. Распаковываем пакет, который находиться тут:
C:\Oracle\Middleware\Oracle_Home\bi\bifoundation\advanced_analytics
Распаковываем файл в ту же папку: r-installer.tar.gz.
Потом распаковываем еще раз: r-installer.tar

2. Установить утилиты:

Oracle recommends to download and install unzip and wget utilities:

• http://gnuwin32.sourceforge.net/packages/wget.htm
• http://gnuwin32.sourceforge.net/packages/unzip.htm 

3. В файле: r-package-install.R удаляем две первые строки. Сохраняем файл

4. Открываем cmd с правами администратора.

5. Заходим в каталог:
cd C:\Oracle\Middleware\Oracle_Home\bi\bifoundation\advanced_analytics\RInstaller

6. Устанавливаем переменную: set path=%path%;C:\Program Files (x86)\GnuWin32\bin
(устанавливать надо каждый раз, когда открываем cmd)

7. Выполняем команду: в этой же папке: RInstaller.bat install

8. Выполняем команду: RInstaller.bat installpackages

9. В папке: C:\Oracle\Middleware\user_projects\domains\bi\config\fmwconfig\biconfig\OBIS в файле NQSConfig устанавливаем параметр:
Lets find NQSConfig.ini in BI_DOMAIN/config/fmwconfig/biconfig/OBIS = ORacle_home/user_projects/domains/bi/config/fmwconfig/biconfig/OBIS. Find there ADVANCE_ANALYTICS_SCRIPT section and update R_EXECUTABLE_PATH to point to the R exactable path: C:/Program Files/R/R-3.1.1/bin/x64/R

10. Перестартовываем BI. Restarted OBIS.

лог cmd:

Полезные ссылки OBIEE 12C

Логи с запросами и ошибками:
C:\Oracle\Middleware\user_projects\domains\bi\servers\obis1\logs


NQConfig:
C:\Oracle\Middleware\user_projects\domains\bi\config\fmwconfig\biconfig\OBIS

Instanceconfig.xml 
F:\Oracle\Middleware\Oracle_Home\user_projects\domains\bi\config\fmwconfig\biconfig\OBIPS\instanceconfig.xml

OBIEE 12C + Data Mining

http://www.peakindicators.com/blog/obiee-12c-using-embedded-r-for-real-time-sentiment-analysis
https://www.lntinfotech.com/Blogs/Lists/Posts/Post.aspx?List=921a5925%2Da235%2D44ab%2Da605%2Ded1fc6e28dcd&ID=83&Web=721b315f%2D19e1%2D4439%2Da6a7%2D6a65042028ca 

Ссылка с примером: как запускать:
http://www.peakindicators.com/blog/testing-the-new-r-functions-on-the-obiee-12c-sampleapp

Пачка статей:
http://www.peakindicators.com/blog-category/big-data

Курс лекция по Data Mining
http://www.intuit.ru/studies/courses/6/6/info

Запуск процедуры из DbVis

@call budget.integration.replicate_d_pf();
@call budget.integration.replicate_d_department_pfm();
@call budget.integration.replicate_d_pfm();
@call budget.integration.replicate_d_user();

OBIEE 12c Impersonate User

http://www.profizone.ie/obiee-12c-impersonate-user/

OBI 12c: Segment "Update Counts" Giving Error Path Not Found (/system/mktgjob/job[nn])

To resolve this issue please complete the following steps:

1. Stop your OBI Presentation Server
2. Amend the OBI Presentation Server instanceconfig.xml Clustering Support value to false
3. Restart your OBI Presentation Server
4. Confirm the results of GetCounts.

An example of this is in F:\Oracle\Middleware\Oracle_Home\user_projects\domains\bi\config\fmwconfig\biconfig\OBIPS\instanceconfig.xml make a change to the Clustering value as listed below:

...
<ServerInstance>
<Marketing>
<Clustering>
<Enabled>false</Enabled>
</Clustering>
</Marketing>
</ServerInstance>

Сворачиваем раздел на странице

• 
  

  The fact that a section is collapsed is actually a container state as visible in the XML behind a saved customization. The XML storing the definition of the dashboard only contains collapsilble="true" or "false", but not the actual state.
  
  Customization example:
  

  <?xml version="1.0" encoding="utf-8"?>
  <sawsel:dashboardSelections xmlns:sawsel="com.siebel.analytics.web/dashselections/v1" xmlns:sawst="com.siebel.analytics.web/state/v1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="0.7">
  <sawst:envState xmlVersion="200811100"><sawst:container cid="d:dashboard" xsi:type="sawst:topLevelStateContainer">
  <sawst:container cid="p:5f17p12grfof9tb1" xsi:type="sawst:page" inDashboardPage="true">
  <sawst:container cid="r:or12dfr4au2qvktmj" xsi:type="sawst:report" defaultView="compoundView!1" folder="/shared/something" itemName="something"/>
  <sawst:container cid="s:0hq4f94fvkfgpg" xsi:type="sawst:section" minimized="true"/></sawst:container></sawst:container></sawst:envState></sawsel:dashboardSelections>

  Key part: xsi:type="sawst:section" minimized="true"
  
  Not sure if creating a shared customization valid for all users for each and every single dashboard page would be worth it.

  • Мне нравится Показать отметки "Мне нравится" (0)(0)
  • Действия
• 
  

  2. Re: Collapsible Section minimize by default

  

  Devpriyo 22.11.2012 9:55 (в ответ на Alex1)

  Hi Christian,
  
  I followed your link and was able to get the desired result. https://forums.oracle.com/forums/message.jspa?messageID=10423215#10423215_ Thanks
  
  I had to do the above in all my reports.
  
  My bigger concern now is that whenever I create a new user, the selections* folder is empty. So, I have to copy the content of selections folder into the _selections folder of the new user and only then I get my sections in the reports for the new user as collapsed. I have to perform this 3-4 steps for every user I create. Is this how it is in OBIEE11g or this is a bug ?
  
  
  Thanks n Regards
  Dev

  • Мне нравится Показать отметки "Мне нравится" (0)(0)
  • Действия
• 
  

  3. Re: Collapsible Section minimize by default

  

  Layman-Oracle 22.11.2012 15:16 (в ответ на Devpriyo)

  Not sure what code you have used. I have successfully done this using the javascript code embedded in a text object (contains HTML marked)within the section. This should work for all users without doing anything else
  
  <script type="text/javascript">
  saw.dashboard.onToggleSection('<your-section-id-here>',null);
  </script>
  
  Replace your-section-id-here with the actual section id which you are trying to auto-collapse by default. It will be something like d:dashboard~p:unsm5na5r999jejc~s:mvd7gvgnq90nad7m

  • Мне нравится Показать отметки "Мне нравится" (0)(0)
  • Действия
• 
  

  4. Re: Collapsible Section minimize by default

  

  exel_cs 20.12.2012 14:45 (в ответ на Layman-Oracle)

  Hi Layman
  
  Do you have to create a saved customization to get the sectionid?
  
  I want to collaspe a section but can seem to find the section id like
  
  d:dashboard~p:unsm5na5r999jejc~s:mvd7gvgnq90nad7m
  
  Where can i find this sectionid?
  
  Please advise
  
  Hiten
  
  Edited by: #:::HITEN:::# on 20-Dec-2012 06:44

  • Мне нравится Показать отметки "Мне нравится" (0)(0)
  • Действия
• 
  

  5. Re: Collapsible Section minimize by default

  

  Layman-Oracle 17.01.2013 11:18 (в ответ на exel_cs)

  The section id can be seen in the html code(View Source) of the rendered html page/report. Best to use firebug on FIrefox so that you can easlly see each individual element's html code.

Про наказания

http://madcats.ru/business/punishment-guide/?utm_source=facebook.com&utm_medium=social&utm_campaign=nakazat-sotrudnika--tselaya-nauka.-chtoby

Oracle Retail Data Model (ORDM) Tutorial

http://download.oracle.com/oll/tutorials/ORDMTutorial/player.htm

Особенности использования WriteBack

1. При изменении файла xml с SQL скриптами - BI не нужно перегружать для Windows системы. Достаточно перезагрузить метаданные
2. Обязательно должны быть заполнены блоки insert и update. Почему оно иногда берет одно - иногда другое, непонятно
3. Если в SQL указаны номера колонок, а не их имена, то имена должны соответствовать номерам в представлении таблица, а не закладке - критерии.
4. Если нужно указывать названия колонок, то их надо указать в xml отчета в параметре ID и использовать в скриптах: @{имя}. Если по номеру колонки: то @1.
5. В скриптах можно запускать блоки процедур!
6. Права на WriteBack можно давать и на уровне привелегий web-каталога. По умолчанию - они заблокированы
Пример XML:

 <?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1">
   <WebMessageTable lang="en-us" system="WriteBack" table="Messages">
      <WebMessage name="WriteBackCoef">
         <XML>
            <writeBack connectionPool="Budget Planning WriteBack">
              <update>UPDATE BUDGET.D_KOEF_VALUE SET KOEF_VALUE = @3 WHERE KOEF_SAP_CODE =  @1 </update>
            </writeBack>
         </XML>
      </WebMessage>
      <WebMessage name="WriteBackFact">
         <XML>
            <writeBack connectionPool="Budget Planning WriteBack">
          <insert>UPDATE BUDGET.F_FACT_VALUE_MANUAL SET
        MONTH_01 = @{c2},
        MONTH_02 = @{c3},
        MONTH_03 = @{c4}, 
        MONTH_04 = @{c5},
        MONTH_05 = @{c6},
        MONTH_06 = @{c7},
        MONTH_07 = @{c8},
        MONTH_08 = @{c9},
        MONTH_09 = @{c10},
        MONTH_10 = @{c11},
        MONTH_11 = @{c12},
        MONTH_12 = @{c13}
        WHERE ID='@{c1}'</insert>
              <update>UPDATE BUDGET.F_FACT_VALUE_MANUAL SET
        MONTH_01 = @{c2},
        MONTH_02 = @{c3},
        MONTH_03 = @{c4}, 
        MONTH_04 = @{c5},
        MONTH_05 = @{c6},
        MONTH_06 = @{c7},
        MONTH_07 = @{c8},
        MONTH_08 = @{c9},
        MONTH_09 = @{c10},
        MONTH_10 = @{c11},
        MONTH_11 = @{c12},
        MONTH_12 = @{c13}
        WHERE ID='@{c1}'</update>
            </writeBack>
         </XML>
      </WebMessage>
<WebMessage name="AddBudgetPeriod">
         <XML>
            <writeBack connectionPool="Budget Planning WriteBack">
          <insert>declare
 v_result varchar2(500);
begin
  azeri_utils.create_new_budget_period(@2, 'AUTO', 'Добавить',  v_result);
commit;
 end;</insert>
              <update>declare
 v_result varchar2(500);
begin
  azeri_utils.create_new_budget_period(@2, 'AUTO', 'Добавить',  v_result);
commit;
 end;</update>
            </writeBack>
         </XML>
      </WebMessage>
<WebMessage name="DeleteBudgetPeriod">
         <XML>
            <writeBack connectionPool="Budget Planning WriteBack">
          <insert>declare
 v_result varchar2(500);
begin
  azeri_utils.create_new_budget_period(@2, 'AUTO', 'Удалить',  v_result);
commit;
 end;</insert>
              <update>declare
 v_result varchar2(500);
begin
  azeri_utils.create_new_budget_period(@2, 'AUTO', 'Удалить',  v_result);
commit;
 end;</update>
            </writeBack>
         </XML>
      </WebMessage>
  <WebMessage name="ChangeStatus">
         <XML>
            <writeBack connectionPool="Budget Planning WriteBack">
          <insert>declare
 v_result varchar2(500);
begin
  azeri_utils.update_status(@1, '@2', '@4', 'AUTO',  v_result);
 end;</insert>
              <update>declare
 v_result varchar2(500);
begin
  azeri_utils.update_status(@1, '@2', '@4', 'AUTO',  v_result);
 end;</update>
            </writeBack>
         </XML>
      </WebMessage>
   </WebMessageTable>
</WebMessageTables>

Клиент Oracle

http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html

Файлы, которые надо удалять

FMW/instances/instance1/diagnostics/logs

\user_projects\domains\bifoundation_domain\servers\AdminServer\logs

\user_projects\domains\bifoundation_domain\servers\bi_server1\logs

\user_projects\domains\bifoundation_domain\servers\AdminServer\cache

\user_projects\domains\bifoundation_domain\servers\AdminServer\tmp

\user_projects\domains\bifoundation_domain\servers\bi_server1\cache

\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp

Драйвера ODBC

[ 19 сентября 2016 г. 7:31 ] Светлана: подскажи пожалуйста, как мне открыть ODBC 64
[ 19 сентября 2016 г. 7:34 ] Денис Воробьёв: минутку
[ 19 сентября 2016 г. 7:36 ] Денис Воробьёв: %systemdrive%\Windows\System32
[ 19 сентября 2016 г. 7:38 ] Денис Воробьёв: %systemdrive%\Windows\System32\Odbcad32.exe
[ 19 сентября 2016 г. 7:39 ] Денис Воробьёв: в Run набери
[ 19 сентября 2016 г. 7:39 ] Денис Воробьёв: или через проводник
[ 19 сентября 2016 г. 7:39 ] Денис Воробьёв: 32-разрядной версии файл Odbcad32.exe находится в папке %systemdrive%\Windows\SysWoW64
[ 19 сентября 2016 г. 7:39 ] Светлана: работает через проводник
[ 19 сентября 2016 г. 7:40 ] Светлана: как все запутано ))))
[ 19 сентября 2016 г. 7:40 ] Светлана: спасибо!

Проверка на число

select case when str is null then 'number' else 'not number' end
from (
select translate('0495756', '_0123456789', '_') str from dual
)



select case when translate('049575f6', '_0123456789', '_') is null then cast('049575f6' as number) else null end  from dual

Отличный курс по Oracle SQL/PLSQL

http://www.firststeps.ru/sql/oracle/oracle1.html

Текущая дата в приглашении по умолчанию

или переменная: @{system.currentTime}