Posts
Hello again.
Today I want to talk about a scenario we face in our project. In terms of OBIEE, we know we have to create reports and dashboards to show data. How to ensure we show the right data? Data means nothing when the concept of time is out the windows, specially when it comes to performance related data. In other words, when you are reporting your sales performance, financial performance, you always have to look at a specific time or time range in order to have the number make sense to you. How do you ensure you are getting the right data is always something we have to guarantee in our design.
Let's take the following scenario:
I have an overview dashboard that the goal is to show the list of objects or entities that I have in my system. In my case, show me a list of network devices such as LSP cables, Pseudowires, Nodes that are active within 1 day's hourly range. Then from overview dashboard, we will look at the performance of each devices by throughput, utilization and drops over a range of dates and hours.
In terms of data modeling, we will have models like the following:
Overview report queries: LSP Dim --> LSP_PWE_CONFIG_F--> Pseudowire Dim
In this case, LSP_PWE_CONFIG_F is more of an mapping table that join the 2 dimensions when they are N:N relationship.
Performance reports queries: LSP Dim --> LSP_PWE_CONFIG_F--> Pseudowire Dim --> Pseudowire Fact
Here in this case, it is a little bit of snowflaking, but eventually we will get the throughput, Utilization of pseudowires that would add up to LSP.
So when we have this model, how are we going to check against the date dimension to ensure you have the right data?
From a developer's perspective, you don't have to worry such thing as you would pretty much do what the design document tells you. However, that would only get you this far. If you want to think beyond developers, you have to look at it from an architect's perspective. This is what I am focusing in this article. This isn't about where to click and what code to write, it is about how to make the design.
So here are a few things we have to take into consideration before making the design.
1. How often are the dimension tables updated? Does it keep history or not?
2. How often are data loaded into LSP_PWE_Config_Fact?
3. How often are data loaded into Pseudowire Fact or any performance fact?
4. What does date key in date dimension and fact table have?
In other words, you can't just blindly join the date dimension to both fact or any one of the fact table and be done with it. You have to think in terms of architect and really understand how would it work when you make that join.
Next time, we will continue on how this scenario is implemented
Until next time
Today I want to talk about a scenario we face in our project. In terms of OBIEE, we know we have to create reports and dashboards to show data. How to ensure we show the right data? Data means nothing when the concept of time is out the windows, specially when it comes to performance related data. In other words, when you are reporting your sales performance, financial performance, you always have to look at a specific time or time range in order to have the number make sense to you. How do you ensure you are getting the right data is always something we have to guarantee in our design.
Let's take the following scenario:
I have an overview dashboard that the goal is to show the list of objects or entities that I have in my system. In my case, show me a list of network devices such as LSP cables, Pseudowires, Nodes that are active within 1 day's hourly range. Then from overview dashboard, we will look at the performance of each devices by throughput, utilization and drops over a range of dates and hours.
In terms of data modeling, we will have models like the following:
Overview report queries: LSP Dim --> LSP_PWE_CONFIG_F--> Pseudowire Dim
In this case, LSP_PWE_CONFIG_F is more of an mapping table that join the 2 dimensions when they are N:N relationship.
Performance reports queries: LSP Dim --> LSP_PWE_CONFIG_F--> Pseudowire Dim --> Pseudowire Fact
Here in this case, it is a little bit of snowflaking, but eventually we will get the throughput, Utilization of pseudowires that would add up to LSP.
So when we have this model, how are we going to check against the date dimension to ensure you have the right data?
From a developer's perspective, you don't have to worry such thing as you would pretty much do what the design document tells you. However, that would only get you this far. If you want to think beyond developers, you have to look at it from an architect's perspective. This is what I am focusing in this article. This isn't about where to click and what code to write, it is about how to make the design.
So here are a few things we have to take into consideration before making the design.
1. How often are the dimension tables updated? Does it keep history or not?
2. How often are data loaded into LSP_PWE_Config_Fact?
3. How often are data loaded into Pseudowire Fact or any performance fact?
4. What does date key in date dimension and fact table have?
In other words, you can't just blindly join the date dimension to both fact or any one of the fact table and be done with it. You have to think in terms of architect and really understand how would it work when you make that join.
Next time, we will continue on how this scenario is implemented
Until next time
0 comments:
Post a Comment