Search code examples
ssasdata-warehousedimensions

How to design dimension and fact tables in ssas

I have a database with few tables, ex.:

  • Employee (Id, Name, state, country)
  • Material (Id, Name, color, Type, amount, stock)
  • Order (Id, MaterialId, EmployeeId, amount, discount, total amount)
  • ShippingOrders (OrderId, MaterialId, EmployeeId, amount, discount, total amount)
  • ShippingCountries (Id, CountryName)
  • Customers (Id, Name, state,address, CountryId)
  • MaterialImages (Image, MaterialId)
  • PlacedOrders (OrderId, EmployeeId, CustomerId, Amount, shippingDate)
  • SavedCarts

Now, I have to create data warehouse from this table. Like we have AdventureWorksDW from AdventureWorks database.

As I am completely new to database, I am curious to know how will I decide which fields should be selected as dimensions and which fields should be selected as Facts.

and once I decide that then how would I create a DW database (like AdventureWorksDW)? Is this will be like Creating database and table. After that fill those tables with select query from master database (Ex. DimProduct in AdventureWorksDW is might made up from joins of few tables from AdventureWorks database)

To summarize, I am curious to know that how can I create my datawarehose db from existing db.

I am very new to SSAS and your suggestion and opinion will be very useful to me.

Solution

  • you will create your DW database like any other, it will just be more denormalized than your regular OLTP DB You should have a mechanism (SSIS packages are the most commomn) to update your DW from your OLTP database from time to time.

    The MAIN difference from facts and dimensions is that facts are values (like amount of sales for example) and dimensions are the "parameters" you will use to slice and dice the facts. Like customer, region, date, prodct type, etc.. now, how you will implement this on your DW is another thing (like for example, will you have a table for product color or will you add it to the product table) that we cant discuss on a single topic. As pondlife said, you have to google about data warehousing implementation.