---
# System prepended metadata

title: 'Jaggi Charles-Lewis, Mottier Fabien'

---

# Jaggi Charles-Lewis, Mottier Fabien
## Labo 3 Data Management
### Point 1
#### Requête utilisée
```sql=
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
![](https://i.imgur.com/ypBCr3T.png)
![](https://i.imgur.com/qs4X59f.png)
![](https://i.imgur.com/DjgW0fj.png)

#### By semester
![](https://i.imgur.com/6KMsi72.png)
![](https://i.imgur.com/CR30Z23.png)
![](https://i.imgur.com/jtogeTO.png)
![](https://i.imgur.com/U4ivNY1.png)

### Point 3
![](https://i.imgur.com/ecYSDtb.png)
![](https://i.imgur.com/Exx0840.png)
![](https://i.imgur.com/2ulebhE.png)

### Point 4
#### Requête utilisée
```sql=
SELECT [Measures].SalesAmount ON COLUMNS,
	[Dim.Date.ByMonth].[Year].[2008] ON ROWS
FROM [cube] 
```
![](https://i.imgur.com/nUB1ujd.png)


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

## Point 5
#### Requête utilisée
```sql=
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])
```
![](https://i.imgur.com/un9z5zl.png)


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

### Point 6
#### Requête utilisée
```sql=
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]
````

![](https://i.imgur.com/5uO3dPC.png)

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
```sql=
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]
```
![](https://i.imgur.com/P9rOoDs.png)


#### 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     |


