May 26, 2015 at 2:33 pm
Hi all,
I'm a novice with SSAS, but figured it would only benefit me to dabble a bit using data that is familiar to me, but I have been scratching my head since last Friday trying to get what I think will be simple for those that are in here every day. Any help would be greatly appreciated, and I thank you in advance!
I have three tables in play that I'm trying to join (columns joined specified here, full table code below):
dbo.app_names_supported
app_name
dbo.sql_ci_compliance_by_instance
app_name
dbo.app_data
app_name
My current goal:
Get the data into an Excel PivotTable and pivot the data based on application name (and have the ability to filter app names), and have the numbers re-calculate based on application selection.
Long-term goal:
Use SSRS to display this same pivottable to allow web-based consumption of the data.
My current situation:
The cube is deployed, and in my PivotTable, I have:
1. A filter on app_name from app_names_supported
2. Values: count of fqdn from app_data; NonCompliantCount from sql_ci_compliance_by_instance
Next to the app_name, the "count of fqdn" dimension is updating as expected. However, the NonCompliantCount is the count for ALL non-compliant items, not just for that app. So, my question, what trick am I missing on my NonCompliantCount dimension to make it update correctly? In addition, I have apps still listed (exist on the sql_ci_compliance_by_instance) that are not in the apps_supported table. I only wish to make "supported" apps available in the reporting side, and I thought doing the join within the cube would have removed them, but it is not.
Within my tabular project, i created a couple of dimensions (I think that's the correct term):
On app_data: Count of fqdn:=DISTINCTCOUNT([fqdn])
On sql_ci_compliance_by_instance: NonCompliantCount:=CALCULATE(COUNTROWS(SQL_CI_Compliance_By_Instance), 'SQL_CI_Compliance_By_Instance'[CI_Compliance_State] = "Non-Compliant")
CREATE TABLE [dbo].[App_Names_Supported](
[app_name] [varchar](675) NOT NULL,
CONSTRAINT [PK_App_Names_Supported] PRIMARY KEY CLUSTERED
(
[app_name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[SQL_CI_Compliance_By_Instance](
[pk_id] [int] IDENTITY(1,1) NOT NULL,
[instance_name] [varchar](255) NULL,
[computer_name] [varchar](255) NULL,
[CI_Name] [varchar](255) NULL,
[CI_Compliance_State] [varchar](255) NULL,
[fqdn] [varchar](255) NULL,
[failure_details] [varchar](max) NULL,
[collected_date_time] [datetime] NOT NULL CONSTRAINT [DF_SQL_CI_Compliance_By_Instance_collected_date_time] DEFAULT (getdate()),
[CI_CheckId] [int] NULL,
[CI_Priority] [int] NULL,
[CI_Category] [varchar](100) NULL,
[sql_major_version] [varchar](50) NULL,
[data_center] [varchar](50) NULL,
[is_fci] [int] NULL,
[vm_or_physical] [varchar](50) NULL,
CONSTRAINT [PK_SQL_CI_Compliance_By_Instance] PRIMARY KEY CLUSTERED
(
[pk_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[App_Data](
[id] [int] NULL,
[fqdn] [varchar](765) NULL,
[app_name] [varchar](765) NULL,
[app_Group_name] [varchar](765) NULL,
[environment] [varchar](765) NULL,
[department_name] [varchar](765) NULL,
[organization_name] [varchar](765) NULL,
[tier] [varchar](765) NULL,
[serial_number] [varchar](765) NULL,
[ip_address] [varchar](765) NULL,
[service_level_tier] [int] NULL
) ON [PRIMARY]
GO
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply