Abstract:
A computer-implemented method includes providing a fixed portion of a data model, uploading external files to an extended portion of the data model, matching at least some parts of the external files to the fixed portions of the data model, and using data from both the fixed and extended portions of the data model together. A computer-implemented method includes receiving a query from a user to a data model that includes a stored subject area portion and a stored extended subject area portion, accessing at least one storage to retrieve the subject area portion and the extended subject area portion, joining relevant portions of the subject area and extended subject area, extracting answers to the query from the joined portions, and reporting the answers to the user.

Description:
CROSS REFERENCE TO RELATED APPLICATION 
       [0001]    This application claims priority to and the benefit of U.S. Provisional Application No. 62/054,682, filed Sep. 24, 2014, which is incorporated herein in its entirety. 
     
    
     BACKGROUND 
       [0002]    Business intelligence (BI) systems often include databases and data warehouses as part of an overall data model. Users of these systems often want to include data that is not necessarily in the data model for running reports and analyses, but may reside in spreadsheets or other data files. In current systems, the ability to combine these types of data into a form usable by most business users does not exist. 
         [0003]    It is possible in some systems to add in external files, such as Microsoft® Excel® spreadsheets. Typically this capability requires the use of administrator tools to create the data model that includes the files, or to insert the data source into the model. Other systems can upload the file as a data model through publishing tools, but generally does not provide integration with the semantic models used for queries and access to the data. Approaches also exist that allow uploading of the external file for defining a complete model in business intelligence systems, but the system users typically consist of sophisticated business analysts and may be limited to cloud-base services. These solutions will not work for most end-users. 
     
    
     
       BRIEF DESCRIPTION OF THE DRAWINGS 
         [0004]      FIGS. 1 and 2  show a system having a data model that includes external files. 
           [0005]      FIG. 3  shows a flowchart of an embodiment of a method to upload and use external files in a data model. 
           [0006]      FIGS. 4-8  show embodiments of user interfaces for uploading external files into a data model. 
           [0007]      FIGS. 9-16  show embodiments of query results in a data model that includes external files as dimension extensions. 
           [0008]      FIG. 17-20  show embodiments of query results in a data model that includes external files as fact extensions. 
       
    
    
     DETAILED DESCRIPTION OF THE EMBODIMENTS 
       [0009]      FIGS. 1 and 2  show embodiments of a system including a data model of both fixed subject areas (SA) and extended subject areas (XSA). In  FIG. 1 , the client system  10  provides a user access to a data model that includes a fixed model subject area  12 . The client system  10 , as well as the servers mentioned below may consist of one or more computing devices having a process for executing instructions that allow the processor to interact with the user through a user interface, operate on data retrieved from one or more storages and store results of those operations in the storage. The client system allows the user to access the data model. For example, the fixed model may include tables from a database or data warehouse related to products, customers and sales. The rpd file for the database may be used by distributed data processing tools and services  17 , such as Hadoop, databases and data warehouses  18 , and storage  19 . 
         [0010]    However, the user wants to include data related to surveys and demography of the customers, which are currently in spreadsheet form as extended subject areas  14 . The user does not necessarily want to add this information into the fixed model, but wants to be able to perform analysis and run reports on this information combined with the information in the fixed model subject area. The XSA, in this case Excel® spreadsheets are stored in storage  16 , which may or may not coincide with the storage  19 . 
         [0011]      FIG. 2  shows a more detailed view of the architecture. The client  10  uploads the XSA file to the presentation server  11 . The process matches the columns in the XSA file to any columns in the data model that it can. The presentation server  17  communicates the information to the BI server  12  that has the Semantic Model (rpd file)  12  and the data set service such as Hadoop  17 . The data set service  17  also provides access to the data store  16  for the XSA data and metadata related to the XSA data. The BI server provides access to the data warehouse data  18  and the XSA cache, which need not necessarily be in the data warehouse. 
         [0012]    This system allows users to upload files external to the fixed model and allows for updating and storing that data as it changes. A flowchart of an overview of an embodiment of such a process is shown in  FIG. 3 . In  FIG. 3 , the system uploads the external data from a client system at  20 . An example of a user interface that provides such a capability is shown in  FIG. 4 . The user interface  30  has an option  32  to upload external files. 
         [0013]    Typically, the user will have several options in uploading and working with the files. The user can upload the file and provide the necessary metadata, discussed below, or the user can upload a file as a replacement file. The user can edit the metadata after it has been entered, upload a file into an existing catalog location, resulting in the file being appended to an existing file. The user may also have the capability to perform catalog operations such as delete, copy, move and export filed from the data catalog. 
         [0014]    Returning to  FIG. 3 , once the file is uploaded the system then needs to understand the data at  22 . No use is being made of the external subject matter at this point, so the queries may be to the XSA data only. One way to understand the external data is to query the external data dimension values and the data warehouse product dimension to see what matches.  FIG. 5  shows an example of a user interface that allows the user to identify the metadata for the columns to allow the match between the SA and the XSA. The columns  34  will be matched, or ‘conformed,’ to columns in the SA. The brand and target revenue columns  36  represent the ability to add an attribute, the brand, and to add a measure, the target revenue, to the XSA. 
         [0015]    Once the external data is matched, it can be conformed to the fixed data model to allow data from both portions of the model to be used with each other at  24  in  FIG. 3 . The user interface that allows the user to use the new information is shown in  FIG. 6 . The New Brand list  38  allows the use of the extended information not previously able to be used. The user interface with region  40  in  FIG. 7  shows that the XSA columns are used like any other subject area columns  FIG. 8  shows at  42  that they can be re-used with other data analyses outside the current one shown in  FIG. 7 . Finally, at  26  in  FIG. 3  the external data is unioned with the system at  26 . Each of these portions will be taken in turn. 
         [0016]    In the XSA, during the matching and extending process, three types of columns can be introduced, in one embodiment. Conformed columns match dimensional column values available in the Subject Area. Non-conformed attribute columns add new dimensional values not found in the SA, such as a demographic description include zip codes. Non-conformed, aggregate columns add a new measure not found in the Subject Area, such as a sales target set by a person&#39;s supervisor. 
         [0017]    Similar to the types of columns, two types of tables can be introduced, in one embodiment. Dimension extension tables have conformed columns to match existing dimension columns and attributes, and new, non-conformed columns added to a dimension table. A fact extension table includes matches of conformed columns to match with existing dimension columns, measures that are new as non-conformed columns added as aggregate measures, and attributes that are new as non-conformed columns added to a dimension table. 
         [0018]    Once the XSA with its various column types and tables is added to the data model, queries may result in many different combinations of the data. In many of the below examples, four columns from the SA will be combined at least in part with a spreadsheet from the XSA. The columns for the SA are as follows: 
         [0000]                                        Customer Dim   Sales Fact   Product Dim   Calendar Dim                   Customer Key   Customer Key   Product Key   Day Key       Region   Product Key   Product Family   Cal Year       Country   Day Key   Product Category   Cal Quarter       State   Invoice Ref   Product Type   Cal Month       City   Sales Rep Key   Product Name   Month Name       Zip   Units Sold (Σ)   Product Brand   Date       Customer Name   Revenue (Σ)                    
