samedi 5 mai 2012 13:27
In a windows Server 2008, using SQL server 2005, Visual Studio and SQL Server Analysis Services i created an OLAP Cube.
The scenario is a typical store with sell products, customer and agents.
The cube is made by a fact table tblVendite (sell data) and dimensions based on sql views.
**NOTE**: *I tried embed the screenshot here but they will resized and will not clear so i posted them in a safe site to view it:*
- FACT TABLE tblVendite (sell) > **PK: CdArticolo** (IDProduct)
- DIMENSION Prodotti (produtcs)
- DIMENSION Calendario (calendar)
- DIMENSION Agenti (agents)
- DIMENSION Clienti (customer)
Browsing the cube with basic filters as calendar, customer/agent, products, brand ... all work fine
Now i need to provide a new feature as:
Comparing the "top sell brand" of all company with the sell of each agent to see how he sell those brand
So i did a new table and the related view: vwMarcheOrd (top sell brand)
The table top sell brand is pre-populated by a sp and contain 1 record for each brand (CdMarca - Position - Description order by Position)
NOTE: from vwMarcheOrd i cant have direct link to the fact table because the PK is CdMarca and it's not present so i need to "bridge" the view Products by CdMarca and get the CdArticolo to link the fact table
Dimension and the attributes for Brand and Top Brand:
BROWSE THE CUBE AND THE PROBLEM
Adding the dimension Brand(1) to the row fields the result is correct but if i add the Top Brand(2) is incorrect and many brand are missing (but they exist)
SCREENSHOT: See the browsing cube result
So my questions are 2:
1. What i missing or wrong or misunderstand with the Top Brand Dimension ?
2. How i can display all records of Top Brand in the row fields even if the agent didnt sell any item of that brand (blank row) ?
p.s. i tried also make a dimension based only with vMarcheOrd and then create a Referenced Relationship with the Product table to bridge CdMarca > CdArticolo > Fact Table but the result are the same
Thanks in advance who can really help me to solve this problem that block me since many days