• 新版成本管家可视化账单报表实践

    新版成本管家数据存储在表格存储(OTS)中,能够更好地保证数据准确性和实时性,并且支持每日自动全量更新。新旧版成本管家均不会产生额外费用。成本管家所涉及的Project、表格存储实例均为免费实例。

    PART.01  开通新版成本管家

    点击sls.console.aliyun.com/此链接,勾选“导入账单授权”和“授权开通表格存储”,点击“立即更新”按钮。

    选择“阿里云账单导入”,点击“下一步”按钮。

    点击“更新资源&下一步”。

    点击“完成”按钮。

    说明:数据导入大概需要1~2小时,具体导入时间受账单数据量影响。

    PART.02   账单数据说明

    点击otsnext.console.aliyun.com此链接,找到“华北6(乌兰察布)”地域下的以”bss-“开头命名的实例,点击进入该实例。

    根据instance_bill表的大小即可判断数据是否导入。

    点击sls.console.aliyun.com/此链接,找到“bill-analysis-”开头,主账号uid结尾的project,点击进入。

    双击“aliyun_bill”这个logstore,查询本年度的数据,提示“您的logstore当前暂无内容”。

    这是因为新版成本管家的账单数据存储在表格存储中,以外表instance_bill的形式关联到日志服务Logstore,因此您在Logstore查询分析页面无法直接查看新版成本管家的账单数据。您可以通过下面的语句查询数据。

    *| select * from instance_bill 

    账单数据说明

    PART.03 导入分账账单

    账单明细中的财务单元,对于分拆型产品来说是不准确的,分拆型产品的财务单元需要以分账账单内的为准。目前成本管家默认没有导入分账账单,如果需要导入的话,可以手动进行如下几步操作:

    第一步、表格存储创建数据表

    点击otsnext.console.aliyun.com此链接,进入表格存储,选择乌兰察布区域,进入’bss-‘形式命名的实例

    选择“数据表列表”,点击“创建数据表”按钮。

    创建splititem_bill 表,主键配置参考instance_bill,完成配置后点击“确定”按钮。

    数据表名称 表主键 主键类型 是否允许更新 是否加密 创建二级索引
    splititem_bill hash_key 字符串
    __time__ 整型

    第二步、进入成本管家执行查询语句

    点击sls.console.aliyun.com/此链接,找到“bill-analysis-”开头,主账号uid结尾的的project,点击进入。

    双击名为“aliyun_bill”的logstore,在搜索框中填写下面的SQL语句。其中 {endpoint}需要替换为表格存储中’bss-‘开头命名的实例经典网络访问地址,{ots_instance_name}需要替换为表格存储中’bss-‘开头命名的实例名称,{uid} 需要替换为主账号uid。点击“查询/搜索”按钮。

    * | create table splititem_bill (hash_key varchar, __time__ bigint, __source__ varchar, instanceconfig varchar, productcode varchar, internetip varchar, outstandingamount double, pretaxamount double, instancespec varchar, subscriptiontype varchar, billingitem varchar, pretaxgrossamount double, usage double, producttype varchar, paymentamount double, listprice double, zone varchar, usageunit varchar, nickname varchar, productname varchar, currency varchar, tag varchar, invoicediscount double, productdetail varchar, item varchar, resourcegroup varchar, deductedbycashcoupons double, deductedbyprepaidcard double, ownerid varchar, billingdate varchar, listpriceunit varchar, deductedbycoupons double, instanceid varchar, serviceperiod double, deductedbyresourcepackage double, billingtype varchar, region varchar, intranetip varchar, accountname varchar, AdjustAmount double, BizType varchar,CashAmount double, CommodityCode varchar, CostUnit varchar, ServicePeriodUnit varchar, SplitAccountID varchar, SplitAccountName varchar, SplitBillingCycle varchar, SplitBillingDate varchar, SplitCommodityCode varchar, SplitItemID varchar, SplitItemName varchar, SplitProductDetail varchar ) with ( endpoint='{endpoint}',accessid='rolearn',accesskey='acs:ram::{uid}:role/aliyunserviceroleforslsaudit',instance_name='{ots_instance_name}',table_name='splititem_bill',type='tablestore')

    第三步、联系阿里云后台创建分账账单的导入任务

    PART.04 成本管家仪表盘

    4.1 默认报表

    点击sls.console.aliyun.com/此链接,红框圈出的部分为阿里云提供的常见账单分析场景。

    点击“另存为”按钮,将这些默认报表存储到SLS仪表盘中,方便我们查看、配置及订阅。

    点击sls.console.aliyun.com/此链接,找到“bill-analysis-”开头,主账号uid结尾的的project,点击进入。在左侧选择仪表盘图标,选择刚保存的默认仪表盘,点击编辑按钮。

    选择一个图表,点击该图标右上方的编辑图标,在弹出界面中选择“编辑”按钮。

    可以看到该图表的查询语句及图表配置,在配置自定义报表时,可以参照默认报表的配置。

    4.2 自定义报表

    4.2.1 添加标题及说明

    点击sls.console.aliyun.com/此链接,找到“bill-analysis-”开头,主账号uid结尾的的project,点击进入。在左侧选择仪表盘图标,点击加号图标,在弹出界面中选择“添加仪表盘”。

    填写“仪表盘名称”,完成后点击“确认”按钮。

    选择刚创建的仪表盘,点击“编辑”按钮,进行编辑操作。点击“添加”按钮,选择“文本”。

    将下面的标题及说明填写至文本框中。

    账单总览和预测
    1、分账账单当月数据查询结果仅供参考,不作为对账依据;当月最终账单在次月4日12点后支持查看(月中会发生包括但不限于以下少数场景:延迟出账,退款,调账,欠费核销等)。
    2、当月明细账单数据中不包含未结算(未出账,累账中)的后付费数据。
    3、分账账单数据相对于实际费用消耗延迟48小时更新,其中分拆型云产品(例如:CDN、OSS、共享带宽等)各个分拆项(例如:域名、Bucket、EIP等)的具体费用延迟72小时更新。

    4.2.2 添加变量类型过滤器

    添加财务单元过滤器

    点击“添加过滤器”图标的按钮,添加过滤器。

    根据下表信息填写配置,填写“过滤器名称”,“类型”选择“变量替换”,“显示组件类型”选择“下拉列表”,“替换模式”选择“指定变量”,“Key值”为“cost_unit”,打开“添加动态列表项”,“请选择日志库”选择“aliyun_bill”,将下面代码框中的查询语句填写至“标签动态查询”框内,时间选择“本年度(整点时间)”,完成上述配置后,点击“确定”按钮。

    过滤器名称 类型 显示组件类型 Key值 别名 日志库
    财务单元筛选 变量替换 下拉列表 cost_unit 财务单元筛选 aliyun_bill
    *|select distinct costunit from splititem_bill where ResourceGroup like '${{resource_group|%%}}'

    添加完成后,点击“保存”按钮,即可选择财务单元。

    添加资源组过滤器

    同添加财务单元过滤器一样,按下表信息添加资源组过滤器,添加后如下图所示可以选择资源组。

    过滤器名称 类型 显示组件类型 Key值 别名 日志库
    资源组筛选 变量替换 下拉列表 resource_group 资源组筛选 aliyun_bill
    *|select distinct ResourceGroup from splititem_bill where costunit like '${{cost_unit|%%}}'

    4.2.3 添加折叠行

    点击“添加”按钮,在弹出界面中选择“添加折叠行”。

    点击刚创建的折叠行右边的编辑图标,在弹出界面中“显示名”填“本月消费总览”,完成后点击“确认”按钮。

    添加折叠行后,您可以将其拖动至目标位置,用于折叠位于其下的图表。

    4.2.4 添加统计图Pro

    点击“+添加”按钮,选择“添加新图表”。

    在“查询分析”处,选择“Logstore(SQL)”和“aliyun_bill”,将下面的SQL语句写入查询框内。时间选择“昨天”,点击“应用”按钮。在“通用配置”下的“图标类型”选择“统计图Pro”,“基本配置”中的“标题”填为“昨日消费”,打开“显示标题”、“显示边框”、“显示背景”、“显示时间”。

    * | select round(diff[1],2),round(diff[2] ,2), round(diff[3]*100-100,2) from(select compare("昨日消费",2592000) as diff from(select round(coalesce(sum(PretaxAmount),0),3) as "昨日消费" from instance_bill where costunit like '${{cost_unit|%%}}' and resourcegroup like '${{resource_group|%%}}'))

    在“标准配置”中,“格式化”选择“1,000,000”,“单位”选择“自定义”为“元”,“小数点后位数”为“2”,“颜色方案”选择“单色”为“#FFFFFF”。

    在“图表样式”中,“文字模式”选择“值”,“值字体大小”填为“20”,“颜色模式”选择“背景”,“字体颜色”填为“#555555”,“图像模式”选择“None”,“文字对齐”选择“居中”。

    在“查询分析设置”中,“显示字段”选择“_col0”,“对比字段”选择“_col2”,“对比值描述”填为“昨日消费 / 同比上月”,“对比值格式化”选择“无格式化”,“对比值单位”选择“自定义”为“%”,“趋势比较阈值”填为“0”,“小数点后位数”填为“2”,“描述值字体大小”填为“12”。完成上述配置后,点击“确定”按钮。

    显示效果如下图所示:

    同理根据下面的SQL添加其他指标图

    最大日消费

    * |  select  coalesce(cast( round(diff[1],2) as varchar),'无数据'),round(diff[2] ,2),round(diff[3]*100-100,2)  from( select compare("最大日消费",2592000) as diff from( select round(max(this_month),2) as "最大日消费" from ( select  date_trunc('day',__time__) as day,  sum(  PretaxAmount ) as "this_month" from splititem_bill where costunit like '${{cost_unit|%%}}' and resourcegroup like '${{resource_group|%%}}' group by day)))

    本月累计已消费

    *  | select coalesce(cast(round(diff[1],2) as varchar),'无数据'),diff[2], round(diff[3]*100-100,2) from( select compare(this_month,2592000) as diff from( select this_month  from( select  sum(   PretaxAmount       ) as "this_month" from splititem_bill where costunit like '${{cost_unit|%%}}' and resourcegroup like '${{resource_group|%%}}' )))

    本月预计消费

    *  | SELECT COALESCE(Cast(Round("本月预计消费",2) AS VARCHAR),'无数据') AS "本月预计消费",Round("上月消费" ,2) AS "上月消费",Round("本月预计消费" / "上月消费"*100-100,2) AS PERCENT FROM (SELECT Sum (CASE WHEN time >=To_unixtime(Date_trunc('month',Now())) AND time < To_unixtime(Date_add('month',1,Date_trunc('month',Now()))) THEN predict ELSE 0 END ) AS "本月预计消费",Sum(CASE WHEN time <To_unixtime(Date_trunc('month',Now())) AND time >= To_unixtime(Date_add('month',-1,Date_trunc('month',Now()))) THEN src ELSE 0 END ) AS "上月消费" FROM(SELECT res.stamp as time,  res.real as src,res.pred as predict,res.instances[1] as ins FROM(SELECT sls_inner_ts_regression(Cast(day AS BIGINT), total, array['total'], 86400, 30) AS res FROM(SELECT * FROM(SELECT *,max(day) OVER() AS lastday FROM (SELECT   to_unixtime(date_trunc('day',__time__)) AS day, sum(pretaxamount) AS total from splititem_bill  where costunit like '${{cost_unit|%%}}' and resourcegroup like '${{resource_group|%%}}' GROUP BY day ORDER BY day))))))

    最终效果如下图所示:

    4.2.5 添加线图Pro

    点击“+添加”按钮,选择“添加新图表”。在“查询分析”处,选择“Logstore(SQL)”和“aliyun_bill”,将下面的SQL语句写入查询框内。时间选择“30天(整点)”,点击“应用”按钮。在“通用配置”下的“图标类型”选择“线图Pro”,“基本配置”中的“标题”填为“本月每日消费及趋势预测”,打开“显示标题”、“显示边框”、“显示背景”、“显示时间”。

    * | select date_format(res.stamp,'%Y-%m-%d') as time,res.real as "实际消费",case when is_nan(res.real) then res.pred else null end as "预测消费",res.instances from(select sls_inner_ts_regression(cast(day as bigint),total,array['total'],86400,60) as res from ( select * from ( select *, max(day) over() as lastday  from (select to_unixtime( date_trunc('day',__time__)) as day, sum(PretaxAmount) as total from splititem_bill where costunit like '${{cost_unit|%%}}' and resourcegroup like '${{resource_group|%%}}' group by day  order by day)) where day < lastday )) limit 1000

    在“标准配置”下的“格式化”选择“K,Mil,Bil”,“小数点后位数”填为“8”。

    在“查询分析配置”下的“x轴字段”选择“time”,“y轴字段”选择“预测消费”和“实际消费”。

    在“图形配置”下的“图形类型”选择“线”,“连接方式”选择“直线”,“线宽”填为“2”,“透明度”填为“40”,“点大小”填为“5”。完成上述配置后点击“确定”按钮。

    显示效果如下图所示:

    4.2.6 添加表格Pro

    4.2.6.1 数据转换

    点击“+添加”按钮,选择“添加新图表”。在“查询分析”处,选择“Logstore(SQL)”和“aliyun_bill”,将下面的SQL语句写入查询框内。时间选择“一周(整点)”,点击“应用”按钮。在“通用配置”下的“图标类型”选择“表格Pro”,“基本配置”中的“标题”填为“一周消费”,打开“显示标题”、“显示边框”、“显示背景”、“显示时间”。

    * | select date_format(__time__,'%Y-%m-%d') as "日期", round(sum(pretaxamount),3)  as "金额" from splititem_bill where costunit like '${{cost_unit|%%}}' and resourcegroup like '${{resource_group|%%}}' group by "日期" order by "日期"

    选择“数据转换”,类型选择“行列变换”,数据源选择“A”,完成上述配置后点击“确定”按钮。

    显示效果如下图所示:

    4.2.6.2 字段配置

    点击“+添加”按钮,选择“添加新图表”。在“查询分析”处,选择“Logstore(SQL)”和“aliyun_bill”,将下面的SQL语句写入查询框内。时间选择“本月”,点击“应用”按钮。在“通用配置”下的“图标类型”选择“表格Pro”,“基本配置”中的“标题”填为“本月产品明细”,打开“显示标题”、“显示边框”、“显示背景”、“显示时间”。

    * | select productname as "产品名称",round(diffreal [1], 3) as "折后费用(元)",cast(round(diffreal [1] / sum(diffreal [1]) over() * 100, 2) as varchar ) as "产品费用占比",cast(round(diffreal [3] * 100 -100, 2) as varchar) as "同比上月(折后费用)",round(diffgross [1], 3) as "原始消费(元)",cast(round(diffgross [3] * 100 -100, 2) as varchar) as "同比上月(原始消费)",(days) as "30天费用趋势" from(select productname,compare(pretaxamount, 2592000) as diffreal,compare(PretaxGrossAmount, 2592000) as diffgross,arbitrary(days) as days from(select productname,sum(PretaxAmount) as PretaxAmount,sum(PretaxGrossAmount) as PretaxGrossAmount,array_agg(pretaxAmount) as days from(select productname,sum(PretaxAmount) as PretaxAmount,sum(PretaxGrossAmount) as PretaxGrossAmount,date_trunc('day', __time__) as t from splititem_bill where costunit like '${{cost_unit|%%}}' and resourcegroup like '${{resource_group|%%}}' group by productname,t order by t) group by productname) group by productname) order by diffreal [1] desc limit 2147483647

    在“标准配置”下的“小数点后位数”填为“8”。

    选择“字段配置”,点击“添加字段”按钮。

    选择“A > 30天费用趋势”后,点击“添加配置”按钮。

    选择“字段列设置 > 单元格显示方式”后选择“线图”,可以观察到左边“30天费用趋势”由数据转为线图显示。

    继续点击“添加字段”按钮,

    字段选择“A > 同比上月(折后费用)”,配置选择“值映射”,点击“添加值映射”按钮。

    点击“添加”按钮,选择“值”,输入“NaN”,“映射类型”选择“文本”,“映射值”填为“0%”。继续点击“添加”按钮,选择“值”,输入“null”,“映射类型”选择“文本”,“映射值”填为“0%”。完成上述配置后,点击“确认”按钮。

    可以看到左边“同比上月(折后费用)”原本显示为null的,现在变成了0%。

    在“A > 同比上月(折后费用)”字段下,继续点击“添加配置”按钮。

    配置选择“标准配置 > 格式化”,选择“percent(1-100)”。可以看到左边“同比上月(折后费用)”比原来多了%。

    在“A > 同比上月(折后费用)”字段下,继续点击“添加配置”按钮。配置选择“字段列设置 > 单元格显示方式”,选择“背景高亮”。继续点击“添加配置”按钮。配置选择“标准配置 > 颜色方案”,选择“阈值”。继续点击“添加配置”按钮。配置选择“阈值 > 阈值”,选择“白色”。点击“添加阈值”按钮,选择“绿色”,填为“-10”,选择“小于”。点击“添加阈值”按钮,选择“粉色”,填为“10”,选择“大于”。可以看到左边“同比上月(折后费用)”小于-10的背景显示为绿色。

    同理给“同比上个月(原始消费)”和“产品费用占比”配置值映射。完成上述配置后,点击“确定”按钮。

    显示效果如下图所示:

    4.2.7 效果展示

    可以通过筛选时间、财务单元、资源组,可视化展示财务账单。

    «
    »
以专业成就每一位客户,让企业IT只为效果和安全买单

以专业成就每一位客户,让企业IT只为效果和安全买单