Technical Support

1.844.634.6348

support@finditez.com

Contact Form

Category Archives: Product Hints and Tips

Extract Power BI metadata to manage datasource changes

Power BI metadata contains information on the underlying datasources referenced along with all relevant report design and instructions used to generate report contents.  This metadata also contains source data refresh rates, automated report distribution schedules and much more.  It is the glue that binds the entire Power BI reporting solution together.

Power BI metadata and data flow

Overview of Power BI system and features

For a Power BI developer or administrator, arguably considered the most important metadata is the inter-relationships between individual Power BI reports and the underlying datasources they are built on.  Whenever a change is planned in these datasource (file) structures or database models, this metadata answers the critical question “which of my Power BI reports may be affected by these (datasource) changes?

A SQL Server database contains this metadata in a table that was originally designed for use with SSRS, and extended to support Power BI.  The following SELECT statement can be used to extract this information from the Microsoft Reporting Services SQL database:

SELECT
 [Name]
 ,[Type]
 ,CASE Type
   WHEN 2 THEN 'SSRS Report'
   WHEN 5 THEN 'DataSource'
   WHEN 7 THEN 'Report Component'
   WHEN 8 THEN 'Shared Dataset'
   When 13 Then 'Power BI Report'
   ELSE 'Other - not applicable'
 END AS TypeDescription
 ,CONVERT(varbinary(max),Content) AS Content
 FROM <ReportServerdB>.dbo.Catalog
 WHERE Type in (2,5,7,8,13)

Connect to the SQL Server host and replace <ReportServerDB> with your Report Server database name reserved for your SSRS or Power BI reporting system.  The metadata itself is contained within the Catalog.Content field, and is encoded.   A great article is posted by Bret Stateham describing how to extract and convert the encoded catalog.content XML for SSRS reports, datasources and shared datasets by simply using a set of SQL statements.

However, this same method does not work for Power BI metadata stored in the same catalog.content field.  Reason being, the encoded Power BI metadata is actually the entire .pbit archive comprised of several compressed XML documents and folders.   Essentially it is an encoded binary file.

To extract this encoded data for Power BI content, you must use a programming language such as .net to detect and decode the Bit Order Marker and stream the ( essentially zip file ) contents into memory or write to disk for processing.  Below are C#.net code snippets to accomplish this:

SELECT 
c.[Path]
, c.[Name]
, convert(varbinary(max), c.content) as RDL
, c.Type 
FROM Catalog c 
WHERE c.Content IS NOT NULL 
AND c.Type IN (2,13,5,8,7)

We stream this information into a DataTable object.  Then for each row we write the content of the report out to a file with this C#.net code.

...
string extension = ( see explanation below. use c.type to derive )
string filename = System.IO.Path.GetTempPath() + System.Guid.NewGuid().ToString("N") + extension; 
...
 using (var stream = System.IO.File.Create(filename))
 {
byte[] content = (byte[])row["RDL"];
 stream.Write(content, 0, content.Length);
 }
...

This also works for SSRS types stored in the SQL database Report Server catalog as well (reports, report components, datasource and shared dataset definitions).  When writing the files, simply append the appropriate filename extension for each file based on the catalog.type value.   For example, for “SSRS reports” catalog.type = 2, append extension = “.rdl” to the filename.  For “Power BI reports” catalog.type = 13, append extension = “.pbix” to the filename, and for the remainder of types, use extension = “.xml”.

For the Power BI report, once written to disk as a decoded .pbix file, you can then open the archive file ( if renamed to .zip for example ) to easily extract the Power BI metadata file contents.   This will include:

  • /report/layoutjson Power BI report contents
  • datamashup – the raw data used by the report, still compressed and encoded
  • … plus a number of other files used by Power BI

The embeded layout file contains Power BI metadata in a json text file.  Note that some of the other .pbix file contents are themselves archive binaries and would need to be decoded.  For example the datamashup file is itself an archive with an encoding wrapper that contains some block header & footer information that would need to be stripped off in order to extract the archive file contents.

