Benefits of Working with a Microsoft BI Gold Partner

MS-Gold-BI Benefits of Working with a Microsoft BI Gold PartnerRecently we were certified as a Microsoft Business Intelligence Gold Partner in Vancouver, British Columbia. This new partnership provides many benefits for our clients, which include:

  • Access to Microsoft software and in-depth product information that helps to guides clients to the right tool and strategy for their Business Intelligence initiative.
  • Various training materials, which help facilitate workshops on Microsoft’s platform and tools with the client.
  • Virtual labs and independent study resources that can be used as reference points when discussing a client’s technical problem.
  • For any client-related technical issues, Microsoft offers a specific number of hours of Technical Enablement and advisory services from Microsoft consultants.

Additionally, clients will find it cost effective to engage with a Microsoft Partner that provides umbrella services (i.e. .Net development, BI, etc.), instead of hiring an individual for each requirement.

Microsoft’s definition of a Gold Partner:

“Gold competencies demonstrate your best-in-class expertise within Microsoft’s marketplace. Earning a gold competency is evidence of the deepest, most consistent commitment to a specific, in-demand, business solution area, along with the distinction of being among only 1 percent of Microsoft partners worldwide that have attained this outstanding degree of proficiency.”

OptimusBI has extensive experience, ranging from reporting services to data management, helps clients set themselves apart from their competition. To provide world-class service, we adhere to a proven methodology, provide a superior client experience, and become a true business partner on every project. We bring a broad depth of industry, business, and technology experience to projects. Our team has a focus on customer satisfaction, productivity, and quality of work. OptimusBI views our Microsoft Gold partnership as an integral part of our offerings.

Building SSRS Reports With Multiple Data Sources

SQL Server Reporting Services (SSRS) allow users to create reports using data from a variety of sources, including relational databases, multidimensional databases, web services, XML, and other document types. Using SSRS, we can present data from multiple data sources in a single report.

Methods to achieve this include:

  • Using sub reports
  • Using Openrowset queries.
  • Using Report Builder functions like Look Up, Look Up set, and Multi look up.

In this post we will look at Look Up and Multi Look Up functions.

Consider a typical scenario in which we have two different database instances:

  • MS SQL Server Instance – This instance holds the records for customer sales.
  • Oracle Server Instance   – This instance holds the records for customer information.

The SQL server would have customer table with records like:

Dbo.CUSTOMER_TRANS

customer_id

trans_date

amount

1233

2012-03-01 00:00:00.000

100

2708

2012-04-05 00:00:00.000

150

3421

2012-07-22 00:00:00.000

120

8765

2012-08-27 00:00:00.000

109

The Oracle server would have Customer tables containing customer information like:

CUSTOMER_INFO

cust_id

Name

Email

1233

John

john@xyz.com

2708

David

david@xyz.com

3421

Michael

Michael@xyz.com

8765

Jack

jack@xyz.com

In order to fill data from these two sources into a single report, we would follow these steps:

  1. Create and select data sources (that are already present on your report server):

Untitled-1 Building SSRS Reports With Multiple Data Sources

Untitled1 Building SSRS Reports With Multiple Data Sources

  1. Create a dataset using Sql server data source (db_Customer_SqlServer):

Untitled-4 Building SSRS Reports With Multiple Data Sources

And similarly create a dataset using Oracle Data source:

Untitled-3 Building SSRS Reports With Multiple Data Sources

  1. Add a table to the report and select the SQL server dataset:

Untitled-2 Building SSRS Reports With Multiple Data Sources

In the report add any columns that you want to display:

Untitled Building SSRS Reports With Multiple Data Sources

You’ll see that names and emails are not populated from the SQL server dataset. To fill out names and emails we have to refer Oracle server dataset.

Right click the name cell and go to values. This will open an expression builder screen.

Write an expression along the lines of this:
=Lookup (Fields!customer_id.Value, Fields!cust_id.Value, Fields!Name.Value, ds_Customer_Oracle)
Basically the syntax is:
=Lookup (source expression, destination expression, result expression, dataset)
We can perform the same actions for the email value, therefore when we run the report we will get the desired outputs.

The Look Up instead returns a single value only; in this case it will return a name corresponding to a customer ID that it pulls from the SQL dataset. Similarly, Lookupset return an array of values for a particular condition.

If you’re interested in learning more comment below or contact us at info@optimusinfo.com

Multilevel Cascading with Select All

The long-standing issue with cascading parameters in SSRS is that when changing the selection of the “parent” parameter, the default selection of the dependent parameter is not always automatically changed.

After a considerable amount of R&D and considering the report cache, I have found a solution/workaround which not only solves the issue at hand, but also addresses up to 4 levels of cascading.

To address this issue, you need to write a query in your dataset in a manner so that the dependent parameter changes its value every time you change its parent parameter.

I used Northwind database (datasource) for testing.

The steps are as follows:

