Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Multidimensional analysis can be done by giving a SQL.

2025-01-15 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Internet Technology >

Share

Shulou(Shulou.com)06/03 Report--

"move Excel PivotTable to WEB" We take the Excel file dataset as an example to show how to embed Excel PivotTable into my application. Students who love to learn will ask, the Excel PivotTable can use any data obtained from Excel, such as database fetch, can you directly fetch numbers from the database for analysis? Yes,I Can!

My program supports SQL dataset multidimensional analysis of any database, as long as the data source is configured first, and then the programmer clicks on the SQL statement that requires multidimensional analysis, which is flexible and convenient!

All right, let's get back to business, and the text will begin right away!

Define physical data sources

The first task when adding a SQL dataset is to define the physical data source to be used in [web Application Root Directory] / WEB-INF/raqsoftConfig.xml.

... …… ... Interface add SQL dataset

After the physical data source is created, the next most important step is to add the SQL dataset. Just select the physical data source, write the SQL statement, click to query the data, cache it in the file and save it to ok! Yes, it's so convenient!

After the dataset is created, everything is ready for the east wind, and then you can begin the experience of multidimensional analysis.

Click [add report], select the dataset, fill in the report name [OK], and get it done!

Now that you have data sets and analysis indicators, you can arbitrarily drag and drop the indicators you want to query, and the analysis of SQL data sets can also jump from stand-alone to WEB environment like file analysis. does it feel good again? Ha ha

Drag and drop a few indicators to find the feel:

Huh? Why are the names listed in code, not in Chinese? It doesn't feel friendly! Students, this question is good, in fact, the answer is very simple, the analysis of the report column name is the field name of the SQL dataset, so as long as the field name returned by the SQL dataset is Chinese!

Use AS to return Chinese field names

After modifying the dataset SQL, click to re-query the data, cache the file, add the report again, and find that the field name has become Chinese!

How, look comfortable, accidentally get to a new skill! However, after reading the above results, students who like to ask questions may have an idea again: how does the department display the number? I don't know what this 0123 represents, so how to analyze it? Haha, the editor guessed your idea. I was prepared for it. Tut-tut.

LEFT JOIN got the number code display.

Displaying the department number as the department name, which varies, depends on the SQL statement of the SQL dataset. Through LEFT JOIN to do multi-table association can be easily done!

All right, through the above study has not found that in fact, the data set is all determined by the SQL statement, as long as the SQL statement here conforms to the SQL standard syntax on the line, for example, you can rename the field through AS, LEFT JOIN multi-table association to achieve name display, and so on, in a word: everything to SQL!

Using Tag tags to add SQL datasets

In addition to the above way of adding SQL datasets to the interface, it can also be added through the Tag tag.

The use of SQL datasets and file datasets in Tag tags is more or less the same, except that the attributes are slightly different.

Add the SQL dataset to the Tag tag:

Add the SQL dataset to the Tag tag, and when you visit the multidimensional analysis page, the dataset defaults to the initial dataset, and the result set is displayed directly in the page in detail.

Do the students who pay attention to details find out again? How to use the Tag tag to add a dataset without clicking "query data, cache file" results can be displayed normally? Is there any difference between the two ways to add datasets?

Haha, confused, the throwing really worked.

In fact, the underlying data processing mechanism of the two methods is the same, the only difference is that adding data sets with Tag tags can be understood as directly setting the initial data set for the analysis page, at this time the program will automatically generate temporary files, and then store the calculation results, so that you do not have to take the trouble to cache again. However, if you need to continue to modify or add new datasets to the initial dataset on the page, you have to recreate the temporary file by clicking "query data, cache file" every time you modify the SQL statement. The default temporary file naming convention is a combination of time milliseconds.

Well, is the SQL dataset super convenient to use in my own application? Whether you add it in the page dataset menu or in the Tag tag, you first let the aggregator fetch the number through SQL, and then cache the returned result set in a temporary file, and then the multidimensional analysis on the page is based on this temporary data file. In this way, using the computing power of the aggregator, you can do data analysis actions such as drag-and-drop grouping, aggregation, filtering, and so on.

The editor still needs to nag a few more words about the life cycle of cache files, which can be divided into the following three points:

1. When the parent directory path of a cache file contains the word "temp", such as / Raqsofttemp/.txt or / temp/.txt, the program will automatically clean up the cache file when Session times out.

2. When generating a cache file, if it is found that the cache file does not exist, the cache file will be automatically regenerated according to the configuration of the dataset.