Members of our R&D team, Gordon, Matteo, Ibrahim and Madel, are adding integrated Power BI search, documentation and change impact analysis support to our Dev Surge 365 Enterprise edition software, with an anticipated availability in early 2018.  Find it EZchange made simple.™

 

Peeling back 3 layers of BI and Analytics software

Three inter-related layers or processes in a robust BI analytics solution can include:

  1. a Symantic (meta-data) layer;
  2. OLAP cubes;
  3. Extract, Transform, Load (ETL) jobs.

Let’s take a closer look at each of these and how small changes within any one of them can affect your critical business reports at the top of your BI stack.

symantic_layer-jpg

(1)  Semantic layer – an abstracted ( meta-data ) layer that contains simplified business views mapped to underlying relational database models and data warehouses.  This layer is a pre-requisite for some WSYWIG, drag and drop design reporting tools.

A semantic layer is a business representation of corporate data that helps non-technical end users access data using common business terms.  The semantic layer is configured by a person who has knowledge of both the data store and the reporting needs of the business. The person creating the semantic layer chooses to expose appropriate fields in the data store as “business fields,” and to hide any fields that are not relevant. Each business field is given a friendly, meaningful name, and the business fields are organized in a way that will make sense to business users.

By using common business terms, rather than data language, to access, manipulate, and organize information, a semantic layer simplifies the complexity of business data.  This is claimed to be core business intelligence (BI) technology that frees users from IT while ensuring correct results when creating and analysing their own reports & dashboards.  In reality, however, IT staff are typically needed to create and maintain the semantic layer ( business view definitions ) which maps tables to classes and columns to objects.

olap_cubes

(2)  OLAP cubes – the arrangement of data into Cubes overcomes a limitation of relational databases, which are not well suited for near instantaneous analysis and display of large amounts of data.   OLAP data is typically stored in a star schema or snowflake schema within a relational data warehouse or in a special-purpose data management system.

An OLAP cube is a term that typically refers to multi-dimensional array of data. OLAP is an acronym for online analytical processing, which is a computer-based technique of analyzing data to look for insights.  A cube can be considered a multi-dimensional generalization of a two- or three-dimensional spreadsheet.   Each cell of the cube holds a number that represents some measure of the business, such as sales, profits, expenses, budget and forecast.  A Slice represents two-dimensional view of an OLAP Cube that arranges data in a grid, similar to a spreadsheet; a Slice functions much like a report or a query in an RDBMS in that it returns data based on a request for what to see

Although many report-writing tools exist for relational databases, these are slow when the whole database must be summarized, and present great difficulties when users wish to re-orient reports or analyses according to different, multidimensional perspectives, aka, Slices. The use of Cubes facilitate this kind of fast end-user interaction with data.  Some reporting tools like Crystal Reports include the ability to define simple two-dimentional OLAP cubes for generating graphs for example.

etl-process-flow

(3)  ETL Software – import clean data from virtually any source into permanent, structured relational database tables.

ALSO CALLED: Data Extraction Software, Database Extraction Software

DEFINITION: In managing databases, ETL refers to three separate functions combined into a single programming tool:  Extract, Transform, Load

  1. First, the extract function reads data from a specified source database and extracts a desired subset of data.
  2. Next, the transform function works with the acquired data – using rules or lookup tables, or creating combinations with other data – to convert it to the desired state.
  3. Finally, the load function is used to write the resulting data (either all of the subset or just the changes) to a target database, which may or may not previously exist.

ETL can be used to acquire a temporary subset of data from virtually any file for reports or other purposes, or a more permanent data set may be acquired for other purposes such as: the population of a data mart or data warehouse; conversion from one database type to another; and the migration of data from one database or platform to another.

etl-process-flow

A change to the underlying source database, or at any one of these BI layers can affect the hundreds or in some organizations, thousands of reports that are dependent on the underlying data model.  Something as simple as renaming a field or variable name can essentially break the chain, and stop reports from working or producing accurate results the business depends on. 

Now imagine a change impact analysis tool that can search your entire BI process flow to show you all affected references throughout your Business Intelligence stack.

Find it EZ – change made simple.    Download your free trial today @ http://www.finditez.com

Analytics and the Olympics