1. Open Report Builder.
2. Create data source.
3. Create datasets.
4. Create parameters.
5. Assign parameter values to datasets.

Here is the screenshot of what this process will look like:
Multilevel-Cascading-with-Select-All-1 Multilevel Cascading with Select All

 

Data Source

1. Right click on the Data Source folder in the left window.
2. Click on Add Data Source.
3. The following screen will appear:
Multilevel-Cascading-with-Select-All-2 Multilevel Cascading with Select All

 

Datasets

Dataset 1: Country

SELECT DISTINCT Country

FROM Northwind.dbo.Customers

ORDER BY Country ASC

 

Dataset 2: City

SELECT l11.City1+’_’+ CAST(row_number() over( order by  l11.city1 desc) AS VARCHAR(50) )as city11

,l11.City

FROM (

SELECT l1.City+’_’+ CAST(row_number() over( order by  l1.City asc) AS VARCHAR(50) )as city1

,l1.City

FROM (

SELECT DISTINCT City

FROM Northwind.dbo.Customers

WHERE Country IN (@Country)

)l1

)l11

ORDER BY l11.City ASC

 

Dataset 3: Company Name

SELECT l11.CompanyName1+’_’+ CAST(row_number() over( order by  l11.CompanyName1 desc) AS VARCHAR(50) )as CompanyName11

,l11.CompanyName

FROM (

SELECT l1.CompanyName+’_’+ CAST(row_number() over( order by  l1.CompanyName asc) AS VARCHAR(50) )as CompanyName1

,l1.CompanyName

FROM (

SELECT DISTINCT CompanyName

FROM Northwind.dbo.Customers

WHERE City IN(@City)

)l1

)l11

ORDER BY l11.CompanyName ASC

 

Dataset 4: Contact Title

SELECT l11.ContactTitle1+’_’+ CAST(row_number() over( order by  l11.ContactTitle desc) AS VARCHAR(50) )as ContactTitle11

,l11.ContactTitle

FROM (

SELECT l1.ContactTitle+’_’+ CAST(row_number() over( order by  l1.ContactTitle asc) AS VARCHAR(50) )as ContactTitle1

,l1.ContactTitle

FROM (

SELECT DISTINCT ContactTitle

FROM Northwind.dbo.Customers

WHERE CompanyName IN(@CompanyName)

)l1

)l11

 

ORDER BY l11.ContactTitle ASC

 

Dataset 5: Contact Name

SELECT l11.ContactName1+’_’+ CAST(row_number() over( order by  l11.ContactName desc) AS VARCHAR(50) )as ContactName11

,l11.ContactName

FROM (

SELECT l1.ContactName+’_’+ CAST(row_number() over( order by  l1.ContactName asc) AS VARCHAR(50) )as ContactName1

,l1.ContactName

FROM (

SELECT DISTINCT ContactName

FROM Northwind.dbo.Customers

WHERE ContactTitle IN(@ContactTitle)

)l1

)l11

ORDER BY l11.ContactName ASC

 

Parameters

Parameter 1: Country

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-3 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-4 Multilevel Cascading with Select All

 

Parameter 2: City

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-5 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-6 Multilevel Cascading with Select All

5. Now click on Default Values:
Multilevel-Cascading-with-Select-All-7 Multilevel Cascading with Select All

 

Parameter 3: CompanyName

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-8 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-9 Multilevel Cascading with Select All

5. Now click on Default Values:
Multilevel-Cascading-with-Select-All-10 Multilevel Cascading with Select All

 

Parameter 4: ContactTitle

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-11 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-12 Multilevel Cascading with Select All

5. Now click on Default Values:
Multilevel-Cascading-with-Select-All-13 Multilevel Cascading with Select All

 

Parameter 5: ContactName

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-14 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-15 Multilevel Cascading with Select All

5. Now click on Default Values:
Multilevel-Cascading-with-Select-All-15 Multilevel Cascading with Select All

 

Now assign parameter values to datasets

Steps:
1. Double click dataset “CompanyName”.
2. The following screen will appear:
Multilevel-Cascading-with-Select-All-16 Multilevel Cascading with Select All

3. Click Okay for both windows.
4. Now double click dataset “ContactTitle”.
5. The following screen will appear:
Multilevel-Cascading-with-Select-All-17 Multilevel Cascading with Select All

6. Click Okay for both windows.
7. Now double click dataset “ContactName”.
8. The following screen appear:
Multilevel-Cascading-with-Select-All-18 Multilevel Cascading with Select All

9. Click Okay for both windows.
10. Now run the report by pressing F5.

If you are interested in learning more, please feel free to ask in the comments below or email us at info@optimusinfo.com

Best Practices: Starting a Successful BI Project

reporting-services-300x192 Best Practices: Starting a Successful BI Project

