OLAP PivotTable Extension

Recently I was working on a PivotTable Report with data from a SSAS Cube (Excel version 2010).
I needed a Profitability Trend calculation for a client, but this calculation was not available in the Cube. Because in Excel 2010 the functionality to create calculated measures is not exposed in the User Interface, I searched on the internet and found OLAP PivotTable Extension, a free add-in for Excel.

Click here to check out the features of OLAP PivotTable Extension.

CLARIFIED for Clarizen - Tableau

Probably you already knew that CLARIFIED for Clarizen is the reporting and analysis service for Clarizen users, offers rich dashboards and enables you to analyze your Clarizen data across all projects, using various criteria. All this within just a few mouse clicks.

But did you know it can be used as a source for bringing your reports and dashboards through Tableau?

Tableau
Tableau is a Business Intelligence software that provides browser-based analytics and reporting anyone can learn and use. It’s a rapid-fire alternative to the slow pace and rigidity of traditional Business Intelligence software. Click here for more information about Tableau.

Experience

After downloading Tableau, it just took me 5 minutes to install and connect Tableau to the CLARIFIED for Clarizen cube. Connecting to the cube was very easy with an Analysis Services Connection in Tableau. Within a view clicks I had instant reporting and visualization!

Besides using Tableau other third party Business Intelligence or reporting solutions like Dundas, Targit, QlikView or RoamBI can be used to create your own dashboards.

Interested in CLARIFIED for Clarizen? Please contact us via info@bi-architects.nl or visit our www.clarified.biz portal for more information and to start your trial of today!

BI Architects visited the Microsoft SQL Server 2012 symposium

Yesterday a delegation of BI Architects visited the Microsoft SQL Server 2012 symposium at the Media Plaza in Utrecht.

During the symposium Microsoft presented the new version of their informationplatform SQL Server 2012. Richard Chin, general manager SQL Server strategy at Microsoft, kicked off the symposium with a keynote. After the keynote the symposium was divided into three blocks: Breakthrough insight with SQL Server 2012, Delivering Mission Critical Confidence to the Modern Enterprise and Building the Modern Enterprise with Hybrid IT.

A view at the stunning Media Plaza during the Microsoft SQL Server 2012 symposium.

As BI Architects we were mostly interested in the block concerning business intelligence, Breakthrough insight with SQL Server 2012. This block included a keynote from Marcel Westra, technology specialist BI at Microsoft NL, explaining the vision behind SQL Server 2012 and demonstrating the new features like PowerView and the integration of Hadoop (Big Data).

All of the speakers outlined five points off improvement for SQL Server 2012:

• Greater availability. Deliver the required 9s and data protection with AlwaysOn, delivering added functionality over CTP3 that allows customers to experience multiple, readable secondaries for distributed scale of reporting and backup workloads and support for FileTable and FILESTREAM which brings first-class HA to complex data types.
• Blazing-fast performance. More effectively govern performance in multi-tenancy environments like private cloud. Resource Governor enhancements include support for 64 resource pools, greater CPU usage control, and resource pool affinity for partitioning of physical resources and predictable resource allocation.
• Rapid data exploration. Empower end users with new insights through rapid data exploration and visualization. Discover new insights at the speed of thought with more feature/functionality in Power View, the highly interactive, familiar browser-based data exploration, visualization, and presentation experience for end users.
• Credible, consistent data. In addition to CTP3 functionality delivered for Data Quality Services and Master Data Services, customers can better support heterogeneous data within Data Warehouses through new Change Data Capture (CDC) support for Oracle.
• Optimized productivity. Optimize IT and developer productivity across server and cloud with the new SQL Server Data Tools, a unified development experience for database and business intelligence projects, and cloud-ready capabilities within SQL Server Management Studio for snapshot backups to the Windows Azure Platform. Additionally, SQL Server 2012 offers a new version of Express – LocalDB. SQL Express LocalDB is a lightweight version of Express with all its programmability features, yet runs in user mode with a fast, zero-configuration installation and short list of pre-requisites. For more information and to try it out, go here.

There are three main editions planned for SQL Server 2012: Standard, Business Intelligence (new) and Enterprise. Business Intelligence adds better reporting via PowerView, PowerPivot for SharePoint Server, Data Quality Services, Master Data Services, and an In-Memory Tabular BI Semantic Model.

For more information about the new features go here.

All of the keynotes will be posted online by Microsoft,  keep watching this blog to find out were you can watch them!

Update:
The videos aren't online yet, but Microsoft sended the presentation to us in the PDF file format. Download the 15 MB zip file from this link.

Query to find the size of the database and database file

Technique 1:


     EXEC sp_helpdb

Technique 2: For a specific database:

sp_helpdb'adventureworks'

And you get the following output: (this one gives you the database and data file information for a specific database)




Technique 3: be in the context of the database and run sp_spaceusedGives you the database size and certain more properties of it:


     EXEC sp_spaceused

This is what you get:

Technique 4: use sys.master_files


SELECTDB_NAME(database_id)AS DatabaseName,
Name AS LogicalName,
size*8/1024 Size_in_MB,
max_size
FROM sys.master_files
WHEREDB_NAME(database_id)='AdventureWorks'
GO




