Why do we need SSIS and star schema of Data Wareho

Question

If SSAS in MOLAP mode stores data, what is the application of SSIS and why do we need a Data Warehouse and the ETL process of SSIS?

I have a SQL Server OLTP database. I am using SSIS to transfer my SQL Server data from OLTP database to a Data Warehouse database that contains fact and dimension tables.

After that I want to create cubes using SSAS form Data Warehouse data.

I know that MOLAP stores data. Do I need any Data warehouse with Fact and Dimension tables?

Is not it better to avoid creating Data warehouse and create cubes directly from OLTP database?

Answer 1

This might be a candidate for "Too Broad" but I'll give it a go.

Why would I want to store my data 3 times?

I have my data in my OLTP (online, transaction processing system), why would I want to move that data into a completely new structure (data warehouse) and then move it again into an OLAP system?

Let's start simple. You have only one system of record and it's not amazingly busy. Maybe you can get away with an abstraction layer (views in the database or named queries in SSAS) and skip the data warehouse.

So, you build out your cubes, dimensions and people start using it and they love it.

"You know what'd be great? If we could correlate our Blats to the Foos and Bars we already have in there" Now you need to integrate your simple app with data from a completely unrelated app. Customer id 10 in your app is customer id {ECA67697-1200-49E2-BF00-7A13A549F57D} in the CRM app. Now what? You're going to need to present a single view of the Customer to your users or they will not use the tool.

Maybe you rule with an iron fist and say No, you can't have that data in the cube and your users go along with it.

"Do people's buying habits change after having a child?" We can't answer that because our application only stores the current version of a customer. Once they have a child, they've always had a child so you can't cleanly identify patterns before or after an event.

"What were our sales like last year" We can't answer that because we only keep a rolling 12 weeks of data in the app to make it manageable.

"The data in the cubes is stale, can you refresh it?" Egads, it's the middle of the day. The SSAS processing takes table locks and would essentially bring our app down until it's done processing.

Need I go on with these scenarios?

Summary

The data warehouse serves as an integration point for diverse systems. It has conformed dimensions (everyone's has a common definition for what a thing is). The data in the warehouse may exceed the lifetime of the data in the source systems. The business needs might drive the tracking of data that the source application does not support. The data in the DW supports business activities while your OLTP system supports itself.

SSIS is just a tool for moving data. There are plenty out there, some better, some worse.

So No, generally speaking, it is not better to avoid creating a DW and build your cubes based on your OLTP database.