These columns are all independent from each other.
 
The spreadsheet from the XSA is:
 
         [0000]    
       
         
               
               
             
           
               
                   
                   
               
               
                   
                 Demography.xlsx 
               
               
                   
                   
               
             
             
               
                   
                 Zip 
               
               
                   
                 Age Group 
               
               
                   
                 Income Group 
               
               
                   
                 Skills Group 
               
               
                   
                 Education Group 
               
               
                   
                 Household Size 
               
               
                   
                   
               
             
          
         
       
     
         [0019]    In the above sets of columns, the Zip is a conformed column, as that exists in the SA column Customer Dim. In order for the XSA dimension table to be used for valid query results, the XSA conformed column set, the row&#39;s composite key that in this case is Zip, values must be unique in the XSA, and they must be available in the SA Presentation Layer. In the above example, the Zip in the Demography column of the XSA conforms to a leaf grain in the Customer Dim column of the SA. The elements of the column Customer Dim are hierarchical. As will be discussed below in more detail, one could have a spreadsheet that has a key such as Country that matches to the Country in Customer Dim. This is a higher grain in the column than the Zip leaf grain. 
         [0020]    The remaining elements of the XSA column are non-conformed attributes. As can also be seen by the above, the Customer Key in the Customer Dim column matches the Customer Key in the Sales Fact column, as the Product Key in the Sales Fact column matches the Product Key in the Product Dim column, and the Day Key in the Sales Fact column matches the Day Key in the Calendar Dim column. 
         [0021]    In a fact extension, the XSA fact table has certain characteristics that will allow for valid query results. The XSA conformed column set, the row&#39;s composite key, can be non-unique in the XSA. The column conformation can only be made to a dimension table, and at some level in the hierarchy the conformed column must unique. The conformed columns must be available to the SA presentation layer. 
         [0022]    Using some of the same columns from the SA above, the discussion now shows a different external spreadsheet with different columns 
         [0000]    
       
         
               
               
               
               
             
               
               
             
           
               
                   
               
             
             
               
                 Sales Rep Dim 
                 Sales Fact 
                 Product Dim 
                 Calendar Dim 
               
               
                   
               
               
                 Sales Rep Key 
                 Customer Key 
                 Product Key 
                 Day Key 
               
               
                 Line of Business 
                 Product Key 
                 Product Family 
                 Cal Year 
               
               
                 Sales Group 
                 Day Key 
                 Product Category 
                 Cal Quarter 
               
               
                 Rep Name 
                 Invoice Ref 
                 Product Type 
                 Cal Month 
               
               
                 Salary 
                 Sales Rep Key 
                 Product Name 
                 Month Name 
               
               
                   
                 Units Sold (Σ) 
                 Product Brand 
                 Date 
               
               
                   
                 Revenue (Σ) 
               
               
                   
               
             
          
           
               
                   
                 Targets.xlsx 
               
               
                   
                   
               
               
                   
                 Prod Category 
               
               
                   
                 Cal Quarter 
               
               
                   
                 Sales Rep Key 
               
               
                   
                 Threshold 
               
               
                   
                 Comsn Pcnt 
               
               
                   
                 Rev Target (Σ) 
               
               
                   
                   
               
             
          
         
       
     
         [0023]    The Sales Rep Key of the spreadsheet is a conformed column with the Sales Rep Key in Sales Fact and Sales Rep Dim. The Product Key and the 2Day Key from Sales Fact match with the same keys in Product Dim and Calendar Dim, respectively. The Product Cat from the spreadsheet conforms to the Product Category in Product Dim and the Cal Quarter from the spreadsheet matches the same from Calendar Dim. The Threshold and Comsn Pcnt from the spreadsheet are non-conformed attributes. And the Rev Target is an XSA aggregate measure. 
         [0024]    Similarly, one can have both a dimension and a fact extension. Using columns from above, and two spreadsheets in the XSA, Demography.xlsx from above and Mkt Data.xlsx, one can see an example of this. Note that the two spreadsheets appear to be columns but are actually separate spreadsheets. 
         [0000]    
       
         
               
               
               
               
             
               
               
               
             
           
               
                   
               
             
             
               
                 Customer Dim 
                 Sales Fact 
                 Product Dim 
                 Calendar Dim 
               
               
                   
               
               
                 Customer Key 
                 Customer Key 
                 Product Key 
                 Day Key 
               
               
                 Region 
                 Product Key 
                 Product Family 
                 Cal Year 
               
               
                 Country 
                 Day Key 
                 Product Category 
                 Cal Quarter 
               
               
                 State 
                 Invoice Ref 
                 Product Type 
                 Cal Month 
               
               
                 City 
                 Sales Rep Key 
                 Product Name 
                 Month Name 
               
               
                 Zip 
                 Units Sold (Σ) 
                 Product Brand 
                 Date 
               
               
                 Customer Name 
                 Revenue (Σ) 
               
               
                   
               
             
          
           
               
                   
                 Demography.xlsx 
                 MktData.xlsx 
               
               
                   
                   
               
               
                   
                 Zip 
                 Prod Fam 
               
               
                   
                 Age Group 
                 Cal Quarter 
               
               
                   
                 Income Group 
                 Zip 
               
               
                   
                 Skills Group 
                 Media Class 
               
               
                   
                 Education Group 
                 Media Type 
               
               
                   
                 Household Size 
                 Vendor 
               
               
                   
                   
                 Rev (Σ) 
               
               
                   
                   
               
             
          
         
       
     
         [0025]    Again, the Zip in the Demography spreadsheet is a conformed column, and in the Mket Data spreadsheet as well, as they conform to the Zip in Customer Dim. In addition, the Prod Fam and Cal Quarter columns in the Mket Data spreadsheet match the Product Dim column and the Calendar Dim column, respectively. The Media Class, Media Type and Vendor in Mket Data are non-conformed attributes and the Rev is an XSA aggregate measure. 
         [0026]    Having seen the various combinations of information from the SA and the XSA, one can see the results of various queries.  FIG. 9-11  shows examples of queries in which there has been a dimension extension in the XSA. Generally, the system will perform a join between the SA and the XSA portions relevant to the query, extract the desired data and then display or otherwise report it to the user. The resulting answer and the query may then be stored for later access. 
         [0027]      FIG. 9  shows a query result from a dimension extension in which 2 zip codes do not exist in the databased, or fixed data model, subject area. This uses the XSA dimension table only. In  FIG. 9 , the query result shows 2 zip codes, for Brooklyn and Santa Barbara that do not exist in the SA. Without the use of the XSA, the information for this zip codes would not be accessible by the end user. 
         [0028]      FIG. 10  shows a dimension extension in which the SA dimension table and the XSA dimension table results are shown in a full outer join. The information from the SA is shown in window  46  and the information from the XSA in window  46 . As can be seen here, the gray area  50  shows null SA attributes. The XSA displays the information for those two zip codes from  FIG. 9 , but there is no matching cities in the SA. One should note that the use of the term ‘window’ merely differentiates between the sources of the data displayed to the user. The data appears in the columns as shown, but with no differentiation indicating their source. 
         [0029]      FIG. 11  shows a query result in which the columns from the SA dimension table and the XSA dimension table are joined in a left outer join to fact. The windows  52  display the information from the SA and the window  54  shows the information from the XSA. The group of columns  56  are conformed columns and will return the same values. One should note that there are no null SA measures as shown in the right SA window  52 . 
         [0030]    Other situations may also arise. In  FIG. 12 , the results of a query include 8 countries, such as South Africa  58 , that do not exist in the fixed model SA, only in the XSA. When the two portions combine in a full outer join to answer a query,  FIG. 13  shows the results. In the SA window  60 , there is a null attributes area  64 , while the countries that exist in the XSA  66  show up in the XSA window  62 . Similarly,  FIG. 14  shows null attributes  72  in the SA dimension table window  68  for Portugal  74  that showed in the XSA only query above. New Zealand and Mexico exist in the SA, but not the XSA, so now the XSA window  70  has null attributes  76 . This resulted from a full outer join. 
         [0031]    In  FIG. 15 , both the SA  80  and the XSA  78  include a country column, which conform as  82 , each returning the same value. No null attributes exist for either the SA or the XSA. This resulted from columns from the SA fact table, the SA dimension table and the XSA dimension table, a left outer join to fact. 
         [0032]    As shown above, the XSA dimension in  FIG. 14  includes Portugal, but the SA dimension table does not, and the SA dimension table includes New Zealand and Mexico, but the XSA does not.  FIG. 16  shows a query resulting from a mix set of SA dimension table, SA fact and XSA dimension table left outer join to fact. The resulting report shows no null SA attributes in either  86  or  80 , but null attributes  92  in XSA  88 . 
         [0033]    In summary of these types of queries, an XSA dimension can have a 1:1 or a 1:M relationship with the SA dimension to which it conforms. A left outer join is performed between facts and dimensions. All fact values and only those dimension values that correspond end up in the result. A full outer join is performed between dimensions and dimensions, where null SA or XSA dimension values can appear. 
         [0034]    The discussion now turns to fact extensions, discussed above with regard to the requirements. In  FIG. 17 , the resulting response comes from columns from XSA fact table at  98  and  100 , and SA fact table and SA dimension at  94  and  96 . The resulting calculation Variance at  102  comes from both SA and XSA measures. 
         [0035]      FIG. 18  shows a different situation. This results from a combination of XSA fact, SA fact and SA dimensions. No null attributes for either the SA or the XSA exist in columns  104  or  106  within the window  112 . However, the ‘empty’ spaces exist, but represent no targets or no sales rather than null values. For example, the regions  114  in column  108  represent no sales made for the sales rep Nina Swartz, who had targets present in column  110 . Similarly, no target exists in column  110  for Fernando Rio to sell music, but he had sales, resulting in the empty spot  118 . This combination results from a left outer join to both fact tables. 
         [0036]    In fact extensions, one may have a dense data supplement in which full responses or data exist and the data can be correlated with a number of dimensions.  FIG. 19  shows a result from XSA fact, SA fact and SA dimension. Groups  118 ,  122  and  126  come from the SA and  120  and  124  come from the XSA. This demonstrates how many different dimensions can be analyzed when the data is dense. 
         [0037]    In contrast, a sparse data supplement means that the XSA fact table has minimal data and the responses do not include all of the data points. For example, a survey asked for information that would have resulted in information about the day the customer bought a product, and whether or not the customer liked it, would promote it to others and whether it was of value. The only responses were to whether or not it was of value. The SA had information as to the sales year and revenues, shown at  132  in  FIG. 20 , and the responses showed whether or not it was of value from the XSA at  134 . The system can also present this information as a graph  136 . 
         [0038]    In this manner, users can upload external data to a data model and use it in conjunction with a fixed data model portion to run analyses, reports and to answer queries. Having discussed the various types of joins, dimension and fact extensions, the discussion now turns to the implementation of how to perform such tasks. 
         [0039]    One embodiment of the invention includes a funnel process in which mash-up and existing data are feed in at the top of the funnel and through a series of queries and/or filters, the desired answer comes out at the bottom of the funnel, which may be in the form of, e.g. a table or chart. In one example, the end-user can create mashup data by uploading an Excel file and associate it with elements of an existing model. The Excel file and the appropriate columns of the Excel file can then be used to formulate reports, analyses, and dashboards. 
         [0040]    In one embodiment once a data mashup file is loaded into the storage repository, it is available for authoring and display within analyses and dashboards. BI Answers/Dashboards know the name of the data mashup file and the column names. Datatypes and other metadata are exposed in Answers in the same way as with current BI metadata. Oracle Business Intelligence System (OBIS) will be responsible for interpretation of the metadata. As a simple example assume the existing BI Model contains Sales information including ‘Product’ and ‘Actuals’. A data mashup file is uploaded and stored in the catalog that contains ‘Product’ and ‘Target’ information. To get as an output a report that is a simple table with ‘Product’, ‘Actuals’, ‘Target’ may result in something similar in substance to the following example query being generated automatically: 
         [0000]                                                SELECT                0 s_0,           ″RaaS″.″SAMP_PRODUCTS_D″.″PROD_DSC″ s_1,           ″RaaS″.″SAMP_REVENUE_F″.″REVENUE″ s_2,           ″XSA(‘JohnDoe’.‘MyDataMashup’)″.″DATA″.″TARGET_REVENUE” s_3            FROM ″RaaS″       ORDER BY 1, 2 ASC NULLS LAST       FETCH FIRST 65001 ROWS ONLY                    