If you liked this post, do like us on FaceBook at http://www.facebook.com/pages/BI-Architects/249973345047634?sk=wall

BI solution delivers insight on CXL P&L attributes

BI Architects develops Business Intelligence (BI) solutions for its customers ranging from commodity trading and enterprise risk to project management.

In this video blog I want to present a BI solution we have developed for one of our customers, that uses Triple Point’s Commodity XL (CXL), giving essential insight in the in the causes of the position and mark to market movements, including physical operations (P&L attributes).

The presentation depicted below describes our customer's needs, CXL and the solution itself.



For more information on our CXL BI solution for gaining insight on P&L attributes, please contact our sales manager Erik Rongen-Kleintjens via E.Rongen-Kleintjens@bi-architects.nl .

For more information on Triple Point’s Commodity XL (CXL) software, go to tpt.com .

Addition to BIDS: BIDS Helper

The Business Intelligence Development Studio (or BIDS) is typically used for developing Microsoft BI software. Now there is room for improvement everywhere and the BIDS Helper offers this improvements.

BIDS Helper is a Visual Studio add-in which will add a number of additional features and options to Visual Studio. Most additions are in the Analysis Services and Integration Services projects and range from comfortable to extremely useful. A lot of these additions are really things that make you wonder why Microsoft didn't include it themselves...

A few examples:
- Integration Services packages can sort by name within a project
- Variables in packages for Scope another move / copy
- Within Analysis Services option to create your attribute relations graphically
- A Smart Diff option packages or differences between two cubes to detect

And there are many more.

The add-in comes from an open source project on Codeplex (from Microsoft) and is free to use. It is available for Visual Studio 2005 as well as 2008, so be sure to choose the right one.

Download BIDS Helper, install and try it yourself!

BI solution additional to use of Clarizen's online project mgt software

BI Architects develops Business Intelligence solutions for its customers. Furthermore it has developed BI solutions for managing its own business processes, such as the solution that helps the project managers monitor, track and report project progress (including KPI's and earned values), the finance department enable swift and correct detailed invoicing and the management team gaining direct insight in the performance/profitability of projects.


BI Architects has chosen the Clarizen's online project management software for the registration of customers, projects, milestones, tasks and writing time. The Clarizen tool takes care of the complete project administration/resource management/and much plus it gives basic insight in performance via reporting. What it lacks is giving a more detailed insight in the performance of project phases over projects (for comparing project performances and quality improvement), earned values (current status and expected outcome) of projects and more figures needed for project management that are available without starting reports, setting parameters, running the report and opening the result in MS Excel.


What we have done is developing a standard Microsoft SQL Server Integrated Services/SQL Server Analysis Services (SSIS/SSAS) solution that adds what we miss in the Project Management tooling. SSIS is used for the extraction, transformation and loading of data from the Clarizen database into a staging environment (dimension tables), while SSAS delivers actionable insights (cubes and its dimensions) and made available via a pivot table in Microsoft Excel.


All information is now available via MS Excel and on the fly customizable serving several purposes within the BI Architects (project) organization.


For more information on our project management BI solution please contact me via H.O.Israel@bi-architects.nl . For more information on Clarizen's online project management software go to Clarizen.com .


Hermann-Otto Israel
BI Architects project manager

"No cubes can be found" message from SSRS 2008 R2

Today I had a problem that I have already came across some time ago. In Visual Studio 2008 (BIDS) I created a SQL Server Reporting Services (SSRS 2008 R2) report and connected this report to an Analysis Services Olap Cube (SSAS 2008 R2). Then I created a new dataset and when I started the Query Designer, the following error message appears: "No cubes can be found".









After looking at the cube properties, I noticed that the "Visible" property of the cube was set to "False". This property should be set to "True" to enable connectivity in client applications.



Hopefully I can help someone with this!

Welcome to our blog!

Welcome! Before we describe what you can get from reading our new blog, we want to introduce ourselves to anyone new to this space. BI Architects is a specialist in Business Intelligence Solutions and is located in the Netherlands. Our mission is to offer customized Business Intelligence and System Integration, through honest advice and implementation projects where quality, commitment, up to date knowledge and understanding in and optimizing of the processes of our customers stands central.

What can you find at the BI Architects blog? Our blogging team will cover a wide-range of Business Intelligence topics, including Dimensional Modeling, System Integration, Project Management, News, Information on trends, and more. Check back on a regular basis to read about:

• Project Management approaches in Business Intelligence projects
• Business Intelligence with online Project Management software from Clarizen
• Business Intelligence in Trading & Risk Management with Commodity XL (CXL) from Triple Point
• Sharepoint 2010 dashboard solutions
• Innovations in Business Intelligence software

Our blogging team will also describe practical solutions for challenges we have with tools like:

• Microsoft Visual Studio
• SQL Server
• SQL Server Integration Services (SSIS)
• SQL Server Analysis Services (SSAS)
• SQL Server Reporting Services (SSRS)
• Sharepoint 2010
• etc.

So, enjoy reading, and don’t forget to comment. We want to hear from you.

The BI Architects Blogging Team