# Jaggi Charles-Lewis, Mottier Fabien
## Labo 3 Data Management
### Point 1
#### Requête utilisée

CREATE VIEW view_product AS
SELECT
    `ProductKey`,
    `ProductAlternateKey`,
    dimproduct.ProductSubcategoryKey,
    `ProductSubcategoryAlternateKey`,
    `EnglishProductSubcategoryName`,
    `FrenchProductSubcategoryName`,
    dimproductcategory.ProductCategoryKey,
    `ProductCategoryAlternateKey`,
    `EnglishProductCategoryName`,
    `FrenchProductCategoryName`,
    `WeightUnitMeasureCode`,
    `SizeUnitMeasureCode`,
    `EnglishProductName`,
    `FrenchProductName`,
    `StandardCost`,
    `FinishedGoodsFlag`,
    `Color`,
    `SafetyStockLevel`,
    `ReorderPoint`,
    `ListPrice`,
    `Size`,
    `SizeRange`,
    `Weight`,
    `DaysToManufacture`,
    `ProductLine`,
    `DealerPrice`,
    `Class`,
    `Style`,
    `ModelName`,
    `EnglishDescription`,
    `FrenchDescription`,
    `StartDate`,
    `EndDate`,
    `Status`
FROM dimproduct, dimproductsubcategory, dimproductcategory
WHERE dimproduct.ProductSubcategoryKey = 
dimproductsubcategory.ProductSubcategoryKey
AND dimproductsubcategory.ProductCategoryKey = 
dimproductcategory.ProductCategoryKey;

### Point 2
#### By month

#### By semester

### Point 3

### Point 4
#### Requête utilisée

SELECT [Measures].SalesAmount ON COLUMNS,
	[Dim.Date.ByMonth].[Year].[2008] ON ROWS
FROM [cube] 


The total amount for 2008 is **26461317.1**.

## Point 5
#### Requête utilisée

SELECT {[Measures].SalesAmount} ON COLUMNS,
(
  [Dim.Product].[Category].[Accessories],
  [Dim.Product].[SubCategory].[Fenders],
  [Dim.Product].[Product].[Fender Set - Mountain]
) ON ROWS
FROM [AdventureCube]
WHERE ([Dim.Date.ByMonth].[Year].[2008], [Dim.Geo].[State].[California])


The total amount for 2008 for Fender set - Mountain in california is **7693.0**.

### Point 6
#### Requête utilisée

WITH MEMBER [United States] AS 
    AGGREGATE([Dim.Geo].[Country].[United States], [Measures].SalesAmount)
    MEMBER [Others] AS 
    AGGREGATE([Dim.Geo].[Country] - [Dim.Geo].[Country].[United States], 
    [Measures].SalesAmount)
SELECT {[United States], [Others]} ON COLUMNS,
(
  [Dim.Product].[Category].[Accessories],
  [Dim.Product].[SubCategory].[Fenders],
  [Dim.Product].[Product].[Fender Set - Mountain]
) ON ROWS
FROM [AdventureCube]
WHERE [Dim.Date.ByMonth].[Year].[2008]


The total amount for 2008 for Fender set - Mountain in USA is **13473.7** and for other countries **15254.1**.

### Point 7
#### Requête utilisée

SELECT [Dim.Geo].[Dim.Geo].[Country] ON COLUMNS,
{
[Dim.Date].[Dim.Date.BySemester].[Year].[2008],
Filter(
	[Dim.Date].[Dim.Date.BySemester].[Quarter].Members,
    [Dim.Date].[Dim.Date.BySemester].CurrentMember.Parent.Parent
    IS [Dim.Date].[Dim.Date.BySemester].[Year].[2008]
)} ON ROWS
FROM [AdventureCube]
WHERE [Dim.Product].[Product].[Fender Set - Mountain]


#### best 2008 semester for Fender Set - Mountain
| Country | Semester | Amount |
| -------- | -------- | -------- |
| Australia | 2 | 2000.2 |
| Canada | 1 | 2326.9 |
| France | 2 | 637.4 |
| Germany | 2 | 1143.0 |
| United Kingdom | 2 | 1077.0 |
| United States | 2 | 6220.3 |