You are viewing today's estradaci.com design. See the new design

SSRS Custom Authentication Part 1

Authentication with the Report Server

SQL Server Reporting Services (SSRS) offers several configurable options for authenticating users and client applications against the report server. By default, the report server uses Windows Integrated authentication and assumes trusted relationships where client and network resources are in the same domain or in a trusted domain. Depending on your network topology and the needs of your organization, you can customize the authentication protocol that is used for Windows Integrated authentication, use Basic authentication, or use a custom forms-based authentication extension that you provide. Each of the authentication types can be turned on or off individually. You can enable more than one authentication type if you want the report server to accept requests of multiple types.

All users or applications who request access to report server content or operations must be authenticated before access is allowed.

When Windows Integrated Authentication does not meet the requirements

In one of our projects we had a scenario where Windows Authentication would not help us to meet the project requirements.

The project was using ADFS to authenticate users from different organizations in a single MVC Application, once the users were authenticated, they should access SSRS based on their ADFS Username and Role claims.

Per Microsoft definition SSRS does not support Single Sign On technologies, so what to do? To solve this problem a Custom Authentication was implemented.

Custom Authentication Flow

Before we start going through the technical implementation is important to understand the authentication flow we used for this scenario.

User Registration Process

See below how the User Registration Process works:

  1. User authenticates in the AD FS
  2. AD FS returns Organization, Role and Username claims
  3. The App will look for the Organization in the Organization Table
  4. The app will create the user in the Users table linked to the Organization
  5. The app will get the AD FS roles and look for them in the OrgGroupRole table
  6. For each role found in the OrgGroupRole Table, the app will map them to SSRS Roles based on the OrgGroupRoleSSRS
  7. Once the mapping is done the app will call SSRS web services to set the policies for the user in the Organization`s folder (each organization will have its own folder in SSRS)

Database Model

Let’s look at the Database model we created to map AD FS Claims to SSRS.

This model is used to create users and map the organization roles to real SSRS roles. See the table’s details below:

  • Organization Table – As it was told in earlier, AD FS will integrate with different organizations. This table will store the Organizations and the SSRS Folder path for the organization
  • Users Table – This table will store the users of the organizations
  • OrgGroupRole Table – This table will store the organization roles provided by AD FS claims
  • OrgGroupRoleSSRS Table – This table will relate the organization roles to real SSRS roles

 

Continue Reading Part 2 

Re-Thinking Communication on Complex Projects Part 1

Since the initial Chaos studies by the Standish Group in 1995, the industry has been trying to resolve the
high failure rates of projects. Since most organizations trying to address the problem were associated
with project management, improvements were recommended in the entire project environment with a
high-level of emphasis on the project manager to ensure the right conditions exist. In 1995, when I
reviewed the results of the Chaos study and the many lessons learned published from industry sources, I
created a term I called “Informed Misperception.” Today, I still believe that this one factor is the root of
many project problems.

What is Informed Misperception (IM) and what causes it?

Just think about how many messages you interpret from others in a given day. Now recall how many of
these may have had a misinterpretation at first that needed to be clarified and explained. This is a
natural phenomenon of communication. In projects, communication between some parties needs to be
complete and precise. Rarely, however, do individuals feel like they have the time to get to complete
and precise communication on many points that matter. In fact, there are many conditions that worsen
the actual level of completeness or precision each individual delivers.
Consider the following conditions:

 A technology designer is in a meeting to communicate design.

1. The design individual’s supervisor or lead is not in the meeting.
2. Few, if any, peers exist for interpretation of the design.
3. Parties that have to use the design to complete implementation are present.
4. There are many individuals in the meeting.
5. The design individual is remote and the meeting is a virtual session.
6. The meeting is scheduled for an hour.
7. The content of the design took over 3 weeks to define.
8. The documentation for the design is not detailed.
9. The design individual is a contracted staff member of the implementation vendor.
10. The business users are present; they intend to ensure their requirements would be met
with the design.

While the list of conditions could expand from the list above, the intent of this document is to explain
enough of the problem to recommend a solution.
What is the potential impact for each of these conditions?
For the numbered items above, here is a brief explanation of only one or two scenarios with each that
could lead to misperception. Note that in all cases, the design individual is likely not trying to be vague
or uncooperative; however, when time is short and demands are high, the level of stress for an
individual can build to a level that requires a relief valve to open – more on this after the explanations
for each condition.

1. The design individual’s supervisor or lead is not in the meeting: when technical supervision is not
present, there is a chance that the individual’s design is not an internally approved design nor that
the formal process for design has been followed. This may have no bearing on the quality of the
design; however, systemically unsupervised design tends to be less focused on what the end system

needs to do and is more focused on technical accuracy. This means the system could be doing the
wrong thing very well. Lapses in supervision have been known to drive poor design or
implementation since the individuals can mean to do the right thing, but have less incentive to
check their own work.

2. Few, if any, peers exist for interpretation of the design: when individuals available to receive or
interpret design, if their skill level for the kind of design being presented is low, the design individual
should try to alter the presentation to explain not only what the design is, but why the design is like
it is. This is to help participants to understand how the design allows the implementation to achieve
what the business needs. Of course, this takes additional time, and most individuals will have the
pressure of time constraints that would lead to short-cuts in explanations. This is a dangerous
situation if you have less than adequate design resources. Knowing that the participants are not
able to interpret the design, the explanations can be vague and the answers to questions can also be
vague and superficial so that the details will always have to come later (or perhaps never). Even if
the designer is excellent, they might not understand the business enough to consider some aspect
of design that should have been incorporated. Due to time constraints, the details are often left for
later discussions for which there seems never to be enough time.

3. Parties that have to use the design to complete implementation are present: while this is a very
good practice, the more parties in the room can mean that the people who really need to
understand the details of the design might not have enough time to get what they need. Formal
meetings hinder the right kind of progress, at times. When the details are discussed and questioned
(is this what you mean, is that what you mean) someone very familiar with the business needs to
ensure that the business intent is still going to work with the details that are being clarified. This
level of clarification rarely takes place, but is the most essential. If business users review
implementation every two weeks and get to adjust the functionality to their needs, this might not
be a problem. If the design is followed with many months of implementation prior to business
review, there is a lot more risk that the design, as implemented, won’t do what the business needs.
In public, formal meetings, when detailed questions of the design may not have answers, the actual
answer can be vague. Implementers start to make assumptions and add details they believe are
accurate to which an incomplete designer could respond a simple “yes.”

4. There are many individuals in the meeting: again, as the meeting grows in participation, the time
becomes less to address all concerns or clarification needs. The conversation tends to elevate to a
higher level to complete all conversations at one level that would never provide the details
necessary to truly interpret completely. The designer also may not get the feedback required to
ensure the design can be adjusted, where needed. The higher level of individuals in the meeting or
design session can also imply that other organizations are trying to cover their responsibilities
through participation. Coupled with other of these conditions, all individuals will tend to say enough
to ensure their point was heard or documented, yet often is insufficient to convey all of the meaning
since there might be objections individuals do not want to address in this large of a meeting. This
can lead to much more ambiguity.

5. The design individual is remote and the meeting is a virtual session: virtual meetings make
communication much more difficult. It is bad enough with language barriers and then the tendency
to be more vague to avoid questions, and other techniques identified here; but, with virtual
participation, the body language (over 80% of true communication) is missed and participants don’t
feel the connection. Often virtual participants begin to do other work and are truly just “phoning it
in” for that session.

6. The meeting is scheduled for an hour: most meetings are scheduled in a way that often does not
consider what is to be reviewed. An hour can easily be wasted with meaningless, high-level
discussions. For design details, it is best to isolate a particular area and to set a time appropriate for
that area. 20-40 minutes is recommended due to adult attention span issues. Small groups that
understand the area under discussion (from the business and technical perspective).

7. The content of the design took over 3 weeks to define: there could be a lot of information that
needs to be communicated. Since even an hour can be challenging to allow for attention span,
many meetings may be needed to clarify what the design means. If many people are involved, the
time required will increase which indicates a need for limiting detail or taking other short cuts to get
through the reviews. It is better to review in short increments than to try to complete an entire
design and try to get all of it approved at once. At that point, the detail will likely have difficulty to
get the attention needed.

8. The documentation for the design is not detailed: more and more designs are relegated to user
mock-ups or wire frames with little consideration for the detailed handling of states or data behind
the scenes. If the details are not available, those who implement will make up their own mind and
even greater communication with the business will be required. Expect many changes if review and
update cycles are short. Expect an insurmountable set of changes if review cycles are long and
following significant implementation. Talking about design with little detail in writing, diagrams, or
other representations can be very misleading. Discussions could be endless and this actually causes
individuals to start agreeing to everything, whether right or wrong, just to get out of the meeting.

9. The design individual is a contracted staff member of the implementation vendor: some contract
professionals are highly responsible to get everything right. Some tailor their effort to the vendor
oversight and do what is necessary to get paid. The days where all participants were employees and
where employees were really concerned with corporate performance or reputation are gone. If the
people you are dealing with have less than adequate integrity, you might never know it until it is too
late. Test their ability to make things right to see what you are dealing with.
10. The business users are present; they intend to ensure their requirements would be met with the
design: this condition is generally a great step to take to ensure that the end product will meet
expectations. If this level of understanding is possible, it is more likely in smaller increments and
should be immediately followed by implementation since the gap in understanding between design
and implementation could be wide. Still, business needs and designs may seem aligned when they
are not. The explanations to show how the design will result in delivering the business needs should
take place. This level of discussion becomes, again, burdensome and not possible in a single
meeting for a large design.

In all, you may begin to understand that the level of detail required at implementation level needs to be
crystal clear and validated quickly. The Informed Misperception term was coined for all cases where the
details become a little too burdensome to explain. The design individual will claim to have informed all
parties in the sessions; yet, the level of true understanding was never achieved and the greater detail
with understanding might never have even been attempted. I have yet to see a technology
implementation project where this does not happen.

Continue Reading Part 2 

Rethinking Communication in Complex Projects Part 2

Why haven’t the traditional top ten areas of emphasis over the last 25 years resolved the problems
with project success rates?

The depiction below is a compilation of the Chaos study recommendations published over the years.

In 1994, as the first Chaos study was being published, I had 20 years of experience in project
management. I was certified in the Department of Defense (DoD) at the highest level and managed
projects in the billions of dollars. I could not argue that all of the recommendations above were
anything but appropriate at that time. I also could not see how the recommendations could resolve
what I saw was evolving particularly in larger projects. IM was phasing in at the time and accelerated in
the early 2000s when the dot com bubble occurred due to the significant turn away from details and
documentation toward a move fast, try something, break things fast and refactor, and other traits that
pushed the level of engineering to the side for many companies. Once the engineering skills were lost,
then the management layer also failed to understand the level of detail necessary for a system to
succeed and the tendency for IM to be prevalent became closer to rampant.

I still can’t argue against most recent recommendations. A team can implement every one of these
recommendations and still fail dramatically. These recommendations seem targeted at making a lot of
people feel more comfortable while they are failing. None of them are bad and most of them will help
in one way or another, but the product may still be very challenged to meet expectations unless the IM
problem is resolved. Since 2005, I have implemented a personal set of practices to counter the impacts
of IM. I have done this on my own teams and on other vendors’ teams as well. Where the practices
were resisted by other vendors, the projects were still colossal failures. Where the practices were
embraced, the products were successfully deployed and most are still in operation today.

What were the practices to counter IM?

The practices to help counter the effects of IM are focused on clarification and reduction of ambiguity in
any project. Complexity of projects will require greater diligence in using the practices with multiple
teams.

The practices include:

 Keep small teams collocated and increments of progress small.

 Coordinate business agreement with requirements, acceptance criteria and designs prior to
coding.

 Verify code implementation incrementally for business usability to achieve objectives.

When complexity and size become part of the equation, a couple of other practices are essential:

 Leads for teams concurrently develop modular design.

 Use Foundation First principles to ensure each module is in harmony with others for: data
owned and used, communication of state and action, consistent usability, and security.

Review again the top ten recommendations in the section above. Some of these practices might be
considered part of these ten recommendations; however, without being explicit, it is possible to put all
of the recommendations in place without incorporating any of these practices. Management will need
to learn that doing things that tend to make them feel good without seeing the product incrementally
satisfy the business need is just placating behavior. The practices are explained below with greater
detail to enhance use of the practices and to recommend moving away from management placating
behavior to practices that will remove ambiguity and tame complexity focused on delivering business
results.

What are the detailed explanations for the practices to defeat IM?

While detailed explanations follow, it is important to treat these explanations like project requirements
and take them through the same practices to ensure the all users are clear on what is required. We
have to stop acting like communication is perfect and everyone understands what I am communicating,
even when I don’t get to details.

1. Keep small teams collocated and increments of progress small: It is wise to keep teams
collocated. If all disciplines are not possible to collocate, then developers are most important.
The developers should also be full time. Less than full time is highly disruptive to quality of
implementation. You can have multiple small teams, but then ensure that practices 4 and 5 are
incorporated. Increments of progress should also be small. Small is relative. Agile would use a
two-week time box of effort. Shorter would not be appropriate. Longer might be necessary
with a lower competency team that is learning, but I would not recommend anything greater
than four weeks. It is too difficult to perform the other practices at the level of detailed
coordination and level of understanding required to ensure success.

2. Coordinate business agreement with requirements, acceptance criteria and designs prior to
coding: Activity is required to ensure that business requirements indicate what a user needs to
do within the system being implemented to achieve their work objectives. Always clarify each
requirement with acceptance criteria. If you are going to be true to the practice #1, the
requirements also have to be broken down to a level where they could be completed within the
small increment of progress. Then, design documentation for the increment has to be available.
The documentation, at first, can be draft versions that the business agrees could fulfill the
requirement and the team understands sufficiently to implement. Inevitably some of the design
will need to change, but without initial drafts, the likelihood of successful implementation is low.
Every resource would just implement what they believed to be right; however, without
coordination all resources could be implementing in a different way. The design is just enough
to prevent that opportunity to diverge. A more complete design can be done for the as built
capability when complete.

3. Verify code implementation incrementally for business usability to achieve objectives:
Acceptance test needs to occur with each increment at the level for which the acceptance
criteria were defined. It is OK to identify new requirements or criteria for future increments.
Acceptance can be conditional on the aggregation of capability to fulfill the business needs so
that the system would have to eventually be a complete and operational system. Without
incremental acceptance you cannot verify that you are on the correct path and satisfying what
the business needs in pieces. You also cannot give the business the opportunity to continue to
evolve their needs with the implemented system, which is often critical to get to an acceptable
final system. Technical resources should also test and verify that algorithms and business rules
are appropriately applied in these same small increments so that corrections can take place
early and often.

4. Leads for teams concurrently develop modular design: When a system exceeds one small team,
it is best to organize with multiple small teams focused on separate sections or modules for an
application or system. The teams coordinate their internal work and the leads of the teams
meet to ensure the boundaries for sections they own are clear for all other pieces under
development. At the point of integration testing across teams, include the business as well to
ensure the totality of business expectation is clarified for the way the solution works overall.

5. Use Foundation First principles to ensure each module is in harmony with others for: data
owned and used, communication of state and action, consistent usability, and security:
Multiple teams that do not communicate have proven to be a disaster. They key to
communication though is not a set of random meetings where IM takes place. Each team needs
to have boundaries established to prevent the problems that generally occur. Systems often
duplicate data. Only clear definition of which system or module owns responsibility for which
data can avoid this problem. Systems often fail to interoperate. Only clarity surrounding the
means used to coordinate the state of items shared and the sequence of activity where
meaningful can help to control this situation. Usability is in the eye of the users and consistency
requires feedback across teams to help implement business desires. The Americans with
Disabilities Act (ADA) standards need to be applied from the very first screen built so as not to
build a change nightmare for later. Standards can help the most with security; and, sharing
techniques applied in code will maintain consistency throughout an application.

Conclusion:

Improvement focus at a management or project management level is always a good thing to engage in.
Believing that this will improve the level to which technology projects are delivered is significantly
misleading if control is not placed at the communication level of implementation between the business
and implementation team. While the top ten recommendations may make an organization feel better
as they attempt project implementations, the right practices to reduce the most significant problem of
communication are much more essential to success. Informed Misperception is a significant culprit in
technology implementation projects for many reasons. These reasons must be overcome with the right
implementation practices.

Automated Testing For Data Migration

Data Migration, Part 4/4, Automation

Data Migration Testing revolves around testing the integrity of Data, content and the quality of data between Source and Destination DB based on the given mapping rule.

Broadly, any data Migration has the following:

– Table level mapping

– Element level mapping (column level)

– New elements in the New DB

– Ignore few old elements from the Legacy DB

– Not to be converted data from Legacy to the New system.

– Count Check

Table Level mapping defines how the tables from the old DB should be mapped to the new DB. Based on the mapping rules and how the new system is designed, the table level mapping could be one to many or many to one or one to one.

Element Level mapping defines how each column in the legacy system is mapped to the columns in the new system and this could be also be one to many, manty to one or one to one.

New elements in the new DB are the columns which is very specific to the New DB and not existing in the Legacy

Ignore elements from the Legacy system are the columns which are being exempted from the mapping rule and never brought to the new system.

Not to be converted data are the records from the Legacy DB which are being exempted from getting converted to the new DB for whatsoever reason.

Count Check This is one of the most important validation check to ensure that the source count is in sync with the target count check based on the given mapping rule.

Essentially every automated/manual testing for Data migration projects focuses on the above validation points.

The very fact that we deal with millions and millions of data, manual testing of data migration project can be next to impossible and the situation demands  an automated process and everytime there is a new extraction of data and any changes to the code, we should be able to trigger the automated scripts to check the sanctity and integrity of the data.

This could be achieved with a bunch of SQL scripts which compares the Source data to the Target data based on the mapping rule against each source and target tables.

A simple methodology without using any expensive off the shelf testing tool is to use Excel Macros.

– Get the macros in Excel to connect to Source and Target DB.

– Store the SQL scripts which  validates each table and element level mapping in the macros

– Store the result of the execution back to the Excel which basically confirms if the mapping passed/failed.

The Pros:

1. User-friendly and doesn’t need anyone to learn and understand the complexities of some off -the shelf expensive tools.

2. All the sql scripts being used as part of the test execution are stored at one place and are quite easy for reference.

3. Any mapping rule changes would mean a change in the SQL script and it’s easy to go and make the SQL changes to the corresponding script.

4. It’s very likely that the testing environment (source and destination DB) could change with every execution depending on its availability and the same could be configured in the Excel as a drop down.

5. As the actual execution happens against the DB, validating millions of data is not a challenge and the excel row limit doesnt kick in as only the results of the execution gets stored back to the Excel.

6. Its quite easy to filter the failed test cases and focus on the issues and re-run the same once the issues are fixed.

7. We can even configure the macros to run for specific set of tables, failed test cases rather than going for the entire test suite based on the regression cycle needed for a specific run thereby saving time.and effort.

8. As its excel we could get some fancy reports once the run is complete without depending on some expensive tools and its reporting services.

9. We could even integrate this with the unit test cases of Development team and run it as and when they complete their coding for each tables thereby delivering the QA much matured and bug free code.

10. On an average, 2000 test cases with as much SQL queries takes about the 30 mins to execute and report depending on the load on the DB at a given point of time which signifies the amount of time getting saved as against executing as much test cases manually.

11. All this without having some expensive data migration testing tools thereby saving a lot of cost to the project.

Conclusion

The major factors which should drive QA automation for any project and not particularly migration project is a cost-benefit analysis. It makes sense to go for expensive off-the shelf automation tools if and only if the returns justify the investment. As long as if we can achieve even 80 percent of the efficiency by any in-hand existing inexpensive tool, we should always shoot for the same and get the best out of it.

As for Data Migration project, the biggest challenge is always the sheer quantity of data to be tested and getting a quality output. Most of the times, the very databases where the data gets loaded with a smart combination of excel macros could achieve the desired results.

Data Migration Part 3 of 4

Test Scenarios, in general, would be as below:

I)​ If the migration is to the same type of Database, then,

  • Verify if the queries executed in the new database yield same results as in the older one
  • Verify if the number of records in the old database and new database is the same. Here use appropriate automation tool
  • Verify that there are no redundancies and new database works exactly as the older one
  • Verify if the schema, relationships, table structures are unaltered or set back to match the old database image
  • Verify whether the changes made in application updates new database with correct values and type
  • Verify if after the new database connection is provided to all the components of the application. Application, server, interfaces, firewall, network connectivity etc.
  • Verify the query performance (time-taken to execute complex queries) of the new database is not more than earlier performance

Challenges faced in this testing are mainly with data. Below are few in the list:

#1) Data Quality:

We may find that the data used in the legacy application is of poor quality in the new/upgraded application. In such cases, data quality has to be improved to meet business standards.

Factors like assumptions, data conversions after migrations, data entered in the legacy application itself are invalid, poor data analysis etc. leads to poor data quality. This results in high operational costs, increased data integration risks, and deviation from the purpose of business.

#2) Data Mismatch:

Data migrated from the legacy to the new/upgraded application may be found mismatching in the new one. This may be due to the change in data type, format of data storage, the purpose for which the data is being used may be redefined.

This result in huge effort to modify the necessary changes to either correct the mismatched data or accept it and tweak to that purpose.

#3) Data Loss:

Data might be lost while migrating from the legacy to the new/upgraded application. This may be with mandatory fields or non-mandatory fields. If the data lost is for non-mandatory fields, then the record for it will still be valid and can be updated again.

But if the mandatory field’s data is lost, then the record itself becomes void and it cannot be retracted. This will result in huge data loss and should have to be retrieved either from the backup database or audit logs if captured correctly.

#4) Data Volume:

Huge Data that requires a lot of time to migrate within the downtime window of the migration activity. ​E.g:​ Scratch cards in Telecom industry, users on an Intelligent network platform etc., here the challenge is by the time, the legacy data is cleared, a huge new data will be created, which needs to be migrated again. Automation is the solution for huge data migration.

#5) Simulation of a real-time environment (with the actual data):

Simulation of a real-time environment​ ​in the testing lab is another real challenge, where testers get into different kind of issues with the real data and the real system, which is not faced during testing.

So, data sampling, replication of real environment, identification of volume of data involved in migration is quite important while carrying out data Migration Testing.

#6) Simulation of the volume of data:

Teams need to study the data in the live system very carefully and should come up with the typical analysis and sampling of the data.

E.g:​ users with age group below 10 years, 10-30 years etc., As far as possible, data from the live needs to be obtained, if not data creation needs to be done in the testing environment. Automated tools need to be used to create a large volume of data. Extrapolation, wherever applicable can be used, if the volume cannot be simulated.