================================

All say BI, what OLAP, what ROLAP,MOLAP, But how to base on RDBMS Realization , I don't say much about it .

How to do it? :

1. Through multidimensional analysis model , Store metadata

2. Create topic temporary table dynamically

3. According to the metadata of multidimensional model , assemble SQL, Create a temporary table , Insert data into temporary table

================================

Program usage SQL All aimed at Select,Update,Delete

But it's rarely used create table,drop table, In fact, in database analysis , This is the scene

The use of database original view in development ...., There are also scenes , For example, check the primary foreign key of the table

================================

Generated temporary table structure -- The essence is the understanding and personal feeling of this temporary table

DIM_*: These are dimensions

DIM_*_DATA: It's a specific measure or indicator

This table is based on the user's multidimensional analysis model and user-defined table , He will automatically generate .

Query statement :

SELECT DIM_27_CODE AS DIM_27_CODE,
MAX(DIM_27_DISPLAY) AS DIM_27_DISPLAY,
DIM_29_CODE AS DIM_29_CODE,
MAX(DIM_29_DISPLAY) AS DIM_29_DISPLAY,
DIM_DATE_CODE AS DIM_DATE_LAST_CODE,
SUM(DIM_95_DATA) AS DIM_95_DATA
FROM vhbiyf.rep_temp_report_47
WHERE DIM_DATE_CODE IN ('201409')
AND DIM_DATE_LEVEL = 'YEAR_MONTH'
AND DIM_21_CODE IN ('1000')
AND DIM_27_CODE IN ('1', '2', '3', '1', '2', '3')
GROUP BY
DIM_27_CODE,
DIM_29_CODE,
DIM_DATE_CODE
ORDER BY
DIM_27_CODE ASC,
DIM_29_CODE ASC

Page request :

To create a temporary table SQL:

