ARAdventureWorks is a demonstration of AgoRapide (ARCore) with data from Microsoft's AdventureWorks example dataset.
An online example of this application is found at http://ARAdventureWorksOLAP.AgoRapide.com
Note how queries, although predefined here, do not have to be. You can execute any query directly on-the-fly in your browser as long as you can construct the correct URL for it.
For more sample queries, see also http://ARNorthwind.AgoRapide.com
| Explanation | URL |
|---|---|
| Customer with addresses. Shows use of EntityMethodKey through methods like TryGetCity | DimCustomer SELECT Name, PostalCode, City, StateOrProvince, Country ORDER BY City |
| Products for which we have a photo | DimProduct WHERE Photo NEQ Null SELECT Name, ModelName, Description, Photo |
| Products without a photo | DimProduct WHERE Photo EQ Null SELECT Name, ModelName, Description ORDER BY Name |
| Employees with Supervisors and Subordinates | DimEmployee SELECT Name, ParentEmployee.Name, ChildEmployee.Name.Count() AS DirectSubordinates ORDER BY DirectSubordinates DESC THEN BY Name |
| Employees sorted by pay | DimEmployee WHERE Status = 'Current' SELECT Name, BaseRate, Status, Department, Photo ORDER BY BaseRate |
| Count of employees per department | DimEmployee WHERE Status = 'Current' AGGREGATE Department ORDER BY _SUM DESC |
| What we are currently selling on the Internet | FactInternetSale SELECT OrderDate, SalesAmount, DimCurrency.AlternateKey, DimProduct.Name, DimSalesTerritory.Name ORDER BY OrderDate DESC SKIP 0 TAKE 100 |
| Internet sales per year and month | FactInternetSale PIVOT OrderDate.YearMonth() BY DimCurrency.AlternateKey SUM SalesAmount SELECT *.TMB() AS * ORDER BY OrderDate.YearMonth()Id DESC |
| Internet sales with Fiscal year | FactInternetSale SELECT OrderDate, OrderDate.AWFiscalYear(), SalesAmount, DimCustomer.Name ORDER BY OrderDate DESC |
| Top 1000 Australian Internet customers for 2020 | FactInternetSale WHERE DimCurrency.AlternateKey = 'AUD' PIVOT DimCustomer.Name0x002B','0x002BDimCustomerId BY OrderDate.Year() SUM SalesAmount ORDER BY 2020 DESC TAKE 1000 |
| Demonstrate how foreign key DimSalesTerritoryId is not really needed in FactInternetSales | FactInternetSale SELECT SalesTerritory, DimCustomer.DimGeography.DimSalesTerritory.Name, DimSalesTerritory.Name SKIP 0 TAKE 1000 |
| Reseller sales pr Sales territory country and year (note inclusion of currency for 'safety' reasons) | FactResellerSale PIVOT DimSalesTerritory.Name0x002B'_'0x002BDimCurrency.Name BY OrderDate.Year() SUM SalesAmount |
| Reseller sales per Sales territory and year (note inclusion of currency for 'safety' reasons) | FactResellerSale PIVOT DimSalesTerritory.Name0x002B'_'0x002BDimCurrency.Name BY OrderDate.Year() SUM SalesAmount |
| Reseller sales per year and month | FactResellerSale PIVOT OrderDate.YearMonth() BY DimCurrency.AlternateKey SUM SalesAmount SELECT *.TMB() AS * ORDER BY OrderDate.YearMonth()Id DESC |
| Count of reseller sales by Reseller's first order year | FactResellerSale PIVOT DimReseller.FirstOrderYear BY DimReseller.ProductLine ORDER BY DimReseller.FirstOrderYearId DESC |
| SalesAmount of reseller sales done in EUR by Reseller's first order year | FactResellerSale WHERE DimCurrency.AlternateKey EQ 'EUR' PIVOT DimReseller.FirstOrderYear BY DimReseller.ProductLine SUM SalesAmount SELECT *.TMB() AS * ORDER BY DimReseller.FirstOrderYearId DESC |
| SalesAmount of reseller sales done in CAD by Reseller's ProductLine | FactResellerSale WHERE DimCurrency.AlternateKey EQ 'CAD' PIVOT OrderDate.Year() BY DimReseller.ProductLine SUM SalesAmount SELECT *.TMB() AS * ORDER BY OrderDate.Year()Id DESC |
| Database statistics (Note: Slow query) | SELECT CountP AS NumberOfEntities, CountPRec AS NumberOfPropertiesOfEntities, ToPropertyStream.Length() AS SizeInDatabase ORDER BY NumberOfEntities DESC |
| Entity | Count |
|---|---|
| DimAccount | 99 |
| DimCurrency | 105 |
| DimCustomer | 18484 |
| DimDepartment | 7 |
| DimEmployee | 296 |
| DimGeography | 655 |
| DimOrganization | 14 |
| DimProduct | 606 |
| DimProductCategory | 4 |
| DimProductSubcategory | 37 |
| DimPromotion | 16 |
| DimReseller | 701 |
| DimSalesReason | 10 |
| DimSalesTerritory | 10 |
| FactCallCenter | 120 |
| FactCurrencyRate | 14264 |
| FactFinance | 39409 |
| FactInternetSale | 60398 |
| FactInternetSaleReason | 64515 |
| FactProductDescription | 15168 |
| FactResellerSale | 60855 |
| FactSalesQuota | 163 |
| FactSurveyResponse | 2727 |
AgoRapide (ARCore) documentation
Author: Bjørn Erling Fløtten, Trondheim, Norway. bef_adventureworks@bef.no
The date and time values in the example database are modified at application startup in order to look fresh, and in order for queries like today's Internet sales to function.
Database original SQL dump was of size 160 MB.
The AgoRapide PropertyStream conversion of the same data is about 172 MB.
In-memory footprint of this application (without any optimizations) is around 240 MB (Baseline memory snapshot done in Visual Studio's Performance Profiler).
Note: Table FactProductInventory has been omitted for practical reasons. It will approximately more than double the conversion size / RAM use.
(See C# source code, Converter, for instructions about how to include it.)
The original database has been extensively improved with relation to field names, unused fields and unnecessary values.
Field names have been shortened, like 'DimProductCategory.EnglishName' instead of 'DimProductCategory.ProductCategoryEnglishName'.
Fields with the same value everywhere have simply been deleted, like 'DimInternetSales.RevisionNumber' which was always '1'.
For fields with a predominantly '0'-value, that value is not included ('0' being replaced with NULL). Example: 'DimEmployeeP.SalesPersonFlag'.
Also, unneccessary tables are not included, like 'DimDate' which is replaced with the concept of FunctionKey (see for instance FunctionKeyAWFiscalYear).
Table 'DimScenario' has been replaced with Scenario.
Other tables not included are 'NewFactCurrencyRate', 'ProspectiveBuyer', 'sysdiagrams', 'DatabaseLog'.
Many strategic EntityMethodKey has been implemented, like TryGetName.
All these improvements radically simplifies the process of browsing and making queries (doing OLAP on the data).
Note that the data in the database is somewhat 'random' in nature, it does not look natural (it gives the impression of being artificially generated). It is therefore difficult to show some natural insights that can be gathered by doing OLAP on the data, so this application mostly demonstrates technical aspects of AgoRapide.
Note: The author welcomes access to a real representative database where som new insights can be made with the help of AgoRapide as an OLAP tool.
The AdventureWorks database which have been used as the base for ARAdventureWorks can be found at
https://github.com/microsoft/sql-server-samples/blob/master/samples/databases/adventure-works/data-warehouse-install-script
with the following license:
https://github.com/microsoft/sql-server-samples/blob/master/license.txt
Generated 2024-10-13 01:53:51.213 UTC