At the last event I attended, I participated in a discussion about business intelligence (BI) projects going over budget. It was mentioned that companies sometimes struggle to complete projects within a well-defined budget and time frame. In particular, the chances of associated support and maintenance costs operational BI systems was highlighted. Many people have found BI deployment time frames consistently exceeding their deadlines by one to six months. Organizations now fear making investments that will improve their business intelligence competency, mainly due to several associated hidden costs. Consequently, executives are seeking quick ROI results from on their BI projects, which has resulted mainly in requiring BI deliverables to be supplied in weeks rather than months.

Based on my experience working on BI projects, the following five key points have helped my team and I deploy several enterprise-grade projects on time and budget.

  1. Realistic Estimation of Project Duration: Many vendors claim their solutions are customized and able to be deployed in a matter of days or weeks. However, this is only possible if there is a well defined process for data cleaning and integration already in place prior to the vendor’s involvement. Ideally, these processes are automated, otherwise deployments projects can take several weeks, months or even longer. Deadlines need to be identified and discussed during the planning phase.
  2. Availability and Involvement of End Users: End users play a key role in identifying and streamlining data needs and identifying what insights data should provide. Therefore companies should create a BI stakeholder committee that includes individuals from management, IT and end users from various departments.
  3. Proper End-User Training: End user training plays a vital role in the acceptance and adoption of any BI system. Creating a formal training process an environment where new users can lean about the BI system is crucial. However, training programs do add cost and time to a project, therefore estimations should be considered well in advance.
  4. BI Delivery: Delivery is a key component of any BI application – standalone, integrated, and delivered across the organization. The requirements should be listed with the help of end-users so any hidden costs can be prevented. The budget and resources associated with infrastructure and used in the delivery phase should be identified during the pre-planning phase to avoid any unexpected errors during deployment.
  5. Reoccurring Costs: BI requirements constantly evolve for every organization, therefore a solution’s adaptability to the changes (and its relevant maintenance costs) should be considered when budgeting for an initiative.

OptimusBI provides business intelligence and reporting services services in Canada. We specialize in developing cross-platform and cross-application solutions. If you would like to learn more about BI services and experience, please feel free to contact us. We will be happy to sit down and discuss your needs.

If you are interested in learning more, please feel free to contact me at: rupmeet.singh@optimusinfo.com.

Introducing OptimusBI!

data-warehousing Introducing OptimusBI!

A video overview of OptimusBI.

We are very proud to announce the official launch of OptimusBI!

OptimusBI is a practice within Optimus Information Inc. that focuses on business intelligence and reporting services. Under our general practice Optimus Information Inc., our team had worked on several BI and reporting projects. We have now started a practice dedicated entirely to BI and reporting projects.

To quote the research director at Gartner, Neil Chandler, “The market for BI and analytics is undergoing gradual evolution. By 2014, the metamorphosis of BI from IT-owned and report-centric will be virtually complete for a large number of organizations. These organizations will change what types of BI and analytics they use. They will change how they procure them and where they procure them from, and they will modify how information feeds decision making. BI and analytics leaders should embrace the technology, market and management trends that will transform this field within a few years.”

Business Intelligence is changing the world and we cannot deny that there has been a paradigm shift in the technology sector. Organizations are now utilizing the data they collect from various sources daily. Powerful new BI applications are changing the way we make business decisions. OptimusBI is thrilled to help global organizations take advantage of their data by analyzing it and creating new BI solutions for them.

As mentioned in our welcome post, this website is the new home of OptimusBI. Here we will share what we find exciting and interesting in the Business Intelligence industry and encourage readers to join the conversation by commenting on posts, and sharing posts on Twitter and LinkedIn.

As a practice of Optimus Information Inc., we have direct access to a diverse team of highly skilled developers, QA professionals, and BI specialists. This combination of skills enables OptimusBI to deliver enterprise-grade solutions.

If you have a Business Intelligence or Reporting initiative in mind for your organization, please do give us a call. We are always happy to casually discuss opportunities.

If you are interested in learning more, please feel free to contact me at: rupmeet.singh@optimusinfo.com.

Welcome

Service-Circle-BI-300x300 WelcomeWelcome to OptimusBI’s new website!

Our new website is geared towards everything BI and reporting related. Browse through our services section to learn about what we do; including report design, report development, report migration and report testing. Also, feel free to email us and we’ll call you back.

Take a look through our case studies and see how OptimusBI has helped several clients with their specific BI and reporting needs.

Our BI and reporting newsletter is going to be launched soon, so sign-up to hear about BI and reporting related updates within the IT sector. You can look through our archives to see what it is all about.

Our blog will focus on current BI and reporting reporting news, reporting tools, BI techniques, report testing tools and services, and mobile analytics. As well, information will be posted about events Optimus Information Inc. sponsors and hosts in Vancouver through out the year. Our next event is an information session on the reporting process; find out more here. Contributors to our blog reel will include members of our technology team, project managers, and guest writers.

We’ll begin updating our blog every month, so check back soon!