CREATE TABLE temp_DIM_95
(
DIM_95_fint_year VARCHAR(4000),
DIM_95_fint_month VARCHAR(4000),
DIM_95_a_id VARCHAR(4000),
DIM_95_p_id VARCHAR(4000),
DIM_95_comp_code VARCHAR(4000),
DIM_95_num NUMBER(18, 6)
);
CREATE TABLE temp_DIM_27
(
DIM_95_fint_year VARCHAR(4000),
DIM_95_fint_month VARCHAR(4000),
DIM_95_a_id VARCHAR(4000),
DIM_95_p_id VARCHAR(4000),
DIM_95_comp_code VARCHAR(4000),
DIM_27_name VARCHAR(4000),
DIM_27_id VARCHAR(4000),
DIM_95_num NUMBER(18, 6)
);
CREATE TABLE temp_DIM_29
(
DIM_95_fint_year VARCHAR(4000),
DIM_95_fint_month VARCHAR(4000),
DIM_95_a_id VARCHAR(4000),
DIM_95_p_id VARCHAR(4000),
DIM_95_comp_code VARCHAR(4000),
DIM_27_name VARCHAR(4000),
DIM_27_id VARCHAR(4000),
DIM_29_name VARCHAR(4000),
DIM_29_id VARCHAR(4000),
DIM_95_num NUMBER(18, 6)
);
CREATE TABLE temp_DIM_21
(
DIM_95_fint_year VARCHAR(4000),
DIM_95_fint_month VARCHAR(4000),
DIM_95_a_id VARCHAR(4000),
DIM_95_p_id VARCHAR(4000),
DIM_95_comp_code VARCHAR(4000),
DIM_27_name VARCHAR(4000),
DIM_27_id VARCHAR(4000),
DIM_29_name VARCHAR(4000),
DIM_29_id VARCHAR(4000),
DIM_21_comp_name VARCHAR(4000),
DIM_21_comp_code VARCHAR(4000),
DIM_95_num NUMBER(18, 6)
);
INSERT INTO temp_DIM_95
SELECT DIM_95.fint_year AS DIM_95_fint_year,
DIM_95.fint_month AS DIM_95_fint_month,
DIM_95.a_id AS DIM_95_a_id,
DIM_95.p_id AS DIM_95_p_id,
DIM_95.comp_code AS DIM_95_comp_code,
SUM(DIM_95.num) AS DIM_95_num
FROM (
SELECT YYB_ORDER.*
FROM YYB_ORDER YYB_ORDER
) DIM_95
WHERE DIM_95.fint_year = '2014'
AND DIM_95.fint_month = '09'
GROUP BY
DIM_95.fint_year,
DIM_95.fint_month,
DIM_95.a_id,
DIM_95.p_id,
DIM_95.comp_code;
INSERT INTO temp_DIM_27
SELECT DIM_95.DIM_95_fint_year AS DIM_95_fint_year,
DIM_95.DIM_95_fint_month AS DIM_95_fint_month,
DIM_95.DIM_95_a_id AS DIM_95_a_id,
DIM_95.DIM_95_p_id AS DIM_95_p_id,
DIM_95.DIM_95_comp_code AS DIM_95_comp_code,
DIM_27.name AS DIM_27_name,
DIM_27.id AS DIM_27_id,
SUM(DIM_95.DIM_95_num) AS DIM_95_num
FROM temp_DIM_95 DIM_95
INNER JOIN (
SELECT YYB_AREA.*
FROM YYB_AREA YYB_AREA
) DIM_27
ON (1 = 1 AND DIM_27.id = DIM_95.DIM_95_a_id)
GROUP BY
DIM_95.DIM_95_fint_year,
DIM_95.DIM_95_fint_month,
DIM_95.DIM_95_a_id,
DIM_95.DIM_95_p_id,
DIM_95.DIM_95_comp_code,
DIM_27.name,
DIM_27.id;
INSERT INTO temp_DIM_29
SELECT DIM_27.DIM_95_fint_year AS DIM_95_fint_year,
DIM_27.DIM_95_fint_month AS DIM_95_fint_month,
DIM_27.DIM_95_a_id AS DIM_95_a_id,
DIM_27.DIM_95_p_id AS DIM_95_p_id,
DIM_27.DIM_95_comp_code AS DIM_95_comp_code,
DIM_27.DIM_27_name AS DIM_27_name,
DIM_27.DIM_27_id AS DIM_27_id,
DIM_29.name AS DIM_29_name,
DIM_29.id AS DIM_29_id,
SUM(DIM_27.DIM_95_num) AS DIM_95_num
FROM temp_DIM_27 DIM_27
INNER JOIN (
SELECT YYB_PRO.*
FROM YYB_PRO YYB_PRO
) DIM_29
ON (1 = 1 AND DIM_29.id = DIM_27.DIM_95_p_id)
GROUP BY
DIM_27.DIM_95_fint_year,
DIM_27.DIM_95_fint_month,
DIM_27.DIM_95_a_id,
DIM_27.DIM_95_p_id,
DIM_27.DIM_95_comp_code,
DIM_27.DIM_27_name,
DIM_27.DIM_27_id,
DIM_29.name,
DIM_29.id;
INSERT INTO temp_DIM_21
SELECT DIM_29.DIM_95_fint_year AS DIM_95_fint_year,
DIM_29.DIM_95_fint_month AS DIM_95_fint_month,
DIM_29.DIM_95_a_id AS DIM_95_a_id,
DIM_29.DIM_95_p_id AS DIM_95_p_id,
DIM_29.DIM_95_comp_code AS DIM_95_comp_code,
DIM_29.DIM_27_name AS DIM_27_name,
DIM_29.DIM_27_id AS DIM_27_id,
DIM_29.DIM_29_name AS DIM_29_name,
DIM_29.DIM_29_id AS DIM_29_id,
DIM_21.comp_name AS DIM_21_comp_name,
DIM_21.comp_code AS DIM_21_comp_code,
SUM(DIM_29.DIM_95_num) AS DIM_95_num
FROM temp_DIM_29 DIM_29
INNER JOIN (
SELECT SYS_COMPANY.*
FROM SYS_COMPANY SYS_COMPANY
) DIM_21
ON (1 = 1 AND DIM_21.comp_code = DIM_29.DIM_95_comp_code)
WHERE DIM_21.comp_code IN ('1000')
GROUP BY
DIM_29.DIM_95_fint_year,
DIM_29.DIM_95_fint_month,
DIM_29.DIM_95_a_id,
DIM_29.DIM_95_p_id,
DIM_29.DIM_95_comp_code,
DIM_29.DIM_27_name,
DIM_29.DIM_27_id,
DIM_29.DIM_29_name,
DIM_29.DIM_29_id,
DIM_21.comp_name,
DIM_21.comp_code;
SELECT DIM_21.DIM_27_id AS DIM_27_CODE,
MAX(DIM_21.DIM_27_name) AS DIM_27_DISPLAY,
DIM_21.DIM_29_id AS DIM_29_CODE,
MAX(DIM_21.DIM_29_name) AS DIM_29_DISPLAY,
'201409' AS DIM_DATE_CODE,
'YEAR_MONTH' AS DIM_DATE_LEVEL,
DIM_21.DIM_21_comp_code AS DIM_21_CODE,
MAX(DIM_21.DIM_21_comp_name) AS DIM_21_DISPLAY,
SUM(DIM_21.DIM_95_num) AS DIM_95_DATA
FROM temp_DIM_21 DIM_21
GROUP BY
DIM_21.DIM_27_id,
DIM_21.DIM_29_id,
DIM_21.DIM_21_comp_code
ORDER BY
DIM_21.DIM_27_id ASC,
DIM_21.DIM_29_id ASC,
DIM_21.DIM_21_comp_code ASC;DROP TABLE temp_DIM_95;DROP TABLE temp_DIM_27;
DROP TABLE temp_DIM_29;DROP TABLE temp_DIM_21;

