With the client today we gathered design specs by asking a series of questions to form table structures of Dimension and Fact tables. The design changed frequently based on new questions, hashing out the business rules to finalize some table structure.
After lunch, I sat with the developer to map the Staging and Data Warehouse tables to their Transactional system. When I got home, I created the 6 Dim tables 1 Fact table in Staging, then again in the Data Warehouse. Then created the SSIS ETL queries to move the data through they system.
Tomorrow I'll ask some more questions to finalize the Data Dictionary we've been keeping and then begin to pull data from OLTP. Once that's working, I'll begin to create the Cubes in SQL-Server Analysis Services (SSAS) followed by new reports in SharePoint PerformancePoint. Considering we just met the client for the first time late Friday afternoon and Monday was a holiday, we're off to a good start.
This "Proof of Concept" is due at the end of the month so they can demo everything. And maybe we'll get the 3 year contract to build a full blown Data Warehouse, wouldn't that be nice!
Data Warehousing is not easy by any means, except once you figure out the pattern of modeling the data, documenting the sources and mapping the fields, then building the tables and doing the ETL, it kind of builds itself being able to reuse code patterns. The SSAS will be new for me as my experience is mostly with Tabular Model, so the MDX may be a steep learning curve.
Learning new technology is great, applying it in real world scenarios, priceless!