<< Click to Display Table of Contents >> Current:  Appendix > Page Introduction > Data Set Page  SQL Data Set Page

On the displayed page, you can select the saved conntion and edit SQL Data Set by choosing Select Table or View or New Custom .

[Connection] When you select the user-defined data source, you can configure the corresponding data source. You can also selected created data sources from the existing data source list.

[Edit Connection] After selecting the data source, click this icon to quickly enter the Create Connection module and open the currently selected data source.

[Select Table or View][New Custom]The user decides whether to directly use the table or view under the database or write the SQL statement by himself or herself.

[Tables] All tables,views,Analysis View(HANA)、Calculation View（HANA）、Attribute（HANA）、Cube(Kylin) in the database are listed after you refresh the list. After right clicking to refresh, tables, views and storage process under the data source can be searched. For detailed usage, see the introduction of the data source search section. If the driver and database provided by Yonghong Z-Suite are used, all data sets are listed.

[SQL Statement] Enter a script statement to query the data in the database.

If the Access database is used, the table name generated after refresh has a "$" at the end. When writing SQL statements, you need to quote the table using double quotation marks, for example, select Sales from "Coffee_chain$." If the driver and database provided by Yonghong Z-Suite are used, follow the SQL syntax of Yonghong Z-Suite while compiling SQL statements.

•·Add double quotation marks when the referenced data set contains lower levels, for example, select Sales from "cloud/test.clqry." Double quotation marks are not required when no subset is contained, for example, select Sales from test.clqry.

•·Quotation marks are required when referencing keywords. Assume that the a.clqry data set contains the Date field. The Date field needs to be quoted by double quotation marks because it is a keyword in the database, for example, select "Date" from test.clqry.

•·Note that when the assignment type is a string, you need to use single quotes instead of double quotes. For example, select Nation from test.clqry where Nation='China'

➢Note:  ORDER BY statement is not supported in the Sybase database.

[In-database Computation] It is selected by default, which indicates that SQL will be encapsulated during data query. If it is not selected, later operations for the database will not be performed in the database.

If "In-database Computation" is not selected, the original orange mark column that affects performance in Metadata area will turn to black, and the data set will be put in post processing. For the current data set, the object that invokes the data set will also be affected.

[Refresh Metadata]When the user first clicks to refresh the metadata button, the data set edit area will shrink automatically, overlay the data area, display the state of the detail data, click the contraction control, the data set edit area and the data area will be displayed at the same time, and then open again, and also display the state of the detail data.

 

If the columns referenced in expressions and filters change, "If Data Set's column changed, the expression columns, hierachies or row filter may be unavailable, do you want to clear them?" is displayed when you refreshing metadata. If no change happens, the message is not displayed. The default character, string, Boolean, time, data and timestamp types of data segments of the product are stored in the dimension directory, and other types of data segments are stored in the measure directory.