====================================================================================

-- Create table cache summary data
CREATE TABLE temp_4690DIM_2417
(
DIM_2417_fint_year VARCHAR(4000),-- year
DIM_2417_fint_month VARCHAR(4000),-- month
DIM_2417_profit_state VARCHAR(4000),-- earnings ===
DIM_2417_comp_code VARCHAR(4000),-- Company
DIM_2417_disease_code_vh VARCHAR(4000) -- Disease code
) ;

CREATE TABLE temp_4690DIM_2408
(
DIM_2417_fint_year VARCHAR(4000),
DIM_2417_fint_month VARCHAR(4000),
DIM_2417_profit_state VARCHAR(4000),
DIM_2417_comp_code VARCHAR(4000),
DIM_2408_name VARCHAR(4000),-- Revenue name
DIM_2408_code VARCHAR(4000),-- Revenue name
DIM_2417_disease_code_vh VARCHAR(4000)
) ;

CREATE TABLE temp_4690DIM_21
(
DIM_2417_fint_year VARCHAR(4000),
DIM_2417_fint_month VARCHAR(4000),
DIM_2417_profit_state VARCHAR(4000),
DIM_2417_comp_code VARCHAR(4000),
DIM_2408_name VARCHAR(4000),
DIM_2408_code VARCHAR(4000),
DIM_21_comp_name VARCHAR(4000),-- Name of the company
DIM_21_comp_code VARCHAR(4000),-- Unit code
DIM_2417_disease_code_vh VARCHAR(4000)
) ;

INSERT INTO temp_4690DIM_2417
SELECT
DIM_2417.fint_year AS DIM_2417_fint_year,-- year
DIM_2417.fint_month AS DIM_2417_fint_month,-- month
DIM_2417.profit_state AS DIM_2417_profit_state,-- Profit and loss
DIM_2417.comp_code AS DIM_2417_comp_code,-- Company
DIM_2417.disease_code AS DIM_2417_disease_code_vh -- Case code
FROM
(
SELECT
VIEW_DISE_QYBZSY.*
FROM
VIEW_DISE_QYBZSY VIEW_DISE_QYBZSY -- The original table
) DIM_2417
WHERE
DIM_2417.fint_year = '2013' -- The only unit during caching
AND DIM_2417.fint_month = '12'
GROUP BY
DIM_2417.fint_year,-- year
DIM_2417.fint_month,-- month
DIM_2417.profit_state,-- Profit and loss
DIM_2417.comp_code,-- Company
DIM_2417.disease_code;-- Disease code

