Search code examples
ssasdata-warehouseolap-cubecube-dimension

SQL OLAP Cube and dimension

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:

SCREENSHOT: stucture of the cube

  • 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:

SCREENSHOT: cube dimension

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: browse Top Brand

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

Solution

  • From what I understand, it looks like the top brand could change over time. This means that you would need to somehow track that over time. In my opnion, the easiest way to do that would be to add the TopBrandKey to the fact table and join the top brand dimension directly to the fact table. This way the time tracking is done for you in the fact table. As new records come in, you would look up the key for the top brand and add that to the fact table. I don't think the way you have it would work because the product which was sold would not have the brand which is the top brand (those are two different things).