Build SSAS cube without dimensions

Question

i need a suggestion in designing a cube , here is my scenario: i have 3 tables lets say Product, Company and Sales. Product table contains details about the product for each month meaning for each product in each month, they will a record in this table. Similarly, company table holds details about the company for each month. Sales table maintains details about the sales in each month.

**Example:**

Product Table: 
ProductID   | Month     | ProductName 
------------+-----------+----------------
1           | Jan       | ABC
1           | Feb       | ABC
2           | Feb       | BCD

Company Table: 
CompanyID   | Month     | CompanyName 
------------+-----------+--------------
1           | Jan       | XYZ
1           | Feb       | XYZ
2           | Feb       | XXX

Sales Table: 
SalesID     | ProductID | CompanyID  | Month    | Amount 
------------+-----------+------------+----------+---------
1           | 1         | 1          | JAN      | 10 
2           | 1         | 2          | Feb      | 20

And i have a table for time dimension which is straight forward. I am planning to create views using this tables which will act as dimensions and fact for my cube. From the above scenario, i am able to treat Sales view as a Fact for my cube and able to connect to Product and Company table with the help of Id's and Month column. My question is, how can i design dimensions out of this data model? Is product and company can be treated as dimensions ? if it so, how i need to specify the relation in cube. Is it possible to create a cube with this data model at all? Please advise. your help will be highly appreciated. Thank you.

Answer 1

Yes, I hope so,

The Product, Company and Time/Date are possible dimensions. The Amount must be your Fact. As the productID and CompanyID have same column names in Dim and fact table, the relation will be identified automatically. Just make sure that in DB, everything is related correctly.

You will have to bring a MonthID instead of Month in Fact Table(just in case you missed)

Answer 2

Yes it is possible to create your Cube like this. First create two dimension based on the Product and Company tables. Then Create the Measure Group based on the Sales table. Link the dimensions Product and Company to the Measure Group on ProductID and Company ID. Also link both dimensions to the Measure Group with the Month field.

Alternatively you can add the Customer and Product details to the Sales table. This would enable you to create the Dimension and Measure Group from one table only, using the SalesID as relation. Off course not to be advised when taking performance into account.