INSERT INTO temp_4690DIM_2408
SELECT
DIM_2417.DIM_2417_fint_year AS DIM_2417_fint_year,
DIM_2417.DIM_2417_fint_month AS DIM_2417_fint_month,
DIM_2417.DIM_2417_profit_state AS DIM_2417_profit_state,
DIM_2417.DIM_2417_comp_code AS DIM_2417_comp_code,
DIM_2408.name AS DIM_2408_name,
DIM_2408.code AS DIM_2408_code,
DIM_2417.DIM_2417_disease_code_vh AS DIM_2417_disease_code_vh
FROM
temp_4690DIM_2417 DIM_2417
INNER JOIN (
SELECT
VIEW_DISE_PROFIT_TYPE.*
FROM
VIEW_DISE_PROFIT_TYPE VIEW_DISE_PROFIT_TYPE -- Profit and loss type table
) DIM_2408
ON (1 = 1 AND DIM_2408.code = DIM_2417.DIM_2417_profit_state) -- The relationship between dimensions
GROUP BY
DIM_2417.DIM_2417_fint_year,
DIM_2417.DIM_2417_fint_month,
DIM_2417.DIM_2417_profit_state,
DIM_2417.DIM_2417_comp_code,
DIM_2408.name,
DIM_2408.code,
DIM_2417.DIM_2417_disease_code_vh;

INSERT INTO temp_4690DIM_21
SELECT
DIM_2408.DIM_2417_fint_year AS DIM_2417_fint_year,
DIM_2408.DIM_2417_fint_month AS DIM_2417_fint_month,
DIM_2408.DIM_2417_profit_state AS DIM_2417_profit_state,
DIM_2408.DIM_2417_comp_code AS DIM_2417_comp_code,
DIM_2408.DIM_2408_name AS DIM_2408_name,
DIM_2408.DIM_2408_code AS DIM_2408_code,
DIM_21.comp_name AS DIM_21_comp_name,
DIM_21.comp_code AS DIM_21_comp_code,
DIM_2408.DIM_2417_disease_code_vh AS DIM_2417_disease_code_vh
FROM
temp_4690DIM_2408 DIM_2408
INNER JOIN (
SELECT
SYS_COMPANY.*
FROM
SYS_COMPANY SYS_COMPANY -- Affiliated units
) DIM_21
ON (1 = 1 AND DIM_21.comp_code = DIM_2408.DIM_2417_comp_code) -- The relationship between dimensions
WHERE
DIM_21.comp_code IN ('01') -- Unit code
GROUP BY
DIM_2408.DIM_2417_fint_year,
DIM_2408.DIM_2417_fint_month,
DIM_2408.DIM_2417_profit_state,
DIM_2408.DIM_2417_comp_code,
DIM_2408.DIM_2408_name,
DIM_2408.DIM_2408_code,
DIM_21.comp_name,
DIM_21.comp_code,
DIM_2408.DIM_2417_disease_code_vh;

--- After getting the final result here, summarize it according to the data for the last time
SELECT
DIM_21.DIM_2408_code AS DIM_2408_CODE,-- Unit code
MAX(DIM_21.DIM_2408_name) AS DIM_2408_DISPLAY,-- name
'201312' AS DIM_DATE_CODE,-- Automatically add year
'YEAR_MONTH' AS DIM_DATE_LEVEL,-- Period type
DIM_21.DIM_21_comp_code AS DIM_21_CODE,-- Unit code -- Correlation dimension
MAX(DIM_21.DIM_21_comp_name) AS DIM_21_DISPLAY,-- Name of the company
COUNT(DISTINCT DIM_21.DIM_2417_disease_code_vh) AS DIM_2417_DATA -- Data summary
FROM
temp_4690DIM_21 DIM_21
GROUP BY
DIM_21.DIM_2408_code,
DIM_21.DIM_21_comp_code
ORDER BY
DIM_21.DIM_2408_code ASC,
DIM_21.DIM_21_comp_code ASC;

