How to design dimension and fact tables in ssas

0
votes

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.

1
ssasdata-warehousedimensions
Your question really seems to be, "how can I learn how to implement data warehousing using SSAS" and that is just too broad to be answered here. You need to start with business requirements, i.e. what data and reports should be delivered from the system, and in parallel develop your technical knowledge of the platform. If this is for work not study, ask your boss to pay for training. – Pondlife
@Pondlife Yes you are correct. This will be my first cube. I am learning SSAS by myself. I was wondering if someone could give me a hint how to start thinking like ssas developer. That's why I provided a simple requirement which would help me to understand SSAS – Zerotoinfinity
kimballgroup.com/html/books.html Start with "The Data Warehouse Toolkit" and go from there. – Pondlife

1 Answers

3
votes

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.