3. If there is no temp in the cache file path, the cache files will not be deleted, and the life cycle of these cache files will be managed by the customer's system.

The suffix of the cache file is .txt. If you are smart, you will be able to guess that the report is mediated by TXT files, but the performance of TXT files is a little poor, and the data types are not so accurate, and in some cases there will be minor mistakes. As a matter of fact, the data read from SQL is already in binary format, so it is a bit time-consuming and thankless to convert it into text.

So, what else can we do?

The dry report provides cache files in binary format, but at this point I have to say that this function needs to be supported by a fee-based integrated aggregator. If you already have this functional component, it will be simple.

Binary cache files improve performance

Open [WEB Application Root Directory] / raqsoft/guide/jsp/olap.jsp and add the following JS API script:

GuideConf.dataFileType = 'binary'; / / binary is a binary file that is read as a cursor and can support datasets that are out of memory; the default for this attribute is the text text file type.

All right, once set up, the temporary file will be saved as a binary file when caching.

So, isn't it super easy? So convenient? Love it?

But then again, the above method of caching files is fine when the amount of data is small, but sometimes we want to take advantage of the computing power of the database (after all, the database can also be distributed in clusters, etc.), what should we do?

Don't panic, dry the report to help you!

Non-cached SQL dataset

Add a SQL dataset that does not need to cache data in the JSP script. It is not difficult to operate, just type the SQL statement that makes the original query first.

Pass the SQL statement to the database for query. After the query, the analysis interface will directly list the fields in the result set and drag and drop them.

Take a multi-table query as an example, and add JS API in [demo Application Root Directory] / raqsoft/guide/jsp/olap.jsp to set it.

GuideConf.sqlId= ""; / / specify sqlidvar sqlDatasets = [{sqlId: "sqlId1" / / specify the dataset name, which cannot be repeated, dataSource: "dqldemo" / / specify the data source used by the SQL statement, sql: "SELECT EMPLOYEE.Name as employee name, DEPARTMENT.NAME as department, EMPLOYEE.COUNTRY as country FROM EMPLOYEE LEFT JOIN DEPARTMENT ON EMPLOYEE.DEPTID=DEPARTMENT.ID" / / specify dataset SQL statement, fields:null},... [] ... ...

After the script is added, you only need to pass the dataset name to the sqlId parameter in URL when you access the analysis page, as shown in the following figure, using the dataset named sqlId1:

Once the SQL dataset is set as the data source, it can be analyzed against the dataset, but the analysis process does not preload the data from the dataset as cached into the file type dataset, but queries the database in real time by appending more query conditions, grouping, and aggregation clauses to the SQL, such as:

Select F1, sum (f2) from (${original sql}) t where... Group by... . Having... .

Simple SQL improves performance

Based on the above approach, students who have high requirements for performance will probably be in the mood, and many DB will not be optimized, so it will be very slow and the experience will not be good.

Haha, don't hurry, in order to improve performance, we also have more intelligent means to automatically improve performance for simple SQL. The so-called simple SQL is a single-table SQL statement without grouping. SQL reorganizes the SQL after it has been parsed, rather than using slower subquery statements. However, when the SQL contains JOIN, grouping, subquery, UNION and other complex query functions, because it can not be disassembled, the program will automatically choose the subquery method for processing.

Set a simple SQL with no grouping in a single table:

GuideConf.sqlId= ""; / / specify sqlidvar sqlDatasets = [{sqlId: "sqlId1" / / specify the dataset name, which cannot be repeated, dataSource: "dqldemo" / / specify the data source used by the SQL statement, sql: "SELECT * from customer" / / specify the dataset SQL statement, fields:null},... [] ... ...

Access the analysis page and drag the metric, as shown in the following figure:

The SQL statement generated by the final drag based on the original SQL "select * from customer" as in the example above is: SELECT region, city, count (customer ID) customer ID count FROM customer t GROUP BY region, city.

Well, this is the end of the introduction to the multidimensional analysis of SQL datasets, and now let's review the mental journey:

We can see that we have a good solution from a small amount of data that can be cached to a large amount of data that makes use of the computing power of the database, and also provides the corresponding means to improve performance. However, our WEB multi-dimensional analysis ability is more than that, for example, we can also modify the table style of report analysis, read dimension and index information through modeling, and so on. If you want to learn more about multi-dimensional analysis of reports, welcome to visit the College of dry Sciences (c.raqsoft.com.cn).

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Internet Technology

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report