CN103902625A - Method and device for determining time dimension table data - Google Patents
Method and device for determining time dimension table data Download PDFInfo
- Publication number
- CN103902625A CN103902625A CN201210587732.6A CN201210587732A CN103902625A CN 103902625 A CN103902625 A CN 103902625A CN 201210587732 A CN201210587732 A CN 201210587732A CN 103902625 A CN103902625 A CN 103902625A
- Authority
- CN
- China
- Prior art keywords
- field
- date
- gregorian calendar
- dimension table
- time dimension
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/248—Presentation of query results
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides a method and device for determining time dimension table data. The method comprises the steps that a time dimension table is created, wherein the time dimension table includes fields of western calendar dates and fields of lunar calendar dates corresponding to the western calendar dates; data of all western calendar dates from the start date to the end date, and data of the lunar calendar dates corresponding to the western calendar dates are written to the time dimension table through date functions.
Description
Technical field
The present invention relates to Intellectual analysis service field, particularly relate to a kind of method and device of definite time dimension table data.
Background technology
In existing Intellectual analysis service item, while adding time dimension table, do not set hierarchical structure, inquiry year that can only be independent or the moon or week, when generating report forms inquiry, carry out select time by multiple drop-down list conditions.
In addition, in the time dimension table of prior art, set of time is not comprehensive, there is no the field such as the lunar calendar, ten days, and report data amount is large, search efficiency is low, date time parameter arranges detailed not, extension difficulty, so in use, efficiency is very low, add algorithm and just can obtain ten days equal time section, and in prior art, do not calculate the algorithm of the lunar calendar.
Summary of the invention
The invention provides a kind of method and device of definite time dimension table data, in order to solve in prior art set of time not comprehensively, there is no a lunar calendar field, and in prior art, do not calculate the algorithm of the lunar calendar yet, cause the technical matters that report data amount is large, search efficiency is low.
One aspect of the present invention provides a kind of method of definite time dimension table data, and described method comprises: creation-time dimension table, and described dimension table comprises the field of the field on Gregorian calendar date and lunar date corresponding to described Gregorian calendar date; And utilize date function that from from date to all Gregorian calendar date datas between the Close Date and respectively corresponding lunar date data are write described time dimension table.
Preferably, the field on described Gregorian calendar date comprises the first field, the second field, the 3rd field, the 4th field and the 5th field, described the first field is the field in described year on Gregorian calendar date, described the second field is the field of described half a year on Gregorian calendar date, described the 3rd field is the field in described season on Gregorian calendar date, described the 4th field is the field of the described moon on Gregorian calendar date, and described the 5th field is the field of described Gregorian calendar day, and described method also comprises: described the first field is made as to the first level; Described the second field is made as to the second level; Described the 3rd field is made as to the 3rd level; Described the 4th field is made as to the 4th level; And described the 5th field is made as to layer 5 level.
Preferably, described dimension table also comprises the field in ten days.
Preferably, the described date function that utilizes writes from from date to all Gregorian calendar date datas between the Close Date and respectively corresponding lunar date data described time dimension table and specifically comprises: utilize the first date function to write described Gregorian calendar date data; And utilize the second date function to write described lunar date data.
Preferably, described the second date function is:
declare@Lunardate?nvarchar(30)；
set@Lunardate=dbo.fn_GetLunar('2011-10-9')；
print@Lunardate。
The present invention also provides a kind of device of definite time dimension table data, and described device comprises: creation module, and for creation-time dimension table, described dimension table comprises the field of the field on Gregorian calendar date and lunar date corresponding to described Gregorian calendar date; And writing module, for utilizing date function that from from date to all Gregorian calendar date datas between the Close Date and respectively corresponding lunar date data are write to described time dimension table.
Preferably, the field on described Gregorian calendar date comprises the first field, the second field, the 3rd field, the 4th field and the 5th field, and described the first field is the first level, and described the first field is the field in described year on Gregorian calendar date; Described the second field is the second level, and described the second field is the field of described half a year on Gregorian calendar date; Described the 3rd field is the 3rd level, and described the 3rd field is the field in described season on Gregorian calendar date, described the 4th field is the 4th level, and described the 4th field field that is the described moon on Gregorian calendar date, described the 5th field is layer 5 level, and described the 5th field is the field of described Gregorian calendar day.
Preferably, described dimension table also comprises the field in ten days.
Preferably, described date function comprises the first date function, for writing described Gregorian calendar date data; And the second date function, for writing described lunar date data.
Preferably, described the second date function is:
declare@Lunardate?nvarchar(30)；
set@Lunardate=dbo.fn_GetLunar('2011-10-9')；
print@Lunardate。
Beneficial effect of the present invention is as follows:
In one embodiment of the invention, be provided with the time comprehensively, define multiple Time of Day field, comprise the Gregorian calendar and lunar calendar field, because lunar calendar field is directly defined, institute uses and disuses and recalculates the lunar calendar, in addition, because time parameter arranges in detail, so the demand such as arrange of the selection of various time parameters and each time phase can meet report making time, has improved the search efficiency of form.
Further, one embodiment of the invention adopts time dimension table is set as to hierarchical structure, and while making form, querying condition can be made as one, can show by expanding in layered manner, without the multiple queries condition that arranges of interlock in a condition.
Further, in one embodiment of the invention, utilize self-defining function for time dimension table padding data, thus can Fast Filling data, and especially, in the situation that data volume is larger, method of the present invention has more advantage.
Brief description of the drawings
Fig. 1 is the method flow diagram of determining time dimension table data in one embodiment of the invention;
Fig. 2 is the field schematic diagram of time dimension table in one embodiment of the invention;
Fig. 3 is the schematic diagram of having filled the time dimension table of data in one embodiment of the invention;
Fig. 4 is the method flow diagram that adds time dimension table in one embodiment of the invention;
Fig. 5 be in one embodiment of the invention time dimension table surface chart is set;
Fig. 6 is the functional block diagram of determining the device of time dimension table data in one embodiment of the invention.
Embodiment
One embodiment of the invention provides a kind of method of definite time dimension table data, refers to Fig. 1, and Fig. 1 is this method flow diagram of determining time dimension table data, and as shown in Figure 1, the method comprises:
Step 101: creation-time dimension table, described dimension table comprises the field of the field on Gregorian calendar date and lunar date corresponding to described Gregorian calendar date;
Step 102: utilize date function that from from date to all Gregorian calendar date datas between the Close Date and respectively corresponding lunar date data are write described time dimension table.
In step 101, creation-time dimension table, define multiple Time of Day fields, in the embodiment of the method for Fig. 1, the field of dimension table has only provided the field of the field on Gregorian calendar date and lunar date corresponding to this Gregorian calendar date, but can also comprise other times date field in practice, as shown in Figure 2, Fig. 2 is the field schematic diagram of time dimension table in one embodiment of the invention, certainly, the present embodiment also just for example, and unrestricted the present invention, those skilled in the art can arrange multiple Time of Day field according to actual needs.
As shown in Figure 2, the field of dimension table comprises the_date, and type is smalldatetime, and length is 4, is not empty, represents Gregorian calendar date field; The_lunar, type is that Nvarchar length is 30, is not empty, represents lunar calendar field; The_year, type is int, length is 4, is not empty, represents the time at date place; The_halfyear, type is Char, length is 6, is not empty, represents half a year; The_quarter, type is Char, length is 6, is not empty, represents season; The_month, type is Char, length is 6, is not empty, represents the moon; The_xun, type is Char, length is 4, is not empty, represents ten days; The_day, type is Char, length is 4, is not empty, represents day (which day monthly); The_week, type is int, length is 4, is not empty, represents week; The_yearweek, type is smalldatetime, length is 4, is not empty, represents which week in year; Key_quarter, type is int, and length is 4, is not empty, represents key assignments corresponding to season, for example, be fourth quarter, and key assignments is 4; Key_month, type is int, and length is 4, is not empty, represents a month corresponding key assignments, for example, be Dec, and key assignments is 12; Key_xun, type is int, and length is 4, is not empty, represents key assignments corresponding to ten days, for example, be the first tenday period of a month, and key assignments is 1; Key_day, type is int, and length is 4, can be empty, represents a day corresponding key assignments, is this month the 23rd day for example today, and key assignments is 23; Key_week, type is int, and length is 4, is not empty, represents key assignments corresponding to week, and be Sun. for example today, and key assignments is 7; Key_yearweek, type is int, length is 4, is not empty, in representing year week correspondence key assignments; Be the 40th week in 2011 for example this week, and key assignments is 201140.In addition, time dimension table also comprises Major key PK, and corresponding to Gregorian calendar date field, Major key is used for carrying out associatedly with other tables, for example, carry out associated with the fact table in data warehouse.
In step 102, utilize date function that from from date to all Gregorian calendar date datas between the Close Date and respectively corresponding lunar date data are write described time dimension table and specifically comprised: to utilize the first date function to write described Gregorian calendar date data; And utilize the second date function to write described lunar date data.
Wherein the first date function for example obtains the moon, day, week, week and the season at Gregorian calendar date place for function datepart of existing date of database, and add some judgements and calculated and the moon, day, week, week, season, ten days and half a year have been shown with literal table, for example judge whether month is less than or equal to June, if be less than, explanation is the first half of the year, export the first half of the year with word, other judgement and calculating similarly, are no longer described in detail herein.
Wherein the second date function is a scalar-valued function fn_GetLunar, this function definition first judges whether it is the leap year, for example generate a table from 1900 to 2049 according to lunar date, judge whether it is the leap year by function f n_GetLunar afterwards, if the leap year, continue to judge the leap month which month being, how many days leap month has, calculate again afterwards the number of days of other every months in middle of the month, finally days sky be spliced to a character string, then export lunar date, for example, input a date, just can calculate the corresponding lunar calendar of this date with following algorithm:
declare@Lunardate?nvarchar(30)
set@Lunardate=dbo.fn_GetLunar('2011-10-9')
print@Lunardate
After utilizing the first date function and the second date function creation storing process, next just carry out storing process, as long as input from date and Close Date, the first date function and the second date function will be by data stuffing in time dimension tables, for example, the from date of input is 2000-1-1, the Close Date of input is 2011-10-9, the total data (value that is field the_date is from 2000-1-1 to 2011-10-9) between just having had from 2000-1-1 to 2011-10-9 in complete rear time dimension table.Refer to Fig. 3, Fig. 3 is the time dimension table that data stuffing completes.
Please refer to Fig. 4, Fig. 4 is the method flow diagram of creation-time dimension table in one embodiment of the invention, and the method comprises:
Step 401: the first field is made as to the first level;
Step 402: the second field is made as to the second level.
In the present embodiment, the time dimension table of describing can be added in Analysis Service project in Fig. 2,3, also can add other times dimension table.
In the present embodiment, the field that the first field is year of grace, the second field is the field of the lunar calendar that described year of grace is corresponding.
In other embodiments, the content of Field Count and each field can need to arrange according to the time, please refer to Fig. 5, Fig. 5 be in one embodiment of the invention time dimension table surface chart is set.In Fig. 5, show the hierarchical structure figure of three different time dimension tables.In first, be the simplest hierarchical structure figure, year is ground floor, and the moon is the second layer, can continue in addition to add the new rank > button of < of level below; The second is: year for ground floor, point is half annual layer after entering, and 1 annual layer point is season layer after entering, season layer point after entering, be lunar stratigraphy, lunar stratigraphy point is a day layer after entering, and can also add new level layer below equally; The difference of the third and the second is that day layer also has lunar calendar layer below, can also add new level layer below lunar calendar layer certainly.Therefore, time horizon of the present invention is just as hierarchical tree, in the drop-down list of using of the prior art, very easy to use.
Next please refer to Fig. 6, Fig. 6 is the functional block diagram of determining the device of time dimension table data in one embodiment of the invention, this device comprises creation module 601, and for creation-time dimension table, dimension table comprises the field of the field on Gregorian calendar date and lunar date corresponding to Gregorian calendar date; And writing module 602, for utilizing date function by from from date to all Gregorian calendar date datas between the Close Date and respectively corresponding lunar date data write time dimension table.
Above to determining the various embodiment that describe in the method for time dimension table data, the device of the dimension table data that are equally applicable to really to fix time in Fig. 6, succinct for instructions, does not repeat them here.
In one embodiment of the invention, be provided with the time comprehensively, define multiple Time of Day field, comprise the Gregorian calendar and lunar calendar field, because lunar calendar field is directly defined, institute uses and disuses and recalculates the lunar calendar, in addition, because time parameter arranges in detail, so the demand such as arrange of the selection of various time parameters and each time phase can meet report making time, has improved the search efficiency of form.
Further, one embodiment of the invention adopts time dimension table is set as to hierarchical structure, and while making form, querying condition can be made as one, can show by expanding in layered manner, without the multiple queries condition that arranges of interlock in a condition.
Further, in one embodiment of the invention, utilize self-defining function for time dimension table padding data, thus can Fast Filling data, and especially, in the situation that data volume is larger, method of the present invention has more advantage.
Obviously, those skilled in the art can carry out various changes and modification and not depart from the spirit and scope of the present invention the present invention.Like this, if these amendments of the present invention and within modification belongs to the scope of the claims in the present invention and equivalent technologies thereof, the present invention is also intended to comprise these changes and modification interior.
Claims (10)
1. a method for definite time dimension table data, is characterized in that, described method comprises:
Creation-time dimension table, described dimension table comprises the field of the field on Gregorian calendar date and lunar date corresponding to described Gregorian calendar date; And
Utilize date function that from from date to all Gregorian calendar date datas between the Close Date and respectively corresponding lunar date data are write described time dimension table.
2. the method for claim 1, it is characterized in that, the field on described Gregorian calendar date comprises the first field, the second field, the 3rd field, the 4th field and the 5th field, described the first field is the field in described year on Gregorian calendar date, described the second field is the field of described half a year on Gregorian calendar date, and described the 3rd field is the field in described season on Gregorian calendar date, and described the 4th field is the field of the described moon on Gregorian calendar date, described the 5th field is the field of described Gregorian calendar day, and described method also comprises:
Described the first field is made as to the first level;
Described the second field is made as to the second level;
Described the 3rd field is made as to the 3rd level;
Described the 4th field is made as to the 4th level; And
Described the 5th field is made as to layer 5 level.
3. the method for claim 1, is characterized in that, described dimension table also comprises the field in ten days.
4. the method for claim 1, is characterized in that, the described date function that utilizes writes from from date to all Gregorian calendar date datas between the Close Date and respectively corresponding lunar date data described time dimension table and specifically comprises:
Utilize the first date function to write described Gregorian calendar date data; And
Utilize the second date function to write described lunar date data.
5. method as claimed in claim 4, is characterized in that, described the second date function is:
declare@Lunardate?nvarchar(30)；
set@Lunardate=dbo.fn_GetLunar('2011-10-9')；
print@Lunardate。
6. a device for definite time dimension table data, is characterized in that, described device comprises:
Creation module, for creation-time dimension table, described dimension table comprises the field of the field on Gregorian calendar date and lunar date corresponding to described Gregorian calendar date; And
Writing module, for utilizing date function that from from date to all Gregorian calendar date datas between the Close Date and respectively corresponding lunar date data are write to described time dimension table.
7. device as claimed in claim 6, it is characterized in that, the field on described Gregorian calendar date comprises the first field, the second field, the 3rd field, the 4th field and the 5th field, and described the first field is the first level, and described the first field is the field in described year on Gregorian calendar date; Described the second field is the second level, and described the second field is the field of described half a year on Gregorian calendar date; Described the 3rd field is the 3rd level, and described the 3rd field is the field in described season on Gregorian calendar date, described the 4th field is the 4th level, and described the 4th field field that is the described moon on Gregorian calendar date, described the 5th field is layer 5 level, and described the 5th field is the field of described Gregorian calendar day.
8. device as claimed in claim 6, is characterized in that, described dimension table also comprises the field in ten days.
9. device as claimed in claim 6, is characterized in that, described date function comprises the first date function, for writing described Gregorian calendar date data; And the second date function, for writing described lunar date data.
10. device as claimed in claim 9, is characterized in that, described the second date function is:
declare@Lunardate?nvarchar(30)；
set@Lunardate=dbo.fn_GetLunar('2011-10-9')；
print@Lunardate。
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201210587732.6A CN103902625B (en) | 2012-12-28 | 2012-12-28 | A kind of method and device of determining time dimension table data |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201210587732.6A CN103902625B (en) | 2012-12-28 | 2012-12-28 | A kind of method and device of determining time dimension table data |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN103902625A true CN103902625A (en) | 2014-07-02 |
| CN103902625B CN103902625B (en) | 2018-08-07 |
Family
ID=50993952
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201210587732.6A Expired - Fee Related CN103902625B (en) | 2012-12-28 | 2012-12-28 | A kind of method and device of determining time dimension table data |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN103902625B (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109359132A (en) * | 2018-09-30 | 2019-02-19 | 金蝶软件（中国）有限公司 | A kind of method of data processing, device and system |
Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| KR101134825B1 (en) * | 2005-12-10 | 2012-04-13 | 엘지전자 주식회사 | Method for displayimg moon phase and mobile communication terminal using the method |
| CN102467074A (en) * | 2010-11-11 | 2012-05-23 | 姚延忠 | Integrated scheme for simulating sun-earth-moon operation and completely expressing time calendar system by pointer type 24-hour-system clock |
-
2012
- 2012-12-28 CN CN201210587732.6A patent/CN103902625B/en not_active Expired - Fee Related
Patent Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| KR101134825B1 (en) * | 2005-12-10 | 2012-04-13 | 엘지전자 주식회사 | Method for displayimg moon phase and mobile communication terminal using the method |
| CN102467074A (en) * | 2010-11-11 | 2012-05-23 | 姚延忠 | Integrated scheme for simulating sun-earth-moon operation and completely expressing time calendar system by pointer type 24-hour-system clock |
Non-Patent Citations (2)
| Title |
|---|
| 王学科: "基于.NET的桌面提醒软件的设计与实现", 《中国优秀硕士学位论文全文数据库 信息科技辑》 * |
| 雨中漫步: "关于维度中的属性及命名计算", 《HTTP://BLOG.SINA.COM.CN/S/BLOG_4CD320930100CX89.HTML》 * |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109359132A (en) * | 2018-09-30 | 2019-02-19 | 金蝶软件（中国）有限公司 | A kind of method of data processing, device and system |
| CN109359132B (en) * | 2018-09-30 | 2021-06-29 | 金蝶软件（中国）有限公司 | Data processing method, device and system |
Also Published As
| Publication number | Publication date |
|---|---|
| CN103902625B (en) | 2018-08-07 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| Mi et al. | China's “exported carbon” peak: patterns, drivers, and implications | |
| Rosa et al. | The water‐energy nexus of hydraulic fracturing: a global hydrologic analysis for shale oil and gas extraction | |
| You et al. | Hedging rule for reservoir operations: 1. A theoretical analysis | |
| Hunter et al. | Modeling for insight using tools for energy model optimization and analysis (Temoa) | |
| Zhao et al. | Optimality conditions for a two‐stage reservoir operation problem | |
| KR101977972B1 (en) | Spreadsheet-based programming language adapted for report generation | |
| Kharecha et al. | Implications of “peak oil” for atmospheric CO2 and climate | |
| Wang et al. | Nitrogen constraints on terrestrial carbon uptake: Implications for the global carbon‐climate feedback | |
| AU2019246901A1 (en) | Graphical user interface that simplifies user creation of custom calculations for data visualizations | |
| Letsoalo et al. | Triple dividends of water consumption charges in South Africa | |
| CN102467375B (en) | Method and device for generating client interface | |
| You et al. | Determining forecast and decision horizons for reservoir operations under hedging policies | |
| Pinto et al. | Hybrid optimization for closed-loop reservoir management | |
| Beliakova et al. | Hydrocarbon Field Planning Tool for medium to long term production forecasting from oil and gas fields using integrated subsurface-surface models | |
| CN103902625A (en) | Method and device for determining time dimension table data | |
| Reister et al. | A general equilibrium two-sector energy demand model | |
| CN104866508B (en) | The method and apparatus of file is managed under cloud environment | |
| Yeh et al. | North American natural gas and energy markets in transition: insights from global models | |
| Mukundakrishan et al. | Full-GPU simulation of coupled multi-reservoir models: implementation and field examples | |
| Cantini et al. | Logic and Foundations of Mathematics: Selected Contributed Papers of the Tenth International Congress of Logic, Methodology and Philosophy of Science, Florence, August 1995 | |
| Perifanis | How US suppliers alter their extraction rates and what this means for peak oil theory | |
| Mackay et al. | Forecasting the United Kingdom's supplies and demands for fluid fossil-fuels | |
| CN108074282A (en) | The thickness correction method of physical parameter based on three-dimensional geological modeling software | |
| CN102880550B (en) | Memory formatting method based on constraint verification | |
| Biniwale et al. | An Innovative and Practical Integrated Asset Model: Optimisation from Pore to Process with Automatic Model Update |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant | ||
| CF01 | Termination of patent right due to non-payment of annual fee |
Granted publication date: 20180807 Termination date: 20191228 |
|
| CF01 | Termination of patent right due to non-payment of annual fee |