ARAdventureWorks

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

Some sample queries

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

ExplanationURL
Customer with addresses. Shows use of EntityMethodKey through methods like TryGetCityDimCustomer
SELECT Name, PostalCode, City, StateOrProvince, Country
ORDER BY City
Products for which we have a photoDimProduct
WHERE Photo NEQ Null
SELECT Name, ModelName, Description, Photo
Products without a photoDimProduct
WHERE Photo EQ Null
SELECT Name, ModelName, Description
ORDER BY Name
Employees with Supervisors and SubordinatesDimEmployee
SELECT Name, ParentEmployee.Name, ChildEmployee.Name.Count() AS DirectSubordinates
ORDER BY DirectSubordinates DESC
THEN BY Name
Employees sorted by payDimEmployee
WHERE Status = 'Current'
SELECT Name, BaseRate, Status, Department, Photo
ORDER BY BaseRate
Count of employees per departmentDimEmployee
WHERE Status = 'Current'
AGGREGATE Department
ORDER BY _SUM DESC
What we are currently selling on the InternetFactInternetSale
SELECT OrderDate, SalesAmount, DimCurrency.AlternateKey, DimProduct.Name, DimSalesTerritory.Name
ORDER BY OrderDate DESC
SKIP 0
TAKE 100
Internet sales per year and monthFactInternetSale
PIVOT OrderDate.YearMonth() BY DimCurrency.AlternateKey SUM SalesAmount
SELECT *.TMB() AS *
ORDER BY OrderDate.YearMonth()Id DESC
Internet sales with Fiscal yearFactInternetSale
SELECT OrderDate, OrderDate.AWFiscalYear(), SalesAmount, DimCustomer.Name
ORDER BY OrderDate DESC
Top 1000 Australian Internet customers for 2020FactInternetSale
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 FactInternetSalesFactInternetSale
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 monthFactResellerSale
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 yearFactResellerSale
PIVOT DimReseller.FirstOrderYear BY DimReseller.ProductLine
ORDER BY DimReseller.FirstOrderYearId DESC
SalesAmount of reseller sales done in EUR by Reseller's first order yearFactResellerSale
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 ProductLineFactResellerSale
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

Database entities

EntityCount
DimAccount99
DimCurrency105
DimCustomer18484
DimDepartment7
DimEmployee296
DimGeography655
DimOrganization14
DimProduct606
DimProductCategory4
DimProductSubcategory37
DimPromotion16
DimReseller701
DimSalesReason10
DimSalesTerritory10
FactCallCenter120
FactCurrencyRate14264
FactFinance39409
FactInternetSale60398
FactInternetSaleReason64515
FactProductDescription15168
FactResellerSale60855
FactSalesQuota163
FactSurveyResponse2727

Miscellaneous links

AgoRapide (ARCore) documentation

Internal application state

Other information

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

Edit


Generated 2024-12-04 22:16:28.731 UTC