The Project Design Process:
( The basic Data warehousing process needs to be followed –> Creation of Logical Data model –> then Creation of Conceptual model –> Then Creation of Physical Model – Will discuss about it if possible in the coming sessions )
Data Warehouse Schema Design
Designing the data warehouse schema is the second step in the project design process. As part of this step, you need to complete the following tasks:
– Consider the advantages and disadvantages of various structures for storing data in the data warehouse
– Determine the optimal schema design that balances the reporting requirements, performance requirements, and maintenance overhead
– Create the data warehouse using this schema design or modify the existing data warehouse to use this schema design
( Few key points are highlighted in short above – which includes resolving
1) Many – Many relationship in DWH
2) Attribute Roles ( Alias Table creation )
3) Ragged Hierarchies
4) Split Hierarchies
5) Recursive Hierarchies
6) Recommended schema ( snowflake / star )
7) Partitioning / DB Tuning etc.. )
Creating the Project in MicroStrategy Architect
You need to have a solid design for the logical data model and data warehouse schema before you move on to creating the actual project. Both of these components can directly affect how you query data in a project, what data you can query, how fast queries run, and so forth.
Note:
– Creating a project without first having a logical data model in place can complicate the task of project
creation since you are essentially defining your logical data model in MicroStrategy Architect.
– Creating a project without first having an effective data warehouse schema design in place can result in
more modifications to schema objects later on as you change the physical structure of the schema to accommodate your reporting needs.
So, its advised to follow the defined process to get the effective solution at last. Also better to get the needy requirements from the clients initially itself – so that future changes could be minimized and the effort can also be brought down. All could be possible with the effective DWH design, else we end up with too much of changes back & forth. Keep an effective Data modeler & Business Analyst to get rid of such issues in future.
After these steps are complete, you are ready to create a project in MicroStrategy Architect, which is the third step in the project design process. As part of this step, you need to complete the following tasks:
– Configure the project metadata if necessary ( it means – if its already setup, then not required 🙂 )
– Configure connectivity to the project metadata and data warehouse if necessary
– Create the project object in MicroStrategy Architect
– Create the necessary schema objects for the project based on the logical data model, mapping them to appropriate structures in the data warehouse schema
( will discuss about each of the above process in detailed in coming sessions )
Managing the Project Schema
Managing the project schema is the final and ongoing step in the project design process. Over the life of the project, your logical data model or data warehouse may change, or your reporting needs may change, which can necessitate changes to schema objects. Ongoing management of the project schema involves tasks such as the following:
– Creating new schema objects as necessary ( New attributes if required for the clients at later point of time like for eg: Employe table has Emp No/Emp Name initially & now the client wishes to see Employee DOB / Employee Mobile No / employee Address means — then new attributes or new attribute forms need to be included )
– Modifying the definitions of existing schema objects as necessary ( if any table level changes means – like data type changes or new column inclusions would cause to modify the existing schema objects )
– Removing schema objects you no longer need in the project ( Unused columns can be removed – so that MD load can be reduced. Generally we use Enterprise Manager to find such sorts of analysis from the stats information that we have )
– Updating the project schema to reflect any changes you make to schema objects ( Its said that – need to update the schema always to make the changes to reflect in the MD )
– Creating advanced schema objects for more complex functions ( there could be cases to go for complex functions while we create schema objects – like usage of pass-through functions { applysimple/concat/trim/etc } then usage of fact extensions , then usage of logical views etc )
Hope this is enough for the day i believe !!
Have a good Day!!