DROP TABLE temp_4690DIM_2417;DROP TABLE temp_4690DIM_2408;DROP TABLE -- Delete table
temp_4690DIM_21;

-- Automatically generated query statements ( In the future, the query will only be conducted through this statement )
SELECT DIM_DATE_CODE as DIM_DATE_CODE,-- Time
MAX(DIM_DATE_LEVEL) as DIM_DATE_LEVEL,-- Period type
DIM_2408_CODE as DIM_2408_CODE,-- Company
MAX(DIM_2408_DISPLAY) as DIM_2408_DISPLAY,
DIM_DATE_CODE as DIM_DATE_LAST_CODE,-- Time dimension
SUM(DISTINCT DIM_2417_DATA) as DIM_2417_DATA -- Summary data
FROM bi.rep_temp_report_3221
WHERE DIM_DATE_CODE IN ('201312', '201212') -- Page time filtering
AND DIM_DATE_LEVEL = 'YEAR_MONTH'-- Filter
AND DIM_21_CODE IN ('01')-- Filter
GROUP BY DIM_DATE_CODE, DIM_2408_CODE, DIM_DATE_CODE
ORDER BY DIM_DATE_CODE ASC, DIM_2408_CODE ASC

====================================================================================

BI To put it bluntly , That is, we can make some reports as needed ( There are special business analysts in the customer department ), This is it. BI The needs of .

But for our development, how to achieve ??????

Theoretically , Now there are some multidimensional analysis models , It's a theoretical solution , In development , The coding implementation analyzes the multidimensional model to form metadata metedata, The most important thing is . The second is data storage .

above SQL The essence is based on The metadata of the multidimensional model then forms SQL( We used to face SQL Developing reports ), Then extract the data and save it to the temporary table , It's also a materialization of data .

====================================================================================

Page design requirements :

Form linkage

Icon linkage

The chart is detailed

Index custom formula