In this example, Oracle BI Server will interpret the above. When it evaluates the Mashup Expression above, it will query Oracle Business Intelligence Presentation Server (BIPS) for the data types, relationships, and data appropriately to be able to evaluate the above query.
 
         [0041]    In another embodiment, the following steps may be performed: a data loader will load up the mashed-up data. BIPS will generate the appropriate metadata file. BIPS will issue appropriate queries for simple reports, passing the appropriate SQL syntax to OBIS. BIPS will need to retrieve the metadata as well. OBIS will query BIPS for data and metadata locations, interpret the results and perform the queries. 
         [0042]    An example of Fact Mashup Query Generation is given for the case of all attributes in a mashup. This case may be treated as a simple sub-request and all attributes may be automatically rendered onto the mashup subrequest. For example, if the XSA is (CustomerAddress, Quota), then the query: 
         [0000]    
       
         
               
               
             
               
             
               
               
             
           
               
                   
                   
               
             
             
               
                   
                 Select SA.CustomerAddress, SA.Sales, XSA.quote from SA, XSA 
               
               
                   
                 Is automatically rendered as: 
               
               
                   
                 Select * from 
               
               
                   
                 (select CustomerAddress, sum(sales) from SA.fact f, SA.dim d where 
               
             
          
           
               
                 f.cust_id=d.cust_id group by CustomerAddress) 
               
             
          
           
               
                   
                 FOJ 
               
               
                   
                 (select CustomerAddress, sum(quote) from XSA group by 
               
               
                   
                 CustomerAddress) 
               
               
                   
                   
               
               
                   
                 Note: 
               
               
                   
                 FOJ is full outer join 
               
             
          
         
       
     
         [0043]    An example of Dimension Mashups Query Generation is given for dimension only queries. If the curated side has a dimension Customer with a key of CustId and Attributes Name, Address and Zip, and an excel is uploaded which has the following: 
         [0000]    (CustId(conformed), Name (conformed), Marital Status). 
         [0044]    And if the query is: 
         [0000]    Select SA.CustID, SA.Address, XSA.MaritalStatus from SA, XSA
 
Then the following physical query is automatically rendered:
 
         [0000]    
       
         
               
               
             
               
               
             
           
               
                   
                   
               
             
             
               
                   
                 Select nvl(A.CustID, B.CustID), Address, MaritalStatus 
               
               
                   
                 (Select SA.custID, SA.Address from SA) A 
               
               
                   
                  FOJ 
               
             
          
           
               
                   
                 (Select SA.custID, SA.MaritalStatus from XSA) B 
               
               
                   
                   
               
             
          
         
       
     
         [0045]    It will be appreciated that several of the above-disclosed and other features and functions, or alternatives thereof, may be desirably combined into many other different systems or applications. Also that various presently unforeseen or unanticipated alternatives, modifications, variations, or improvements therein may be subsequently made by those skilled in the art which are also intended to be encompassed by the embodiments here.