With the 2016 summer games in Rio coming to a close, millions have enjoyed real-time access to the action as it happens. All that data, images and video uploaded, organized, and redistributed virtually as it happens, available on any device, on demand. Thanks to the power of the analytics stack!

Rio2016

Summer Olympic Games 2016

When technology can be used to it’s potential, just like an elite Olympic athlete, a personal best performance, peaking at the right time, can deliver a podium performance.  Like the Olympics, there is always a new kid on the block striving to be the new champion.  With technology vendors, that comes through constant innovation and team work.  Who has been behind the Gold medal performances delivering all those statistics, images, video, sports cast articles and more?

Well, for the athletes and spectator experiences, there have been many companies contributing to improving performance and feeling like you are part of the action.  Check out this article mentioning Atos cloud backup and data services, Samsung virtual reality systems, Visa wearable near field communication payment systems, and more … https://www.rio2016.com/en/news/rio-2016-olympics-technological-innovations

But who were the main players for storing and delivering reports on all that data for the 2016 Olympics in Rio?

  1. The network backbone and infrastructure, primarily Cisco.   http://www.zdnet.com/pictures/the-tech-legacy-of-the-rio-olympics/ .   Other key technology partners were Samsung, Panasonic, EMC, Microsoft, Omega, and Symantec.
  2. Data and database vendor, Microsoft SQL Server.   Did you know there is even a real-time SharePoint data feed available?  http://odf.olympictech.org/2016-Rio/rio_2016_OG.htm   After years of dominance by IBM ( DB2 ), here is one example of a new champion moving up on the podium.
  3. Reporting tools, Many ???.   With the variety of dashboards, statistical reports, interactive, web, tablet, mobile devices and so on, the choice appears to have been left to the individual report designers depending on their needs, preference, corporate standards and delivery platform.    The official sponsors and suppliers list indicates the leading reporting tools used by the Olympic committee and official Rio Olympics website at least may have been SSRS and Power BI from Microsoft?  https://www.rio2016.com/en/sponsors
rio-2016-technology-graphic

Rio Olympics technology advances

Whether you are someone who enjoyed the freedom and real-time access to information, instant replays, medal standings, background stories about your favorite athlete or whatever you were looking for, all this technology has delivered a Gold medal performance.  Some peaked at the right time, some will be ready the next time around as up-and-comers, and some may not compete at the next Olympics with their best days now behind them. There is always room for improvement along with new entrants as well as retiring veterans coming and going over the years.  Who will emerge as the next lightning “Bolt” in BI analytics?, only time will tell …

Hope you enjoyed the competition, we @ FinditEZ sure did!   We embrace innovation and  freedom of choice, always.  We are proud to continue to grow our support for the best tools in the business intelligence analytics world.  Can’t wait to see what is to come over the next four years …  Download your free trial today.

 

Top 5 features of a great software user guide

Software user guides are good and useful, as long as they are well done.  What makes them great?

FinditEZ-Main-Screen

  1. No fluff.  Cut to to the chase.  Answer this question succinctly “how do I do this?  … or that?”.  Don’t fill a user guide with marketing jargen or unnecessary content.
  2. Relevant Examples.  Know your audience.   Provide real-world use case examples to describe precisely how to accomplish a given task.  When possible, these should come from actual customer support inquiries.
  3. Some pictures please.  A picture paints a thousand words.  Where appropriate, use some product screen shots for illustration and explantions.  Plain words and instruction are not always as effective.  Don’t be tempted to over-use screen shots though as you UI can change making this difficult to manage.
  4. Just the right amount of links for navigation.  Make it easy to navigate the guide, including listing relevant topics, a table of contents, glossary and index.  However, don’t over-use in content links as it can be distracting and disjointed to follow all links.  Your end-users can may be led astray, begin jumping around and not get the answer they were looking for in the first place!
  5. Keep it current.   Guides can quickly become “stale” as a software evolves through releases.  Ideally, review and update your guide with each software release.  Better still, publish new user guide release editions with each minor or major software product release to benefit those customers who have not yet upgraded.

Find it EZ is about to publish user guides for its full product line.