In Part 1 of this series, I gave a brief introduction to the concept of semantic models and why they should be designed. In this article I discuss the general learnings from developing and deploying semantic models
-
-
If you are doing it all by yourself, it is not enterprise grade
-
As discussed in Part 1 these are the following people I collaborated with while building enterprise grade semantic models for a major superannuation company in Australia
-
-
-
-
- Information Architects
- Solution Architects
- Data Stewards
- Product Champions
- Data Modelers
- Business Analysts
- Business Intelligence Developers
- Testers
- Delivery Managers
- Report Developers
-
-
-
It truly does take a team to ideate, design, test, validate, and support the build of enterprise grade semantic models. In the past, I have developed pseudo enterprise grade models as I was donning multiple hats and that resulted in a not optimal data warehouse and subsequently the semantic model wasn’t fit for reporting. Hence is important to have specialists for the above roles. If you have built a semantic model by yourself, it is probably not enterprise grade. This leads to the next point
-
-
Designing a report for an enterprise is not the same as semantic modeling
-
Many Power BI consultants make the mistake of interpreting a report built for a business unit in an enterprise as enterprise grade semantic models. They are not one and the same. Firstly, the reports have been probably built by a single person meaning, the person building the report is the architect, data modeler, data engineer, semantic modeler, and a report developer all rolled into one. It is akin to building the Burj Khalifa all by yourself, simply not possible when you deal with enterprise data.
That doesn’t mean you should not build Power BI models for enterprises. But due to the localised nature of it, the models will only be used by a section of the end users and the rest of them won’t have a clue about it. Enterprise models should be available to be consumed (via reports) by all employees within the organisation. Only then can you call the models truly enterprise grade.
-
-
Semantic model is not the same as a data warehouse
-
A data warehouse is built to standards conforming to the industry business unit from which the data is sourced. It is usually designed using dimension modelling using techniques of conformed dimensions, slowly changing dimensions, enterprise bus matrix etc. However, it may not always be used as is by the business for reporting. For starters the field names in the data warehouse may be something like “MemberAccountBalance_Total”, which may not mean much to the business. However, in the semantic model you can change it to something like “Member Account Balance” which is understandable for everyone.
The other reason is more important. The fact tables in the data warehouse in most cases cannot be used directly to extract the corporate measures. For ex: in Superannuation and Banking industries, one of the most important measures is the Member Count, both current and historical. The Member Counts here represent Members who have an account in the superannuation or banking organisation. The Member Count should be increasing over time as that means the company is growing and is able to make a profit. This measure is normally not modelled as a field in the fact table in the data warehouse. Hence a semantic layer view needs to be created so that this measure is derived by say joining the Member dimension (which has information on all member attributes) with other fact tables like MemberBalanceSnapshot table (which has information on members who have accounts) along with the required dimensions so that it can be sliced appropriately.
-
-
Be prepared for multiple iterations
-
If you are building a semantic model for the first time, you will probably need a few iterations to get it right. The model which you ultimately choose will be the one which has the right granularity for corporate reporting, has the business approved field names, simpler DAX expressions, refreshes faster, queries faster and is well documented. It is recommended to start the iteration process by focusing on the correct field names, table names, measure names and definitions and begin by designing a high-fidelity prototype with the important fact, dimension tables and measures. The prototype should be parameterised so it can be deployed to different environments easily. This way you will have a version which will be ready to scale when you deploy the final model for production.
-
-
Semantic modeling is not technically difficult
-
While the job of a semantic modeler is technically not that demanding, say as compared to a data engineer it is probably the role with the most exposure to all stakeholders. You are like a conduit between the business and the technical team. You are constantly fielding questions on the semantic model from the report developers/business, and it is your job to make sure their questions are answered satisfactorily.
Requirements for new tables, columns, measures may come thick and fast and that is why it is critical to ask the right questions so that these requirements can be formalised through a demand portal and taken up by the technical team based on their priorities. Which brings us to the next point…
-
-
Have an excellent relationship with all relevant stakeholders
-
Because a semantic modeler sits between business and the technical team, they should maintain excellent relationships with all parties involved. They should not only be adept at talking to the data modelers and architects on things like measure definitions, table grain, memory usage, processing times etc. they should also be skillful at explaining the model in simple terms while talking to business.
You need to be persuasive to make your point while also seeking clarity to better inform your decision-making process. These are some of the soft skills which you need to master in order to be successful in this role.