be based on RDBMS Of BI More articles on Design

  1. be based on RDBMS Of OLAP Solutions for

    BI How to develop a project : understand OLAP Analysis method of : Multidimensional modeling and analysis of data , That is to design your own data cube , Then the program automatically generates the data cube Data cube : 1. Automatic table structure , Just have the columns you need 2. Generate a SQL sentence ( ...

  2. be based on ZedBoard Of Webcam Design ( One ):USB camera (V4L2 Interface ) Image collection of 【 turn 】

    from :http://www.cnblogs.com/surpassal/archive/2012/12/19/zed_webcam_lab1.html I've always wanted to put USB The camera's on Zedboard On , build ...

  3. SOA Practice is based on the design of service bus

    In the above , It mainly introduces SOA The concept of , What is called “ service ”,“ service ” What characteristics should we have . In this , I'll introduce you to SOA A very common design practice of -- Design based on service bus . Design based on service bus Bus based design , Learn from inside the computer ...

  4. be based on Apriori Algorithm Nginx+Lua+ELK Abnormal traffic interception scheme Zheng Yun Based on Yang Haibo's design documents ( turn )

    Zheng Yun Based on Yang Haibo's design documents Founded in 2015/8/13 Last updated on 2015/8/25 key word : Abnormal flow .rate limiting.Nginx.Apriori. Frequent itemsets . A priori algorithm .Lua.ELK This document ...

  5. be based on MATLAB2016b Automatic generation of graphic design Verilog Integral module of language and its application

    In power electronic converter equipment , It's often necessary to calculate power generation , As the power electronic converter equipment is generally high frequency converter equipment , So the calculation of power generation is almost the integration of real-time power , In this case, an integral module will be used . The formula for generating capacity calculation is as follows :Q=∫P. FPGA Because it does not ...

  6. be based on hadoop Of BI framework

    BI System , It is a typical system for enterprises to use data to drive operation .BI The system excavates the data in the process of enterprise operation , Discover the potential risks of enterprises . Provide data support for various decisions of enterprises . Conventional BI Systems are usually built on relational databases . With the increase of business volume ...

  7. be based on FPGA Of DDS Design ( One )

    Recently I've been learning based on FPGA Of DDS Design , Take this opportunity to record the learning process , As my own study notes, I hope it can help my study DDS Little buddy . DDS(Direct Digital Synthesizer) Direct digital synthesizer , this ...

  8. atitit. Starter design based on virtual machine --java starter java Generate exe

    atitit. Starter design based on virtual machine  --java  starter    java Generate exe exe4j   vs  nativej 1. step1 Read configuration file 1 1.1. regular mode   . ...

  9. be based on WPF System framework design (5)-Ribbon Integrate Avalondock 2.0 Implementation of multi document interface design ( Two )

    AvalonDock It's a .NET library , For layout in docking mode (docking) Arrange a series of WPF/WinForm Control . The newly released version has native support MVVM frame .Aero Snap Special effects and better performance . Ava ...

Random recommendation

  1. 【Qt Learning notes 】 Window assembly finishing

    About Qt In the window part study Start today Qt The widget for , Take a look at Qt The magic of , Remember 2012 The winter of that year , I'm still learning Java Well , Now it's basically the same as Java Say goodbye , But for embedded development Qt It's important , I want to take advantage of ...

  2. Android API Guides Learning notes ---Application Fundamentals( One )

    Start today google On the official website API guides , Mainly read Application Fundamentals This chapter , This chapter introduces a App Basic composition of , It consists of four parts . 1.      App ...

  3. hibernate Learning notes 4---HQL、 Extraction implementation of general methods

    One . Extraction implementation of general methods because hibernate All operations of adding, deleting, modifying and checking in are object-oriented , So add, delete, change, search and extract into a general method , In order to meet the operation of adding, deleting, modifying and querying different tables , simplify jdbc Code . Specific examples are as follows : package cn ...

  4. Android init Process Overview

    init process , Its program is in the root file system , stay kernle After self starting , Among them  start_kernel  Function to mount the root file system to / After the directory , stay  rest_init  Function through  kernel_thread(ker ...

  5. capitalize() stay Python Chinese meaning

    Python by string Object provides a way to convert case :upper() and lower(). More than that ,Python We've also been given capital letters , The rest are in lower case capitalize() Method , And all the first words ...

  6. ViewPager Examples of usage of

    Preface : I've been working on a project recently , File manager , It can be selected by sliding in the main interface : mobile phone , Memory card , Different interfaces in the cloud , So we use ViewPager. The early stage ,ViewPager Well done. , Corresponding Adapter It's also written , The test passed ...

  7. Chapter one introduction --《 Design patterns - The foundation of reusable object-oriented software 》Erich Gamma

    Chapter one introduction This chapter is mainly to let us understand what design patterns are for , Pattern classification , How design patterns solve design problems and several common methods of software reuse in object-oriented design . 1. What is design pattern ? Personal understanding generalizes , Design patterns are abstractions of a class of problems ...

  8. c# Summary of interview questions (1)

    c# Summary of interview questions From: https://www.cnblogs.com/suzhiyong1988/p/5069385.html   The following reference answers are just to help you understand , Don't carry it , Interview questions . The written test questions are ever-changing ...

  9. Android JNI Study ( Four ): Interface method table & Base Api & Exception Api

    In this article, let's summarize JNI Provides the function list and the related function table . Be careful : Please note the use of the term “ must ” To describe the right JNI The limitations of programmers . for example , When you see something JNI Function must receive a non NULL Object time , It is your responsibility to ensure that NULL Pass it on to ...

  10. Father div Transparency does not affect div transparency

    Set up div If the parent is set for transparency div Transparency (opacity=0.8), Then div The transparency has changed , And set up div Transparency doesn't work . In this case, you can use rgba To set up the father div Transparency : ...