74 / 96
W H I T E P A P E R
© 2017 Persistent Systems Ltd. All rights reserved. 74
www.persistent.com
—
Leverage star schema optimizations at the DWas most DWsupport this by creating appropriate indexes to
filter individual dimensions first and then apply it to indexes on fact table to read relevant data rows
7.2.5 Performance at Deployment and support stage
—
Test for performance – create a test plan for performance use cases to be validated, decide on the data set
size, load, number of users, hardware to run on and what metrics to collect. It is best to test both ETL and BI
tools for desired loads and scalability. If possible, script the use cases in an automation tool. Apply
optimizations before running any tests like clearly the DB or BI tool cache, defragmenting or collecting the
stats.
—
Multi-user benchmarking – it is good to test the system under realistic workloads by simulating multiple
concurrent users accessing the data as well as loads happening simultaneously for near real-time systems.
—
Monitor and profile the long-running queries and measure performance as data grows. By doing so, assess
need for additional hardware.
—
Aggregation tables should be considered a performance-tuning vehicle. Build some aggregates, conduct
some performance tests, and then revise which aggregates you will use in production. From there, you need to
monitor aggregate use over time andmake adjustments accordingly.
—
Purge historical data after retention period fromaggregation tables.
7.2.6 Security at Requirements and planning stage
—
The DW/BI systemmust publish data to those who need to see it, while simultaneously protecting the data
( [1]page 83). Security cannot be an after-thought and should be discussed right in from the beginning with
business users and how each piece of the data will be accessed and by whom.
Making a matrix sheet, with
classification of data attributes as public, sensitive, private and roles/users access permissions is
considered very handywhile implementing data views and reports.
—
Document the real needs to secure data and do not go over-board as the DW/BI system is successful only if
people can access it. There is also cost and performance overhead attached to each layer of security we add.
Cost of deploying and maintaining security solutions should also be factored in. It is recommended to have
authorized access by striking a good balance between no security (all data is open and available) vs.
extremely secured system (most data is sensitive).
—
Secure from threats to business continuance
(page 174-177). Other vulnerabilities that may affect ability to
[1]conduct the business include: denial of service attack on the report server or database with huge number of
false requests or virus or Trojan horses halting the server, inability to reconstruct the consistent snapshot of the
software (ETL, Reports) from code due to loss of backups or corrupt files or simple oversight. Many of these
can be controlled with limited access on the network using routers, firewalls and security-specific devices that
monitor for malware behavior andmitigate their effects.
7.2.7 Security at Design and Technical architecture stage
—
Decide on ETL users and required minimum permissions to run the ETL jobs, it should not be with system
administrator or DBApermissions
(page 144-145).
[1]