Transcript Munis Excel Cubes Presentation

Munis Excel Cubes
Kevin P. Daly, PMP
Munis Implementation Management
Overview
• Business Intelligence
• What is a Cube?
• Benefits of Using Cubes
• Cube requirements
• How does Munis data get into the Cube
• Cubes became available in Munis Version 9.3 / 10.1
• Security
2
Pivot Tables
You can change a basic table like this:
…into a pivot table like this:
3
Business Intelligence (BI)
• BI uses ‘tools’ to extract, analyze and report on
warehoused relational data
– Microsoft SQL and Share point Suite
– Interactive Dashboards
– Excel Pivot tables and Power Pivot
4
Business Intelligence (BI)
• BI uses ‘tools’ to extract, analyze and report on
warehoused relational data
– Microsoft SQL and Share point Suite
– Interactive Dashboards
– Excel Pivot tables and Power Pivot
• OLAP – On Line Analytical Processing
– In computing, OLAP is an approach to answering multi-dimensional
analytical (MDA) queries swiftly.
– The core of any OLAP system is an OLAP cube (also called a
'multidimensional cube' or a hypercube).
– The usual interface to manipulate an OLAP cube is a matrix interface like
Pivot tables and Power Pivot in a spreadsheet program, which performs
projection operations along the dimensions, such as aggregation or
averaging.
5
What is a Cube?
Cube Definition in Simple Terms
• A Cube aggregates numbers by various categories of data
• The numbers in a Cube are called Measures
• Examples: Original Budget, Transfers, Revised Budget, Actual, and
Encumbrances
6
What is a Cube?
• The categories of data are Dimensions
– Example: GL Account, Account Type, and Time
– Example: Employee, Job Class, Location, and Time
• Cubes contain Hierarchies
– Allowing a user to start at a high level and drill to more detail very
quickly and easily
– Example: Year, Quarter, Month, Day
The cube aggregates the measures by every possible
combination of dimension and hierarchy levels
allowing answers to be displayed fast
7
Benefits of Using Cubes
• Data is named in familiar terms and logically grouped in
folders
• The design and execution of a report occur simultaneously
• Data populates the spreadsheet and/or graph quickly
• Users don’t need to know complicated formulas or
formatting such as “join” or other database “programming”
terms
• When users create a report, there is no impact on users
performing transactions in the system
8
Cube Requirements
• Microsoft SQL Server 2008 R2* (server)
– SQL Server Analysis Services and Integration Services must be
installed
• Microsoft Office 2007 or above (user)
– Microsoft Excel
• Munis Versions 9.3/10.1 and thereafter
*Note: Installing SQL Analysis Services and SQL Integration Service on a separate server
than the MUNIS database will require a second SQL Server license.
9
How Does Munis Data Get Into
the Cube?
• SQL Server Integration Services (SSIS) moves and
transforms data from Munis®
– First the data needed to create the cube is moved from the Munis database
to the TylerStagingDB
– The data in the staging database is then transformed into a star-schema
database called TylerReportingDB
• SQL Analysis Services (SSAS) creates the Cube using the
star schema database
• A Job is scheduled once per night or week to update the
databases and generate the Cubes
– Can be more frequent depending on the client’s requirements and servers
– Cube data is ‘static’
10
Cubes Currently Available
• General Ledger (GL Revenue and Expense)
• Accounts Payable
• Accounts Receivable
• Payroll
• Work Orders
• Project Ledger
• Permits (10.3)
• UB Consumption(10.3)
Following are the existing measurements and dimensions
within the major cubes already available
11
General Ledger Cube
GL Revenue and Expense Accounts only
– Measures
•
•
•
•
•
•
•
•
•
Actual
Available Budget
Revised Budget
Original Budget
Budget Transfers
Encumbrances
Requisitions
LTD values for Multi-Year Funds
Summaries based on the above
– Dimensions
• Account Number
– Various Hierarchies include Org/Object/Project, Full Account, and Character Code
– Attributes include Fund, each of the Segments, Org, Object and Project
• Account Type
– Attributes include Status, Account Type, Fiscal Year Type, Fund Type, Multi-Year
• Fiscal Year Hierarchy includes Year/Quarter/Month/Period
12
Payroll Accumulator Cube
• Measures
–
–
–
–
–
–
Gross Earnings
Employer Amount
Employee Amount
Hours
Rate
Employee Count
• Dimensions
– Employee
• Attributes include Active Status, EEO Race, Name, Number, Gender, Work Location
– GL Account
– Accumulator Type
• Hierarchy includes Accumulator Type/Category/Description
– Calendar and Fiscal Year Hierarchies
– Payroll Organization
• Hierarchy includes Location/Bargain Unit/Job Class
• Important Note: Data will only tie to Payroll Warrants depending on use of
and variance to the Accumulator Gross Verify program.
13
Accounts Payable Cube
• Measures
– Check Amount
– Invoice Total Amount
– Invoice Net Amount
– Discount Amount
– Hold
– Amount
– Sales Tax
– Use Tax
14
Accounts Payable Cube
• Dimensions
– Invoice
• Hierarchies include Invoice Type/Document Number and
Department/Document Number
• Attributes include Department, Document Number, Invoice Number, Status and
Type
– Check
• Attributes include Cleared Flag, Check Number, and Check Type
– GL Journal Year/Period Hierarchy
– Vendor
• Hierarchy Vendor Type/Vendor
• Attributes include Account Type, City, Class, Country, State, Status, Type,
Vendor Name and Number
– Calendar and Fiscal Year Hierarchies
– GL Account
15
Accounts Receivable Cube
• Measures
– Bill Amounts
•
•
•
•
•
•
•
•
•
Abate Amount
Adjusted Amount
Discount Amount
Interest Held
Interest Paid
Original Amount
Paid Amount
Refund Amount
Write Off Amount
– Payment Amounts
•
•
•
•
•
16
Discount Amount – Payment
Interest Held – Payment
Interest Paid – Payment
Principal Adjusted
Principal Paid
Accounts Receivable Cube
• Dimensions
– Bill
• Hierarchy includes Bill Year/Bill Number/Installment
• Attributes include Cycle, Bill Type, Loc Street Address, Property Code
– Payment
• Hierarchy includes Journal Year/Journal Period/Journal Number
• Attributes include Activity Type, Department, Posted
– Calendar Date
• Hierarchies based on Bill Date, Due Date and Interest Date
– Charge Code
• Hierarchy includes Charge Code Year/AR Category/Charge Code Description
– Customer
• Hierarchy includes Customer State/City/Customer
• Attributes include Account Type, City, Name, Address, State, Type, Department
17
Work Orders Cube
• Measures
– Actual Amount
– Actual Quantity
– Billed Amount
– Credit Amount
– Estimated Amount
– Estimated Quantity
18
Work Orders Cube
• Dimensions
– Asset
• Hierarchies include Class/Sub Class/Asset and Department/Asset
• Attributes include Type, Status, Asset Code
– Activity
• Hierarchy includes General Category/Sub-Category/Activity
• Attributes include Department, Priority, Trade
– Work Order
• Attributes include Cost Type, Days Open, Item, Location, Requesting and
Service Department, Description
– Contact
• Attributes include Name, Type, Code, Customer Number
– Calendar and Fiscal Year Hierarchies
19
Permits Cube
Measures
– Application Amounts
• Application Count
– Fee Amounts
•
•
•
•
Adjustment Amount
Fee Amount
Paid to Date
Summaries
– FYTD Fee Amount (Fiscal Year to Date)
– YTD Fee Amount
– Permit Amounts
•
•
•
•
20
Building Units
Estimated Value
Gross Square Feet
Permit Count
Permits Cube
Dimensions
• Application
– Attributes include Application, Application Reference, Application Status,
Department, Munis Application Status, Permit Work Description
• Contractor
– Hierarchy for Contractor City/Contractor State/Contractor
– Attributes include Business Type, Contractor, Contractor City, Contractor
Customer Number, Contractor Name, Contractor State, Contractor Type,
Contractor Zip
• Fee
– Attributes include Fee, Fee Status, Rate Type
• Location
– Attributes include Location, Location Municipality, Location Municipality
Code, Location State, Location Status, Location Street, Location Zip,
Parcel ID, Street Type
21
Permits Cube
• Dimensions
• Permit
– Attributes include Building Style, Building Use, Construction Type, Issuing
Department, Permit, Permit Category, Permit Fee Used, Permit Number, Permit
Prefix, Permit Status, Permit Type, Permit Type Status
• Project
– Attributes include Project, Project Category, Project Code, Project Group, Project
Status, Project Type
• Property
– Hierarchy for Owner City/ Owner State/ Owner
– Attributes include Inspection Area, Owner, Owner City, Owner Customer Number,
Owner Name, Owner State, Owner Zip, Parcel ID, Parcel Use, Subdivision, Zone
• Calendar and Fiscal Year Hierarchies for Application Add Date, Application Close
Date, and Permit Date
22
UB Consumption Cube
• Measures
–
–
–
–
–
–
Billed Rate Adjustment Amounts
–
–
–
–
–
–
–
Demand Actual (electric only)
Demand Amount (electric only)
Demand Billed (electric only)
KVAR Actual (electric only)
KVAR Billed (electric only)
KVAR Billed (electric only)
Subject To Amount
–
Total Billed Amount (Usage Amount + Base Amount + Demand Amount + KVAR Amount + Subject To Amount + Credit
Amount)
Usage Actual
Usage Amount (i.e. consumption amount)
Usage Billed
Usage Replaced
–
–
–
–
23
Rate Adjustment Amount (determined by “Adjust rates in Charges”)
Billed Service Amounts
Base Amount
Connections
Credit Amount
UB Consumption Cube
• Dimensions
– Account Master
• Attributes include Account Number, Account Type, District, Location City,
Location Street, Location Zip
– Billed Service
• Attributes include AR Category, Bill Run, Bill Run Commitment, Bill Run Type,
Current Rate, Route Book, Summary Code
– Customer
• Hierarchy for Customer City/Customer State/Customer
• Attributes include Customer, Customer City, Customer Name, Customer
Number, Customer Relation, Customer State, Customer Zip
24
UB Consumption Cube
• Dimensions
– Meter
• Attributes include Flow Type, Manufacturer Code, Meter Model, Meter Size,
Meter Type
– Service Master
• Attributes include AR Category, Customer Type, Cycle, Rate, Route Book,
Service, Service Code, Service Sequence, Service Status, Service Type
• Calendar and Fiscal Year Hierarchies for Bill Date and Read Date
25
Cube Security
Each user must be granted access to a Cube
–
–
–
–
26
Connect to the Analysis Services server using SSMS
Expand the databases and Roles folders for the Cube
Double-click the Access Role
Add the necessary users and/or groups on the Membership Tab
Cube Security
• In addition, the following Cubes use Munis security
– General Ledger (GL Revenue and Expense)
• Uses the GL Account level Role security specified in Munis
– See GL Account Inquiry program using Org, Object, and Project
• Must have access to GL Reporting Views (Tyler Reporting Services)
(System Administration/Report Writer/Tyler Reporting Services Views)
– Payroll
• Uses the Location, Org, Deduction and Pay Type maintenance access
Role security specified in Munis
• Must have access to GL Reporting Views (Tyler Reporting Services)
(System Administration/Report Writer/Tyler Reporting Services Views)
27
Cube Security
Once granted access to a cube, with the exception of
the General Ledger and Payroll cubes, the user has
full access to all of the data in the Cube.
28
Connecting to a Cube
The connection to each Cube must be set up once
– Click the Data tab on the Excel ribbon
– Click the From Other Sources drop down arrow
– Select From Analysis Services as the source
– Enter the server name for Analysis Services
– Click next
– Select the Cube database from the drop down list
– Select the cube
– Click next
– Enter a friendly name
– Click finish
29
Connecting to a Cube
• Once, the connection has been established, it is available
on the Data tab under Existing Connections for easy future
reference
30
Reporting on Cubes
• Uses Excel PivotTables
• Pick measures and dimensions from the Field List
• Use Report Layout section to reorganize a report
31
Reporting on Cubes
The user can either click a field or drag a field to a specific location to
add to the PivotTable
32
Reporting on Cubes
Each time a field is selected or removed the PivotTable is updated
33
Reporting on Cubes
Filters can be added or removed using the drop down arrow or filter
button next to column and row labels
34
Reporting on Cubes
• Control if and where subtotals and grand totals appear in the PivotTable
using Design Tab
• Headers and Color options can also be found on this tab
• Right-click the PivotTable for additional formatting options
35
Reporting on Cubes
• For reporting needs not met with existing cube structures, explore
Excel Power Pivot
– This will require download of the Excel Add-On for Power Pivot
• Power Pivot features –
– Provides access to all database tables and other SQL sources
– Users can use the database schema for the Munis database to create links between tables
– Allows for creating user-defined calculated fields that do not currently exist within a cube
36
Summary
• Cubes available starting in Munis Versions 9.3 / 10.1
• Access Cube data using Excel
–
–
–
–
–
Use the field list to add data to the pivot table area
Filter columns and/or rows
Move fields
Formatting options
Right click the pivot table area for additional formatting options
• Remember…
– Static data
– Security
– Power Pivot
37
Munis Data Cubes Documentation
Finding the Data Cube documents in the Munis KnowledgeBase
Search based on Topic “cubes” and Release “Version 10”
38
Education
Free Online Education
– Next session: June 5th from 1:30 – 4:30 PM EDT
– Calendar of classes available
– Many people can view class from one login
Onsite Visit
– Contact sales rep to arrange for an implementation visit
– Visit can be customized to your site’s specific needs
39
Munis Data Cubes
Thank you!
Questions
[email protected]
40